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

closed as off-topic by ypercubeᵀᴹ, James Anderson, Tom V, RLF, Paul White Jan 15 '17 at 3:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, James Anderson, Tom V, RLF, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

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, '').

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