1

If I have two table like:

  • table1 with id, field1 and id_table2 and
  • table2 with id and field2;

Can I retrieve all rows on table1 also if the foreign key is null without using union? If I write:

select table1.id, table1.field1, table2.field2 from table1, table2
where  table1.id_table2=table2.id

it will return only the rows that contains id_table2!

if I write:

select table1.id, table1.field1, table2.field2
from   table1, table2
where  table1.id_table2 = table2.id
union
select table1.id, table1.field1, '' as field2
from   table1
where  table1.id_table2 is null

It should work correctly but It seems too much expensive to manage this simply situation! There is something better in mysql5+?

4

This is what outer joins do. Use a LEFT (outer) JOIN:

select t1.id, t1.field1, t2.field2
from       table1 as t1
       left join 
           table2 as t2
       on  
           t1.id_table2 = t2.id ;

If you want the empty string (and not NULL) in the result when there is no match, replace t2.field2 in the select list with COALESCE(t2.field2, '').

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.