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

3

6 Answers

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;

One of the best approach if you do not want to return any columns from table2 is to use the NOT EXISTS

SELECT table1.id
FROM table1 T1
WHERE NOT EXISTS (SELECT * FROM table2 T2 WHERE T1.id = T2.user_one OR T1.id = T2.user_two)

Semantically this says what you want to query: Select every row where there is no matching record in the second table.

MySQL is optimized for EXISTS: It returns as soon as it finds the first matching record.

One more note to this: When you check against a nullable column in the joined table, depending on your needs, you may have to use NOT EXISTS (or check against other columns when using LEFT JOIN), because MySQL won't be able to distinguish between a column which is NULL, but there is an existing record in the joined table and a column which is generated as NULL because the joined table have no matching record.

Here is a query that returns only the rows where no correspondance has been found in both columns user_one and user_two of table2:

SELECT T1.*
FROM table1 T1
LEFT OUTER JOIN table2 T2A ON T2A.user_one = T1.id
LEFT OUTER JOIN table2 T2B ON T2B.user_two = T1.id
WHERE T2A.user_one IS NULL AND T2B.user_two IS NULL

There is one jointure for each column (user_one and user_two) and the query only returns rows that have no matching jointure.

Hope this will help you.

SELECT table1.id
FROM table1
LEFT JOIN table2 ON table1.id = table2.user_one
WHERE table2.user_one is NULL

Try following query:-

 SELECT table1.id FROM table1 where table1.id NOT IN (SELECT user_one FROM Table2 UNION SELECT user_two FROM Table2)

Hope this helps you.

Try:

SELECT A.id FROM
( SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.user_one WHERE table2.user_one IS NULL
) A
JOIN ( SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.user_two WHERE table2.user_two IS NULL
) B
ON A.id = B.id

See Demo

Or you could use two LEFT JOINS with aliases like:

SELECT table1.id FROM table1 LEFT JOIN table2 A ON table1.id = A.user_one LEFT JOIN table2 B ON table1.id = B.user_two WHERE A.user_one IS NULL AND B.user_two IS NULL

See 2nd Demo

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy