0

I am hoping someone might be able to help me with this issue I am having. I have a table of customers - let's call it Table C. And I have a second table of customers that are not to be called - let's call it Table D.

I would like to pull all of the needed information (name, address, phone, etc...) from Table C unless the customer appears in Table D.

In the example shown below, I'd like to have data returned for all customers except John Doe (ID: 1) and Fred Savage (ID: 5)

Data Example

I think a RIGHT OUTER JOIN might be applicable here, but I have not used this type of join before.

MISNole
  • 992
  • 1
  • 22
  • 48
  • Did your research turn up [`EXCEPT`](https://msdn.microsoft.com/en-us/library/ms188055.aspx), or is it not available in the version of SQL Server you're using? – HABO Aug 14 '15 at 23:40

4 Answers4

2

Use NOT EXISTS to do this:

SELECT c.*
FROM tableC c
WHERE NOT EXISTS (
        SELECT *
        FROM tableD d
        WHERE c.customerID = d.customerid
        );
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • Thanks - Is there a performance hit for using "Select *" within the sub-query instead of using select D.CustomerID since that is basically all I'm checking for? – MISNole Aug 14 '15 at 21:32
  • Quite honestly, `NOT EXISTS` is exactly built for your type of scenario. As far as I understand, you would not have performance hit with the `select *` since you have a where clause in the subquery. You could change it to `Select customerid` and see if you find any perfomance gain. – FutbolFan Aug 14 '15 at 21:35
2

If you want to use a join then it's a left join you want with a filter for null values in the d table. A right join would have gotten you all the rows from the d table, plus the matching ones from the c table - quite the opposite of what you want, but if you had switched the tables around then you would have gotten the same result, so this:

select c.* from c
left join d on c.CustomerID = d.CustomerID
where d.CustomerID is null

is equivalent to:

select c.* from d
right join c on c.CustomerID = d.CustomerID
where d.CustomerID is null;

Personally I would prefer using either a correlated not exists query or not in (but beware of null values) as I think those convey the intent more clearly.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • Thanks for the time you took to help - I did end up using the `NOT EXISTS` code - the fields are IDs and should not be NULL. – MISNole Aug 14 '15 at 22:03
  • @MISNole I would have used `not exists` too. :) I just wanted to add an answer using joins for clarity. – jpw Aug 14 '15 at 22:04
1
Select * from table.c where customer_id not in (select distinct customer_id from table.d);
Abhis
  • 585
  • 9
  • 25
  • This may work in this case, but one has to be careful while using `NOT IN` in the query. `The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.` – FutbolFan Aug 14 '15 at 21:30
  • @FutbolFan Yeah true. – Abhis Aug 14 '15 at 21:34
-1

Yes you want an outer join. Try this: https://technet.microsoft.com/en-US/library/ms187518(v=SQL.105).aspx

EAnders
  • 112
  • 8
  • I'll have to read that and try out outer right joins. I understand left outer - but right outer for some reason don't make sense to me. – MISNole Aug 14 '15 at 21:34
  • @MISNole I think you'll find this answer informative: http://stackoverflow.com/a/4715847/1175077 – jpw Aug 14 '15 at 21:37
  • More nerdy comparisons http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ – EAnders Aug 14 '15 at 21:38