How to exclude rows that do not join with another table?

Question

I have two tables, one has a primary key, the other as a foreign key.

I want to extract data from the primary table only if there is no record in the secondary table containing its key. The opposite kind of simple inner join, which returns only the rows that are joined by this key.

Answer 1

SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL 

Full image of joining

From Atom: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

Answer 2

 SELECT * FROM primarytable P WHERE NOT EXISTS (SELECT * FROM secondarytable S WHERE P.PKCol = S.FKCol) 

As a rule, (NOT) EXISTS is the best choice, then (NOT) IN or (LEFT) JOIN

Answer 3

use the "does not exist" left join:

 SELECT p.* FROM primary_table p LEFT JOIN second s ON p.ID = s.ID WHERE s.ID IS NULL 

Answer 4

 SELECT P.* FROM primary_table P LEFT JOIN secondary_table S on P.id = S.p_id WHERE S.p_id IS NULL 

Answer 5

If you want to select the columns from the first table, which are also present in the second table, then in this case you can also use EXCEPT . In this case, the column names may be different, but the data type should be the same.

Example:

 select ID, FName from FirstTable EXCEPT select ID, SName from SecondTable 

Answer 6

Another solution:

 SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2) 

Answer 7

It was useful to use it in COGNOS because the creation of the SQL query "Not in" in Cognos was allowed, but it took too much time to start. I had a manually encoded table A to join table B in Cognos, since A.key was not in B.key, but the query took too long or did not return results after 5 minutes.

For anyone looking for a “NOT IN IN” solution in Cognos, here's what I did. Create a query that joins tables A and B with LEFT JOIN in Cognos, choosing the type of link: table A.Key has values ​​from 0 to N in table B and then adds a filter (they correspond to Where Clauses) for: table B.Key - NULL

Quick and fast, like a charm.

Answer 8

I have another call. What if I want to exclude rows that are not joined with two other tables