MySQL joins a record that may not exist

Question

I am trying to execute a query similar to this:

SELECT <columns> FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id
INNER JOIN table3 ON table1.id = table3.table1_id
WHERE table3.column1 != 'foo' AND <other_conditions>
LIMIT 1;

The thing is, I want the query to return a result regardless of whether the record exists in table3or not. That is - if a record is table3present, I want to check whether this record has a specific column value. If the entry in table3does not exist, I want the query to assume that the condition is TRUE.

Any pointers?

Answer 1

. , , coalesce , :

SELECT <columns> FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table1.id = table3.table1_id
WHERE COALESCE(table3.column1, '') != 'foo' AND <other_conditions>
LIMIT 1

Answer 2

, , WHERE JOIN.

SELECT 
  <columns> 
FROM
  table1 
  INNER JOIN table2 ON table2.table1_id = table1.id
  LEFT  JOIN table3 ON table3.table1_id = table1.id AND table3.column1 <> 'foo' 
WHERE
  <other_conditions>
LIMIT 1;

Answer 3

You need to use an outer join to include table3 instead of an inner join.