Combining Intersect an Union in the same query

1

Hello everyone,

I have some code which I have inherited. It seems to work and looks like it is producing the correct results. For my own understanding I would just like to know how the query is working and how SQL is putting the data together. Here is the code:

(
select distinct c.ref as Client_Id,
c.name,
c.Address,
c.Postcode,
c.Status,
c.type

from customertype ct
inner join customer c on ct.id=c.id
where ct.id IN ('97','98','99')
and c.update_date >='01/01/2020'


intersect

select distinct c.ref as Client_Id,
c.name,
c.Address,
c.Postcode,
c.Status,
c.type

from product p 
inner join customer c
on p.ref= c.ref
inner join price pr
on pr.product_code = p.product_code

where p.product_code not in ('AA','BB','BC','BD')

union

(select distinct c.ref as Client_Id,
c.name,
c.Address,
c.Postcode,
c.Status,
c.type

from customertype ct
inner join customer c on ct.id=c.id
where ct.id IN ('97','98', '99')
and c.update_date >='01/01/2020'

intersect

(select distinct c.ref as Client_Id,
c.name,
c.Address,
c.Postcode,
c.Status,
c.type

from product p 
inner join client c
on p.ref= c.ref
inner join price pr
on pr.product_code = p.product_code

where (p.product_code not in ('AA','BB','BC','BD') and p.PortfolioID in 
(select v.PortfolioID from vw_ProductLookup v 
where v.methodid in ('1','2','3'))))
			
order by c.ref

I haven't used INTERSECT and UNION much so I would be grateful if someone could explain in plain English how it works in this query, and also how this links in with the sub query at the end.

Many thanks for your help.

Vinnie

2

Assuming that you know what INTERSECT, EXCEPT, and UNION, and UNION ALL do, I suspect the problem you're having is what the order or precedence is. That's actually covered in the following link. Search for the word "Precedence" on the following...

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

To summarize the rules of precedence, here's a quote from the link above:

  1. Expressions in parentheses
  2. The INTERSECT operator
  3. EXCEPT and UNION evaluated from left to right based on their position in the expression

The other "SET" operators have entries in the document layout on the left of that link.

If you don't know what these operators do, the simple explanations are...

All of the operators must work on the same number of columns in the same order (the names can be different though)

Comparisons are made for all columns and that includes NULL comparisons. It's one of the few places where NULL = NULL without making a serverwide setting change and it's not affected by such changes.

INTERSECT is a bit like an INNER JOIN and will return only those items that are an exact match across all columns.

EXCEPT returns rows in the first (left) query that are NOT in the second (right) query.

UNION ALL returns all rows in both queries even if they're duplicated.

UNION returns all rows in both queries without duplicates.

So, getting to your question, let's simplify your query for purposes of explanation. SELECT returns a SET and so we'll assign letters to each Query thusly...

(SET A
INTERSECT
SET B
UNION
(SET C
INTERSECT
(SET D)

I've included the parentheses that I've found that are parts of signs of set grouping rather than parts of the queries. Either you've copied and pasted the query incorrectly, something got lost in the copy process, you were experimenting with adding parentheses in order to try to explain it to yourself, or something else went wrong because the number of left and right grouping parentheses do not match.

Without the parentheses, the INTERSECTS would be done first...

If we were to remove those parentheses for purposes of explanation, SET A and SET B would be INTERSECTED first to produce only unique rows that were exact matches between the two sets. This would form SET AB.

The same would happen with SET C and SET D forming SET CD.

Then, finally, the UNION between between SET AB and SET CD would produce all of the rows from both sets eliminating any duplicates in the process.

3

Jeff, sounds like you reversed the meanings of UNION and UNION ALL. Please check.

Great explanation otherwise.

4

I did indeed. Thank you for the catch. I've repaired the post.

5

Hi Jeff. Thank you for taking the time to decipher what I was struggling with. Your explanation makes sense. Thanks for your help.
Vinnie

6

No problem. Glad to help and thank you for the feedback.