• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1198
  • Last Modified:

MySQL: Select rows from one table that do not exist on another table

I want to select all rows from table2 where table2 has a value in "isbn" that does not exist on table1.

This does not return anything:
SELECT * FROM table2 JOIN table1 on table2.isbn = table1.isbn WHERE table2.isbn != table1.isbn

Open in new window

0
hankknight
Asked:
hankknight
  • 2
3 Solutions
 
ravikantninaveCommented:
If the tables have compound primary keys, you'll have to use a "NOT EXISTS" clause

SELECT * 
  FROM TableA AS a 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableB b 
                    WHERE b.id1 = a.id1 
                          AND b.id2 = a.id2 
                          AND b.id3 = a.id3
                  );

Open in new window


or
SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id WHERE TableB.id IS NULL

Open in new window

0
 
Proud_GrandpaCommented:
Would this work?

SELECT table2.* FROM table1, table2 WHERE table1.isbn NOT IN table2.isbn;
0
 
ravikantninaveCommented:
This works perfectly:

SELECT Table_2.* FROM Table_2 LEFT JOIN Table_1 ON Table_2.isbn = Table_1.isbn WHERE Table_1.isbn  IS NULL
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now