How to exclude rows that don t join with another table

Question

I have two tables  one has primary key other has it as a foreign key   I want to pull data from the primary table  only if the secondary table does not have an entry containing it s key  Sort of an opposite of a simple inner join  which returns only rows that join together by that key

User · Accepted Answer

SELECT  lt select list gt   FROM Table A A LEFT JOIN Table B B ON A Key   B Key WHERE B Key IS NULL   Full image of join   From aticle   http   www codeproject com KB database Visual SQL Joins aspx

User · Answer

use a  not exists  left join   SELECT p   FROM primary table p LEFT JOIN second s ON p ID   s ID WHERE s ID IS NULL

User · Answer

SELECT      FROM    primarytable P WHERE    NOT EXISTS  SELECT   FROM secondarytable S      WHERE          P PKCol   S FKCol    Generally   NOT  EXISTS is a better choice then  NOT  IN or  LEFT  JOIN

User · Answer

SELECT P   FROM primary table P LEFT JOIN secondary table S on P id   S p id WHERE S p id IS NULL

User · Answer

This was helpful to use in COGNOS because creating a SQL  Not in  statement in Cognos was allowed  but it took too long to run   I had manually coded table A to join to table B in in Cognos as A key  not in  B key  but the query was taking too long not returning results after 5 minutes   For anyone else that is looking for a  NOT IN  solution in Cognos  here is what I did   Create a Query that joins table A and B with a LEFT JOIN in Cognos by selecting link type  table A Key has  0 to N  values in table B  then added a Filter  these correspond to Where Clauses  for  table B Key is NULL   Ran fast and like a charm

User · Answer

Another solution is   SELECT   FROM TABLE1 WHERE id NOT IN  SELECT id FROM TABLE2

User · Answer

If you want to select the columns from First Table  which are also present in Second table  then in this case you can also use EXCEPT  In this case  column names can be different as well but data type should be same   Example   select ID  FName from FirstTable EXCEPT select ID  SName from SecondTable

[sql] How to exclude rows that don't join with another table?

Examples related to sql

Examples related to join