CODEWITHSUNDEEP
sql
George Mauer
George Mauer

Reputation: 122212

Get rows that don't match rows in another table

In SQL, how would I do something like a reverse join?

For example suppose I have the following two tables

UsedSlide
    SlideId
    UserId
    SomeOtherValue

LegacySlide
   SlideId
   UserId

How would I select all rows in UsedSlide where the SlideId and UserId don't match the values in any row of LegacySlide?

Note that I specifically made it two things we're matching on since otherwise I know that I can use NOT IN and a subselect.

Bonus: In my scenario the dataset is small, but what if it was large? How do I do it most efficiently?

Upvotes: 2

Views: 1383

Answers (3)

Robert Lewis
Robert Lewis

Reputation: 11

Use the EXCEPT operator:

Some sample code I have tested:

CREATE TABLE #UsedSlide (SlideId INT NOT NULL, UserId INT NOT NULL, SomeOtherValue VARCHAR(10) NOT NULL)
CREATE TABLE #LegacySlide (SlideId INT NOT NULL, UserId INT NOT NULL)

INSERT INTO #UsedSlide(SlideId, UserId, SomeOtherValue)
VALUES
(1, 35, 'testing123'),
(2, 39, 'testingabc'),
(3, 24, 'testingxyz')

INSERT INTO #LegacySlide( SlideId, UserId )
VALUES  (1, 35),
        (2, 39)

SELECT SlideId, UserId
FROM #UsedSlide
EXCEPT
SELECT SlideId, UserId
FROM #LegacySlide

This produces the following result set:

SlideId UserId
------- ------
3       24

Note: the order of statements in the EXCEPT is important here. If you ran that last statement as:

SELECT SlideId, UserId
FROM #LegacySlide
EXCEPT
SELECT SlideId, UserId
FROM #UsedSlide

...that would NOT produce the desired effect. This is basically an operation on sets: find the "tuple" that is not in the set of tuples.

The companion operator to EXCEPT is INTERSECT - find tuples in common between two sets. Both are very useful operators.

As an aside, I believe that Oracle has a MINUS operator that is roughly equivalent to EXCEPT (someone can verify and find the link?)

Upvotes: 1

radar
radar

Reputation: 13425

LEFT JOIN CAN BE USED

  SELECT * from
  UsedSlide US
  LEFT JOIN LegacySlide LS
  ON US.SlideId = LS.SlideId
  and US.UserId = LS.UserId
  WHERE LS.SlideId is NULL
  AND LS.UserId is NULL

Upvotes: 2

Mureinik
Mureinik

Reputation: 312219

You could use the not exists operator:

SELECT *
FROM   UsedSlide u
WHERE  NOT EXISTS (SELECT *
                   FROM   LegacySlide l
                   WHERE  u.SlideId = l.SlideId AND u.UserId = l.UserId)

Upvotes: 4

Related Questions