How to exclude rows that don’t join with another table?

By Posted on

How to exclude rows that don’t join with another table? – This article will take you through the common SQL errors that you might encounter while working with sql, join,  . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

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.

Solution :

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 join

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

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

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

Another solution is:

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

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

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

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.

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.