CODEWITHSUNDEEP
sqldatabase
demouser123
demouser123

Reputation: 4264

Error in using EXCEPT with INTERSECT in SQL

Suppose I have three tables Table A,Table B and Table C.

Table A contains the col t1 with entries 1,2,2,3,4,4.
Table B has col t2 with entries 1,3,4,4.
Table C has col t3 with entries 1,2,4,4.

The query given was

SELECT * FROM  A EXCEPT (SELECT * FROM  B INTERSECT SELECT * FROM C ).

I saw this question in a test paper. It was mentioned that the expected answer was 2 but the answer obtained from this query was 1,2,4. I am not able to understand the principle behind this.

Upvotes: 0

Views: 98

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

Because of the bracket, the INTERSECT between B and C is done first, resulting in (1,4). You can even verify this just be taking the latter part and running in isolation:

SELECT * FROM  B INTERSECT SELECT * FROM C

The next step is to select everything in A EXCEPT those that exist in the previous result of (1,4), which leaves (2,3).

The answer should be 2 and 3, not 1,2 and 4.

BTW, it should be mentioned that even if you had no parenthesis in the query at all, the result should still be the same because the INTERSECT operator has a higher precedence than the EXCEPT/UNION operators. This is the SQL Server documentation but it's consistent with the standard that applies to any DBMS that implements these operators.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Well, as I see it, both the expected answer and the answer you obtained are wrong. It may be the RDBMS that you are using, but analyzing your query the results should be 2,3. First you should do the INTERSECT between tables B and C, the values that intersect are 1 and 4. Taking that result, you should take all the values from table A except 1 and 4, that leaves us with 2 and 3 (since EXCEPT and INTERSECT return only distinct values). Here is a sqlfiddle with this for you to try.

Upvotes: 2

Related Questions