How to select rows with no matching entry in another table?
SqlForeign KeysSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 fromTable1
, regardless of whether or not there is a matching row inTable2
. -
The
WHERE t2.ID IS NULL
clause; this will restrict the results returned to only those rows where the ID returned fromTable2
is null - in other words there is NO record inTable2
for that particular ID fromTable1
.Table2.ID
will be returned as NULL for all records fromTable1
where the ID is not matched inTable2
.
Solution 2 - Sql
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)
Solution 3 - Sql
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
id
s 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 theforeign_key_id_column
is not in the list of table 2id
s. - The
SELECT
statement in parenthesis will get a list of all theid
s that are in table 2.
Solution 4 - Sql
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)
Solution 5 - Sql
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.
Solution 6 - Sql
From similar question here https://stackoverflow.com/questions/9954749/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.
Solution 7 - Sql
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);
Solution 8 - Sql
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]
Solution 9 - Sql
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"
Solution 10 - Sql
SELECT * FROM First_table MINUS SELECT * FROM another
Solution 11 - Sql
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