Comparing two SQL tables I want to always output a table 1 value if they have an ID in common output table 1 value and table 2 value

Question

I'm comparing two SQL tables (table1 & table2), if they have a common 'ID' I want to print out both. If they don't share the 'ID' I want to only print out the first table value. I did a JOIN but I could only output the data that had matching values. I then tried to SELECT * the data and put it in an array and then filter it with PHP instead, but am struggling.

SELECT table1.value, table2.additionalinfo
FROM table1
INNER JOIN table2
ON table1.ID=table2.ID

Answer 1

Make it an outer join instead - that way you will always bring in the first table - and just bring in matching rows from the second if they are there:

SELECT table1.value, table2.additionalinfo
FROM table1
left outer JOIN table2
ON table1.ID=table2.ID

And seriously, please read this Q&A that I posted which will explain this answer in a LOT more detail. I wrote is specifically for situations like this - where you get an answer to your question, but probably don't get as much detail and answer to make it fully stick in your mind - the Q&A explains everything (like inner, outer joins for example) with a lot of detail.

Answer 2

I think you want a "left outer join":

SELECT table1.value, table2.additionalinfo
FROM table1 LEFT JOIN
     table2
     ON table1.ID = table2.ID;