Sql-server – Exclusionary Left Joins

join;sql serversql-server-2012

I'm trying to join 2 different tables (Table99 and Table2) to Table1. The first is a straight-forward RIGHT JOIN. The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2, and bring back results from Table1 that don't exist in Table2

Example:

 FROM Table1 tbl1 
      RIGHT JOIN Table99 tbl99 ON tbl1.CARD_ID = tbl99.CARD_ID 
            AND tbl1.DATE_OPEN = tbl99.DATE_OPEN 

      LEFT JOIN Table2 tbl2 ON tbl1.ACCOUNT_NUMBER = tbl2.ACCOUNT_NUMBER 
           AND tbl1.APPL_ID = tbl2.APPL_ID'

WHERE tbl1.ACCOUNT_NUMBER <> tbl2.ACCOUNT_NUMBER

The Left Join clause is where I'm trying to pull in all of the results from tble1 that do NOT exist in tbl2

Best Answer

bring back results from Table1 that don't exist in Table2

You can use NOT EXISTS 

SELECT
    T1.*
FROM
    TABLE1 T1
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER 
                        AND T2.APPL_ID = T1.APPL_ID );

Update

It doesn't matter how many JOIN's you use before the left anti semi join, it returns all rows from the left side that does not exists on the right side, according the predicate: WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND T2.APPL_ID = T1.APPL_ID

SELECT
    T1.*
FROM
    TABLE1 T1
RIGHT JOIN 
    TABLE99 T99
    ON T99.CARD_ID = T99.CARD_ID 
    AND T99.DATE_OPEN = T1.DATE_OPEN
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND  T2.APPL_ID = T1.APPL_ID
                );

Let's imagine 3 tables with next ID's:

 T1    T2    T9
----  ----  ----
 01    01    01
 02          02
 03    03
 04    04    04
 05          05

Give me all ID's that exists in T1 and T9.

SELECT ID FROM T1 JOIN T9 ON T1.ID = T2.ID;

01,02,04,05

and now, give me all ID's of this group that does not exists in T2:

pseudo-code:

SELECT ID FROM (01,02,04,05) WHERE NOT EXISTS IN (01,03,04);

02,05

NOTE: I've replaced table names by their values to make the final result more clear

Related Question