How to select rows with no matching entry in another table?

Question

I'm doing some maintenance work on a database application and I've discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there's no foreign key constraints on the tables.

I'm trying to add FK constraints on these columns, but I'm finding that, because there's already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don't match up to the other table and then delete them.

I've found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don't understand why they work.

Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it's doing, so that I can make these queries myself, rather than coming running to SO for every table in this mess that has no FK constraints?

Answer 1

Use this code:

SELECT ta.ID

FROM TableA ta

LEFT JOIN TableB tb ON ta.ID = tb.ID

WHERE tb.ID IS NULL

key points are as follows:

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

Here, LEFT JOIN is used to return all the rows from TableA even though they don’t match with the rows in TableB.   

You can observe that WHERE tb.ID IS NULL clause; there will be no records in TableB for the particular ID from TableA. In TableA, if all the records in which ID don’t match with TableB then the ID will be returned as NULL.     

Answer 2

You'd have a LEFT JOIN and throw in a WHERE clause so that rows will be gone where there was no match. And now, how that looks goes like this:

You got two tables, table1 and table2. And you wish to get from table1 the rows which have that no column in table1 say col1 has there a matching value in table2.

SELECT t1.*

FROM table1 AS t1

LEFT OUTER JOIN table2 AS t2 ON t1.col1 = t2.col3

WHERE t2.col3 IS NULL;

Answer 3

You'd have a LEFT JOIN and throw in a WHERE clause so that rows will be gone where there was no match. And now, how that looks goes like this:

You got two tables, table1 and table2. And you wish to get from table1 the rows which have that no column in table1 say col1 has there a matching value in table2.

SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.col1 = t2.col3 WHERE t2.col3 IS NULL;