Geek asks and answers

SQL optimization - joining different tables based on column value

I have a table that contains a column that acts as a “flag”, which is used to determine which table retrieves the additional information (i.e. the value 1 pulls from table 1, 2 from table2, etc.). Usually I just join the table using indexes / keys. However, the table that I could join in contained information that could be normalized into separate tables, which leaves me in this situation of using a column to decide which table to join.

So, here is my question: what is the most efficient way to join different tables based on the value obtained in this column?

Here are two ways that I know how to accomplish this task now. I am sure that both of them are not the optimal solution:

  • Extract information from my main table (containing the value of the column that determines which table to join), and then send additional requests through the code in my application to get the rest of the information.

  • Go crazy, return the columns of each table (even if they are not used). Then, through my code, ignore the zeros of the tables you don't need.

+5
source share
3 answers

2. , . . ( ), . 1 SQL . , , .

+1

, UNION - . - :

SELECT table1.title, tabel2.text FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE table1.key='2'
UNION
SELECT table1.title, tabel3.text FROM table1 INNER JOIN table3 ON table1.id=table3.id WHERE table1.key='3'

( SQL, , , , , )

+1

, :

create table a (id integer, flag boolean);
create table b (id integer, value_b varchar(30));
create table c (id integer, value_c varchar(30));

insert into a values (1, true), (2, false);
insert into b values (1, 'Val 1'), (2, 'Val 2');
insert into c values (1, 'C 1'), (2, 'C 2');

select a.id,
       case when a.flag then b.value_b else c.value_c end AS value
  from a
  left join b using (id)
  left join c using (id);

.

Of course, there are limitations:

  • the number of columns is fixed, so you should go for NULLif some values ​​should be omitted;
  • you need to write CASE ... ENDfor each column;
  • You must know all joined tables in advance.
  • performance may not be the best.
+1
source

More articles:


All Articles