MySQL select rows where left join is null

Question

I have these MySQL tables:

table1:

id | writer
1  | Bob   
2  | Marley
3  | Michael

table2:

user_one | user_two
   1     | 2

And this query:

SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.user_one

This query will return all rows of table1 which are 1,2,3

I want to select only rows which are not found in the left joint. So it should return only row with id 3

I want sort of the opposite of INNER JOIN which will select only the rows which are found in the join. How to get the opposite like if left join exists, ignore it and move to the next row. Hope i'm clear

Answer 1

You could use the following query:

SELECT  table1.id 
FROM    table1 
        LEFT JOIN table2 
            ON table1.id IN (table2.user_one, table2.user_two)
WHERE   table2.user_one IS NULL;

Although, depending on your indexes on table2 you may find that two joins performs better:

SELECT  table1.id 
FROM    table1 
        LEFT JOIN table2 AS t1
            ON table1.id = t1.user_one
        LEFT JOIN table2 AS t2
            ON table1.id = t2.user_two
WHERE   t1.user_one IS NULL
AND     t2.user_two IS NULL;