CODEWITHSUNDEEP
sql-server
GitZine
GitZine

Reputation: 475

Importance of Intersect Except order

I am using Sql Server and my question is the following:

Do this two codes give the same result:

  1. A except B intersect C except D intersect E

  2. A intersect E intersect C except B except D

?

Upvotes: 0

Views: 321

Answers (2)

GitZine
GitZine

Reputation: 475

I found the answer to the question: It mainly relate to the precedence of intersect over except. so A except B intersect C is not like A intersect B except C.

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

If you understand the definitions of each operation, then I think you would understand what gets returned from both examples.

EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. INTERSECT returns distinct rows that are output by both the left and right input queries operator.

So in order of operations:

1. A except B intersect C except D intersect E
  • The A Except B will return rows from A that aren't output by B;
  • A only rows will Intersect with C
  • AC rows will Except with D
  • AC only rows will Intersect with E

Resulting in ACE rows

2. A intersect E intersect C except B except D
  • A Intersect E returns both AE rows
  • AE Intersect C returns AEC rows
  • AEC Except B returns AEC without B
  • AEC Only rows Except with D returns AEC without D

So concluding 1. ACE rows and 2. AEC rows, it would appear that the both would return the same results.

Upvotes: 1

Related Questions