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

User · Answer

Let we have the following 2 tables salary and employee    Now i want those records from employee table which are not in salary  We can do this in 3 ways    Using inner Join   select   from employee where id not in select e id from employee e inner join salary s on e id s id       Using Left outer join    select   from employee e  left outer join salary s on e id s id  where s id is null      Using Full Join   select   from employee e full outer join salary s on e id s id where e id not in select id from salary

User · Answer

SELECT id FROM table1 WHERE foreign key id column NOT IN  SELECT id FROM table2    Table 1 has a column that you want to add the foreign key constraint to  but the values in the foreign key id column don t all match up with an id in table 2    The initial select lists the ids from table1  These will be the rows we want to delete   The NOT IN clause in the where statement limits the query to only rows where the value in the foreign key id column is not in the list of table 2 ids   The SELECT statement in parenthesis will get a list of all the ids that are in table 2

User · Answer

I would use EXISTS expression since it is more powerful  you can e g  more precisely choose rows you would like to join  In the case of LEFT JOIN  you have to take everything that s in the joined table  Its efficiency is probably the same as in the case of LEFT JOIN with null constraint  SELECT t1 ID FROM Table1 t1 WHERE NOT EXISTS  SELECT t2 ID FROM Table2 t2 WHERE t1 ID   t2 ID

User · Answer

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

User · Answer

Where T2 is the table to which you re adding the constraint   SELECT   FROM T2 WHERE constrained field NOT IN       SELECT DISTINCT t constrained field     FROM T2      INNER JOIN T1 t     USING   constrained field       And delete the results

User · Answer

I Dont Knew Which one Is Optimized  compared to  AdaTheDev   but This one seems to be quicker when I use  atleast for me    SELECT id  FROM  table 1 EXCEPT SELECT DISTINCT  table1 id  table1 id FROM table 2   If You want to get any other specific attribute you can use   SELECT COUNT    FROM table 1 where id in  SELECT id  FROM  table 1 EXCEPT SELECT DISTINCT  table1 id  table1 id FROM table 2

User · Answer

From similar question here MySQL Inner Join Query To Get Records Not Present in Other Table I got this to work  SELECT   FROM bigtable  LEFT JOIN smalltable ON bigtable id   smalltable id  WHERE smalltable id IS NULL   smalltable is where you have missing records  bigtable is where you have all the records  The query list all the records that not exist in smalltable but exists on the bigtable  You could replace id by any other matching criteria

User · Answer

You can do something like this     SELECT IFNULL  price   fPrice  100  as fPrice product ProductId ProductName            FROM  products  left join  price  ON            price ProductId product ProductId AND  GeoFancingId 1 OR GeoFancingId            IS NULL  WHERE Status  Active  AND Delete  No

User · Answer

You could opt for Views as shown below   CREATE VIEW AuthorizedUserProjectView AS select t1 username as username  t1 email as useremail  p id as projectid    select m role from userproject m where m projectid   p id and m userid   t1 id  as role  FROM authorizeduser as t1  project as p   and then work on the view for selecting or updating   select   from AuthorizedUserProjectView where projectid   49   which yields the result as shown in the picture below i e  for non-matching column null has been filled in    Result of select on the view  1

User · Answer

SELECT   FROM First table  MINUS SELECT   FROM another

User · Answer

How to select rows with no matching entry in Both table         select   from  dbo   EmppDetails  e      right join  Employee   Gender  d on e Gid d Gid     where e Gid is Null      union      select   from  dbo   EmppDetails  e      left join  Employee   Gender  d on e Gid d Gid     where d Gid is Null

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

Examples related to sql

Examples related to foreign-keys