CODEWITHSUNDEEP
mysql
user1876234
user1876234

Reputation: 857

Compare 2 tables and find non duplicate entries

I have 2 tables. I want to check if columns of table 1 don't have duplicates in columns of table2.

Here is how the search should work! searching duplicate rows

If no duplicates are found, I want to get the row name from table1.

Upvotes: 0

Views: 4185

Answers (2)

Theolodis
Theolodis

Reputation: 5102

If I got you right, this is what you want.

SELECT 
    t1.name 
FROM 
    Table1 t1 
WHERE 
    t1.name 
NOT IN 
    (
        SELECT t2.name 
        FROM Table2 t2
        JOIN t1 
        ON t2.name = t1.name
    )

Upvotes: 2

spencer7593
spencer7593

Reputation: 108420

You need to specify a column (or columns) that you will use to "match" the rows, to determine whether they are "duplicates".

I'm going to assume (absent any schema information), that the column name is id.

An "anti-join" pattern is usually the best performing option:

SELECT a.id
  FROM table1 a
  LEFT
  JOIN table2 b
    ON a.id = b.id
 WHERE b.id IS NULL

(Performance is dependent on a whole bunch of factors.)

Your other options are to use a NOT EXISTS predicate:

SELECT a.id
  FROM table1 a
 WHERE NOT EXISTS
       ( SELECT 1
           FROM table2 b
          WHERE b.id = a.id
       )

Or, use a NOT IN predicate:

SELECT a.id
  FROM table1 a
 WHERE a.id NOT IN
       ( SELECT b.id
           FROM table2 b
          WHERE b.id IS NOT NULL
       )

The generated execution plan and performance of each of these statements will likely differ. With large sets, the "anti-join" pattern (the first query) usually performs best.

Upvotes: 1

Related Questions