MySQL get data from another table with duplicate ID/data

Question

How to query data from table_1 which ID is not available on table_2 that has duplicate ID's. See example below.

I want to get ID 5 and 6 of Table 1 from Table 2

Table 1
-------------
| ID | Name |
| 1  | a    |
| 2  | b    |
| 3  | c    |
| 4  | d    |
| 5  | e    |
| 6  | f    |
-------------

Table 2
-------------
Table 1 ID  |
| 1         |
| 1         |
| 2         |
| 2         |
| 2         |
| 3         |
| 4         |
-------------

Thanks!

Answer 1

Use:

   SELECT t.id
     FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.id = t1.id
    WHERE t2.id IS NULL

Using NOT EXISTS:

   SELECT t.id
     FROM TABLE_1 t1
    WHERE NOT EXISTS(SELECT NULL
                       FROM TABLE_2 t2
                      WHERE t2.id = t1.id)

Using NOT IN:

   SELECT t.id
     FROM TABLE_1 t1
    WHERE t1.id NOT IN (SELECT t2.id
                          FROM TABLE_2 t2)

Because there shouldn't be NULL values in table2's id column, the LEFT JOIN/IS NULL is the fastest means: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

Answer 2

If I am understanding you correctly you want to do an outer join. In this case it would be:

SELECT * FROM 
table_1 LEFT JOIN ON table_2
ON table_1.id = table_2.id
WHERE table_2.id is NULL

Answer 3

Minus query would be very helpful, see this link: minus query replacement for your data this would look like this:

SELECT table_1.id FROM table_1 LEFT JOIN table_2 ON table_2.id = table_1.id WHERE table_2.id IS NULL

Answer 4

This one does what you want:

Select t1.id
From table1 t1
Left Join table2 t2
On t2.id = t1.id
Where t2.id Is Null

Result:

id
--
5
6