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

 

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?

Here's a simple query: 

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

  • LEFT JOIN is used; this will return ALL rows from Table1 , regardless of whether or not there is a matching row in Table2 .

  • The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1 . Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2 .

    • I would use EXISTS expression since it is more powerfull, you can ie more precisely choose rows you would like to join, in case of LEFT JOIN you have to take everything what's in joined table. Its efficiency is probably same as in case of LEFT JOIN with null test. 

    SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)

    Where T2 is the table to which you're adding the constraint: 

    SELECT *
FROM T2
WHERE constrain_field NOT
IN (
    SELECT DISTINCT t.constrain_field
    FROM T2 
    INNER JOIN T1 t
    USING ( constrain_field )
)

    And delete the results.

    链接地址: http://www.djcxy.com/p/94222.html

    上一篇: fin，复制和粘贴的SQL代码：

    下一篇: 如何在另一个表中选择没有匹配条目的行？