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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT table1 id  FROM table1  LEFT JOIN table2 ON table1 id   table2 user one WHERE table2 user one is NULL

User · Answer

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

[mysql] MySQL select rows where left join is null

Examples related to mysql

Examples related to sql

Examples related to join

Examples related to left-join