Query multiple tables with foreign key filled or is null 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+?
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, '').