SQL select clause with a compound

Question

I have two lookup/reference tables (Subscribed and Unsubscribed) in my Microsoft SQL Server 2008 database with the following structure:

UserId int
PublicationId int

These fields are indexed together as a compound index.

What I want to be able to do is find all of the records that exist in the Unsubscribed table that do not have a matching record in the Subscribed table (matching UserId and PublicationId)

Functionally, I want something like:

select PublicationId, UserId
from Unsubscribed
where PublicationId, UserId not in (
   select PublicationId, UserId
   from Subscribed
)

Can anyone point me in the right direction?

Thanks.

Thank you to everyone that responded to this question. All of your responses were very useful to me and I will use as a reference in the future. On this occasion the EXCEPT clause seems to give me everything I need. Thanks gain. — Neilski, Feb 16 '12 at 13:44

score 3 · Accepted Answer · answered Feb 16 '12 at 12:50

3

SELECT PublicationId, UserId
FROM   Unsubscribed
MINUS
SELECT PublicationId, UserId
FROM   Subscribed

answered Feb 16 '12 at 12:50

Szilard Barany

1,125
7
14

2

If SQL product is not Oracle then replace `MINUS` with `EXCEPT`. – onedaywhen Feb 16 '12 at 12:58

Kane · Answer 2 · 2012-02-16T12:55:23.543

2

You can use a left join to find the non matching publications and users.

SELECT U.[PublicationId], U.[UserId]
FROM [Unsubscribed] AS U
    LEFT JOIN [Subscribed] AS S ON S.[PublicationId] = U.[PublicationId]
        AND S.[UserId] = U.[UserId]
WHERE S.[PublicationId] IS NULL
    AND S.[UserId] IS NULL

Or if you are using Microsoft SQL Server 2005 / 2008 then you can use the Except keyword (use the Intersect keyword for the opposite).

SELECT [PublicationId], [UserId]
FROM [Unsubscribed] 

EXCEPT

SELECT [PublicationId], [UserId]
FROM [Subscribed]

edited Feb 16 '12 at 12:55

answered Feb 16 '12 at 12:49

Kane

16,471
11
61
86

The `[]` are used in SQL-Server only. – ypercubeᵀᴹ Feb 16 '12 at 13:00
Ahh yes, apologies I am a Microsoft guy, don't judge me too harshly :) – Kane Feb 16 '12 at 13:03

score 2 · Answer 3 · answered Feb 16 '12 at 12:57

You can always convert IN to EXISTS. In your case, this would look like this:

select PublicationId, UserId
from Unsubscribed
where
   not exists (
      select *
      from Subscribed
      where Subscribed.PublicationId = Unsubscribed.PublicationId
         and Subscribed.UserId = Unsubscribed.UserId
   )

Incidentally, if you are using Oracle, you can actually implement your original intent directly (just add a couple of parenthesis):

select PublicationId, UserId
from Unsubscribed
where (PublicationId, UserId) not in (
   select PublicationId, UserId
   from Subscribed
)

score 1 · Answer 4 · answered Feb 16 '12 at 12:50

You can use a LEFT JOIN to achieve this;

SELECT U.*, S.PublicationId
FROM Unsubscribed U
LEFT JOIN Subscribed S ON U.PublicationId = S.PublicationId AND U.UserId = S.UserId
WHERE S.PublicationId IS NULL

If you are new to join's, Jeff Atwood's Visual Explanation is a good place to start.

Effectively, what the query is doing is bringing back all of the rows in ubsubscribed that have a matching row in Subscribed, and all of the rows in Unsubscribed that have no matching rows in subscribed - the rows in subscribed are represented with NULL's for these.

SQL select clause with a compound

4 Answers4