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+?