Compare two MySQL tables and select distinct entries in second one

Question

I have two MySQL tables with one column in each. I want to select the entries in second table that does not exist in the first one.

I tried the below query, but seems does not work

SELECT column_name FROM `table_name_1` 
WHERE NOT EXISTS (SELECT column_name FROM `table_name_2`); 

For example: table_name_1

column 
111111111111
222222222222
333333333333
444444444444

table_name_2

column
222222222222
333333333333
555555555555
666666666666

Now, I want to get only 55555555555 and 66666666666 entries in table_name_2.

Answer 1

You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)

Also, note that, for performance you will need Index on both the columns in their respective tables.

Try the following instead:

SELECT DISTINCT t2.column_name  
FROM table_name_2 AS t2 
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name 
WHERE t1.column_name IS NULL 

Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.

---

Result:

| column_name  |
| ------------ |
| 555555555555 |
| 666666666666 |

View on DB Fiddle