68

Could some one please provide how to write following sql query using joins. I do not want use not in as well as if possible I would like to replace where condition as well.

SELECT d1.Short_Code
FROM domain1 d1
WHERE d1.Short_Code NOT IN (
  SELECT d2.Short_Code
  FROM Domain2 d2
)

I am using SQL Server 2008

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
manu
  • 1,807
  • 4
  • 25
  • 32

3 Answers3

91

This article:

may be if interest to you.

In a couple of words, this query:

SELECT  d1.short_code
FROM    domain1 d1
LEFT JOIN
        domain2 d2
ON      d2.short_code = d1.short_code
WHERE   d2.short_code IS NULL

will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct.

You can also use this:

SELECT  short_code
FROM    domain1
EXCEPT
SELECT  short_code
FROM    domain2

This is using neither NOT IN nor WHERE (and even no joins!), but this will remove all duplicates on domain1.short_code if any.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    @Quassnoi, could you please provide the efficient way way writing the same query using either "NOT NULL" and/or "NOT EXISTS" – manu Apr 15 '11 at 12:36
  • 1
    @Elan: your original query is just nice. Just create the indexes on `short_code` in both tables. – Quassnoi Apr 15 '11 at 12:38
  • @Quassnoi - You are assuming he doesn't need any information from the domain2 table – Aducci Apr 15 '11 at 12:50
  • 2
    @Aducci: I'm assuming nothing, I'm just rewriting the original query. – Quassnoi Apr 15 '11 at 12:53
  • @Quassnoi - You say it is less efficient. – Aducci Apr 15 '11 at 13:19
  • 2
    @Aducci: `LEFT JOIN` is less efficient indeed. The question was "how to rewrite without `NOT IN`" — here's how. – Quassnoi Apr 15 '11 at 13:20
  • 1
    DUDE you are SUPER AWESOME :) Thanks for the above query i was stuck somewhere like hell and You are a SAVIOR :) – Vinay Chopra Mar 19 '13 at 12:11
  • @VinayChopra: my pleasure, but my query was supposed to be less efficient :) – Quassnoi Mar 19 '13 at 13:33
  • well m not much experienced in this field , so i can't judge .Well i like your attitude , genrally people feels proud after getting knowledge and you are Down to Earth :) . Hmm thanks :) – Vinay Chopra Mar 20 '13 at 14:01
  • According to [this](http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/) link AVOID to use is null with left join. Better use not in. – kostas ch. Jun 05 '14 at 14:11
  • @kostasch. That's what's written in the answer – Quassnoi Jun 06 '14 at 17:55
  • @Quassnoi i am sorry. you are right. I didn't noticed. :) – kostas ch. Jun 07 '14 at 04:18
  • @Quassnoi Shouldn't "will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct" be "will work but it is less efficient than a NOT **IN** (or NOT EXISTS) construct."? – user1766169 Sep 12 '17 at 06:52
21
SELECT d1.Short_Code 
FROM domain1 d1
LEFT JOIN domain2 d2
ON d1.Short_Code = d2.Short_Code
WHERE d2.Short_Code IS NULL
Nathan DeWitt
  • 6,511
  • 8
  • 46
  • 66
Aducci
  • 26,101
  • 8
  • 63
  • 67
7

I would opt for NOT EXISTS in this case.

SELECT D1.ShortCode
FROM Domain1 D1
WHERE NOT EXISTS
    (SELECT 'X'
     FROM Domain2 D2
     WHERE D2.ShortCode = D1.ShortCode
    )
Rob Paller
  • 7,736
  • 29
  • 26
  • What difference would `NOT EXISTS` make? – Quassnoi Apr 15 '11 at 12:56
  • Instead of an outer join or a complex OR operation (with the NOT IN clause) the optimizer should use the equivalent of an exclusion merge join between the two tables. – Rob Paller Apr 15 '11 at 12:59
  • 3
    the optimizer will build exactly same plan for both `NOT IN` and `NOT EXISTS` (as long as `short_code` is not nullable). There will be no "complex OR operations". – Quassnoi Apr 15 '11 at 13:14
  • Didn't realize that. Behavior in Teradata is slightly different. – Rob Paller Apr 15 '11 at 13:18