How to do a Select in a Select

Question

I have a table containing a unique ID field. Another field (REF) contains a reference to another dataset's ID field. Now I have to select all datasets where REF points to a dataset that doesn't exist.

SELECT * FROM table WHERE ("no dataset with ID=REF exists")

How can I do this?

Answer 1

3 ways

SELECT * FROM YourTable y WHERE NOT EXISTS 
     (SELECT * FROM OtherTable o WHERE y.Ref = o.Ref)

SELECT * FROM YourTable WHERE Ref NOT IN 
     (SELECT Ref FROM OtherTable WHERE Ref IS NOT NULL)

SELECT y.* FROM YourTable y 
LEFT OUTER JOIN  OtherTable o ON y.Ref = o.Ref
WHERE o.Ref IS NULL

See also Five ways to return all rows from one table which are not in another table

Answer 2

Try this:

SELECT * FROM TABLE WHERE NOT EXISTS 
     (SELECT * FROM OtherTable WHERE TABLE.Ref = OtherTable.ID)

Answer 3

I think this should work

SELECT * FROM table WHERE id NOT IN (SELECT ref_id FROM ref_table)

or with JOIN

SELECT table.* 
FROM table LEFT JOIN ref_table ON table.id = ref_table.ref_id
WHERE ref_table.ref_id IS NULL

Answer 4

SELECT 
 table1.* 
FROM 
 table1
 LEFT JOIN table2 ON table1.id = table2.ref
WHERE 
 table2.ref IS NULL

Answer 5

You can do a subquery like:

select * from table where somefield not in (select otherfield from sometable where ID=REF)

Answer 6

SELECT * 
FROM table 
WHERE ((SELECT COUNT(*) FROM table2 WHERE table2.id = table.ref) = 0)

Answer 7

Something like that :

SELECT * FROM table WHERE ID NOT IN(SELECT REF FROM Table2 )

Answer 8

Yes you can use

select * from x where not exist ( select * from y )