SQL Join: Everything in the LEFT table that is NOT IN the RIGHT Table

Question

I have two tables, "Affiliates" and "ControlPanels".

ControlPanels has a field (CP_AffiliateID) that maps to Affiliates (A_ID).

I want to select all records from Affiliates that DO NOT have a match in CP_AffiliateID.

The fields A_ID is a Primary Key so I'm not worried about matching NULL.

What would that join look like?

`Affiliates LEFT OUTER JOIN ControlPanels ON A_id = cp_affiliateID WHERE ControlPanels.CP_AffiliateID IS NULL` OR you could do `FROM Affiliates aff WHERE NOT EXISTS (SELECT 1 FROM ControlPanels cp WHERE cp.cp_affiliate_id = aff.a_id)` — JNevill, Apr 23 '18 at 19:17
@JNevill, THANKS! I don't think it's a dup b/c I'm not concerned with matching on NULL, as A_ID is a Primary Key. HTH. — nbardach, Apr 23 '18 at 19:25
@nbardach look more closely at the duplicate and how it relates to your question. Looking for NULL in the `CP_AffiliateID` is absolutely a correct way to do this. It matters not that `A_ID` is a Primary Key. — Tab Alleman, Apr 23 '18 at 19:27
@Tab Aleman, actually, it's not that I want all records from Affiliates where CP_AffiliateID IS NULL. It's just that if there's is '5' in CP_AffiliateID, I don't want to include the record from Affiliates where A_ID = '5'; — nbardach, Apr 23 '18 at 19:28

Hardik Masalawala · Accepted Answer · 2018-04-23T19:44:26.690

2

Please try NOT IN

SELECT * 
FROM   affiliates 
WHERE  affiliates.a_id NOT IN (SELECT cp_affiliateid 
                                  FROM   controlpanels)

edited Apr 23 '18 at 19:44

answered Apr 23 '18 at 19:32

Hardik Masalawala

I think this is the most elegant solution. Thanks, Hardik! – nbardach Apr 23 '18 at 19:46
Welcome @nbardach :) – Hardik Masalawala Apr 23 '18 at 20:11

score 1 · Answer 2 · answered Apr 23 '18 at 19:24

1

You don't really need a join for this and can just use != ALL (subquery).

SELECT * FROM Affiliates A 
WHERE a.A_ID != ALL (SELECT C.CP_AffiliateID FROM ControlPanels C)

answered Apr 23 '18 at 19:24

Brian

1

This is valid for SQL Server??? i've never heard of this syntax. – Tab Alleman Apr 23 '18 at 19:28
Since SQL Server 2008. ref: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/all-transact-sql?view=sql-server-2017 – Brian Apr 23 '18 at 19:29
Yeah, I very, very rarely see anyone use ALL, SOME, ANY, or even != http://www.blackwasp.co.uk/SQLAllSome.aspx – dfundako Apr 23 '18 at 19:32
1

Looks like it does the same thing as NOT IN. – Tab Alleman Apr 23 '18 at 19:34
Interesting syntax. I've never used ALL before. – nbardach Apr 23 '18 at 19:46

2 Answers2