1

What approach should I follow to construct my SQL query if I need to select a data exepct some other data?

For example, my

I want so select all the data from the data-base EXCEPT this result-set:

SELECT *
FROM table1
WHERE table1.MarketTYpe = 'EmergingMarkets'
AND IsBigOne = 1
AND MarketVolume = 'MIDDLE'
AND SomeClass = 'ThirdClass'

Should I use

  • NOT IN (the aboe result set)
  • Or shoudl I get INVERSE of the conditions like != inseat of = etc.
  • Or ?

Can you advice?

pencilCake
  • 51,323
  • 85
  • 226
  • 363

5 Answers5

6

Use the EXCEPT construct?

SELECT *
FROM table1
EXCEPT
SELECT *
FROM table1
WHERE table1.MarketTYpe = 'EmergingMarkets'
AND IsBigOne = 1
AND MarketVolume = 'MIDDLE'
AND SomeClass = 'ThirdClass'

Note that EXCEPT and NOT EXISTS give the same query plan using "left anti semi joins".

NOT IN (subquery with above) may not give correct results if there are NULL values in the sub-query, hence I wouldn't use

I would avoid negation in the WHERE clause because it isn't readable straight away
As the comments show on Michael's answer...

For more on "all rows except some rows", see these:

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Do I need to concern about the performance? compared to usage of <> instead of =, etc? – pencilCake Nov 14 '11 at 20:04
  • @pencilcake: EXCEPT and NOT EXISTS will be semantically correct and quicker (it is a semi join to find the row to omit). Using negation in the WHERE clause in non-Sargable too – gbn Nov 14 '11 at 20:08
  • +1 - the SARGable aspect is possibly the most persuasive argument of which to use – Andrew Nov 14 '11 at 20:14
2

Simplest and probably fastest here is to simply invert the conditions:

SELECT *
FROM table1
WHERE table1.MarketTYpe <> 'EmergingMarkets'
  OR IsBigOne <> 1
  OR MarketVolume <> 'MIDDLE'
  OR SomeClass <> 'ThirdClass'

This is likely to use lots fewer resources than doing a NOT IN(). You may wish to benchmark them to be certain, but the above is likely to be faster.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • @drdwilcox Why? Doing `MarketType <> 'EmergingMarkets' OR IsBigOne <> 1` would return `MarketType = 'EmergingMarkets'` when `IsBigOne = 0`. that's not how I understand the OP's desired result – Michael Berkowski Nov 14 '11 at 19:58
  • @drdwilcox switching to `OR` would drastically change the logic here. I don't think that's what the OP intends at all. – Michael Berkowski Nov 14 '11 at 20:01
  • drdwilcox is right, you are just wanting a row where ANY of the 4 columns do not match (assuming original posters logic was stated correctly and we consider that AND clause in the OP to really mean a SQL AND) deMorgans rule on putting a Not thru the logic (A & B & C & D) => ! (A & B & C & D) => !A or !B or !C or !D – Andrew Nov 14 '11 at 20:03
  • @Michael - get there in the end :) can +1 it now :) – Andrew Nov 14 '11 at 20:12
  • 1
    @gbn I'm kind of inclined to agree. – Michael Berkowski Nov 14 '11 at 20:15
2

What database engine?

Minus operator in ORACLE Except operator in SQL Server

xQbert
  • 34,733
  • 2
  • 41
  • 62
1

Use NOT IN because that makes it clear that you want the set in the main select statement excluding the subset in the NOT IN select statement.

Jamie Ide
  • 48,427
  • 16
  • 81
  • 117
  • 1
    What makes you think this will be slower? The query optimizer may generate the same execution plan. You have to test all approaches to determine which one is faster. – Jamie Ide Nov 14 '11 at 20:01
  • NOT IN isn't always correct is there are NULL values in the subquery. Generally, NOT IN is to be avoided because of this: use NOT EXISTS or EXCEPT to be both *correct* and consistent – gbn Nov 14 '11 at 20:12
  • I didn't know about EXCEPT so that was great to learn. However, I think it's more accurate to say that NOT IN may produce _unexpected_, rather than incorrect, results if the set contains NULL values. See also http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values – Jamie Ide Nov 14 '11 at 20:20
1

I like gbn's answer, but another way of doing it can be:

SELECT *
FROM table1
WHERE NOT (table1.MarketTYpe = 'EmergingMarkets'
AND IsBigOne = 1
AND MarketVolume = 'MIDDLE'
AND SomeClass = 'ThirdClass')
David Parvin
  • 927
  • 7
  • 10