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

@GordonLinoff Just for me can you plz expalin this logic to add where table2.user_one is null because i think it will return 2 and 3 both, but user only wants 3. — Ankit Bajpai
– Ankit Bajpai, Commented Sep 17, 2014 at 12:33
What is the structure of table2? Two columns user_one and user_two? — Joël Salamin
– Joël Salamin, Commented Sep 17, 2014 at 12:34

Andrew · Accepted Answer · 2018-12-14 20:39:30Z

81

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;

edited Dec 14, 2018 at 20:39

Andrew

20.4k13 gold badges110 silver badges122 bronze badges

answered Sep 17, 2014 at 12:38

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pred · Accepted Answer · 2022-05-24 11:11:38Z

33

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.

edited May 24, 2022 at 11:11

answered Sep 17, 2014 at 13:26

Pred

9,0903 gold badges28 silver badges47 bronze badges

1 Comment

Jonathan Head Over a year ago

The "One more note" explanation was the key for resolving a similar issue I was experiencing. I had no idea MySQL won't being able to distinguish between the two NULL values in this case. Many thanks.

Joël Salamin · Accepted Answer · 2014-09-17 12:38:22Z

5

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.

answered Sep 17, 2014 at 12:38

Joël Salamin

3,5663 gold badges24 silver badges33 bronze badges

Comments

Ankit Bajpai · Accepted Answer · 2014-09-17 12:35:49Z

3

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.

answered Sep 17, 2014 at 12:35

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Comments

Chris · Accepted Answer · 2014-09-17 12:36:59Z

3

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

answered Sep 17, 2014 at 12:36

Chris

8,10910 gold badges44 silver badges68 bronze badges

Comments

Edper · Accepted Answer · 2014-09-17 12:49:28Z

2

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

edited Sep 17, 2014 at 12:49

answered Sep 17, 2014 at 12:42

Edper

9,3621 gold badge32 silver badges49 bronze badges

Collectives™ on Stack Overflow

MySQL select rows where left join is null

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related