Creating Combined Queries

Question

Post reply

Creating Combined Queries

SQL 2016

SSC-Addicted

Points: 427
More actions
March 29, 2016 at 10:27 am

#325260

Hello Help forum,
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <=5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
SELECT vend_id, prod_id, pro_price
FROM products
WHERE prod_price <=5
OR vend_id IN (1001, 1002);
My question, I am trying to create the same logic from the UNION using the 2 WHERE statements would I use the OR or AND operator?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You would use AND.

SELECT vend_id, prod_id, pro_price

FROM products

WHERE prod_price <=5

AND vend_id IN (1001, 1002);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Alan.B (3/29/2016)
You would use AND.
SELECT vend_id, prod_id, pro_price
FROM products
WHERE prod_price <=5
AND vend_id IN (1001, 1002);

Disagree. This requires an OR.

select a from b where condition1

union (should probably be union all)

select a from b where condition2

is equivalent to

select a from b

where (condition1 or condition2)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Phil Parkin (3/29/2016)
Alan.B (3/29/2016)
You would use AND.
SELECT vend_id, prod_id, pro_price
FROM products
WHERE prod_price <=5
AND vend_id IN (1001, 1002);
Disagree. This requires an OR.
select a from b where condition1
union (should probably be union all)
select a from b where condition2
is equivalent to
select a from b
where (condition1 or condition2)

+1.

CREATE TABLE #products (vend_id int, prod_id int, prod_price money);

INSERT INTO #products VALUES

(1001,1,4),

(1002,2,6),

(1003,3,3),

(1004,4,6);

SELECT vend_id,

prod_id,

prod_price

FROM #products

WHERE prod_price<=5

UNION

SELECT vend_id,

prod_id,

prod_price

FROM #products

WHERE vend_id IN (1001,1002);

SELECT vend_id,

prod_id,

prod_price

FROM #products

WHERE prod_price<=5

AND

vend_id IN (1001,1002);

SELECT vend_id,

prod_id,

prod_price

FROM #products

WHERE prod_price<=5

OR

vend_id IN (1001,1002);

DROP TABLE #products;

Cheers!

EDIT: Fixed some alignment annoyances in the code snippet.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Phil Parkin (3/29/2016)
Alan.B (3/29/2016)
You would use AND.
SELECT vend_id, prod_id, pro_price
FROM products
WHERE prod_price <=5
AND vend_id IN (1001, 1002);
Disagree. This requires an OR.
select a from b where condition1
union (should probably be union all)
select a from b where condition2
is equivalent to
select a from b
where (condition1 or condition2)

Yep, NEC moment... I don't know what I was thinking.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76985 More actions · Answer 5

Phil Parkin (3/29/2016)
Alan.B (3/29/2016)
You would use AND.
SELECT vend_id, prod_id, pro_price
FROM products
WHERE prod_price <=5
AND vend_id IN (1001, 1002);
Disagree. This requires an OR.
select a from b where condition1
union (should probably be union all)
select a from b where condition2
is equivalent to
select a from b
where (condition1 or condition2)

Not necessarily.

SELECT a FROM b WHERE condition1

UNION ALL

SELECT a FROM b WHERE condition2

will create duplicates for records that meet both condition.

SELECT a FROM b

WHERE condition1 OR condition2

will retain duplicates, but not create duplicates where a record meets both conditions.

SELECT a

FROM b

WHERE condition1

UNION

SELECT a

FROM b

WHERE condition2

will eliminate all duplicates and is equivalent to

SELECT DISTINCT a

FROM b

WHERE condition1 OR condition2

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

Quick thought, the first (union) query will use two index seek operators (if the correct indices are in place) while the latter does a single scan. If the cardinality of the table is low this probably doesn't matter but if it high then the difference can be huge.

😎

Further, by changing the UNION to UNION ALL and use a CTE to select distinct value the optimizer will remove the distinct sort operator.

;WITH BASE_DATA AS

(

SELECT

vend_id

,prod_id

,prod_price

FROM products

WHERE prod_price <=5

UNION ALL

SELECT

vend_id

,prod_id

,prod_price

FROM products

WHERE vend_id IN (1001, 1002)

)

SELECT DISTINCT

BD.vend_id

,BD.prod_id

,BD.prod_price

FROM BASE_DATA BD;

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 7

I thought it would need the AND operator because you need a price filter and then the vender check. But I see that the UNION is joining 2 separate tables so the OR would be used. Since it is checking the first statement if it is TRUE and then checking the 2nd statement if it is TRUE. Is this the correct logic?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

Eirikur Eiriksson (3/29/2016)
Quick thought, the first (union) query will use two index seek operators (if the correct indices are in place) while the latter does a single scan. If the cardinality of the table is low this probably doesn't matter but if it high then the difference can be huge.
😎
Further, by changing the UNION to UNION ALL and use a CTE to select distinct value the optimizer will remove the distinct sort operator.
;WITH BASE_DATA AS
(
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE prod_price <=5
UNION ALL
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE vend_id IN (1001, 1002)
)
SELECT DISTINCT
BD.vend_id
,BD.prod_id
,BD.prod_price
FROM BASE_DATA BD;

Even more efficient (and in my opinion easier to udnerstand):

SELECT

vend_id

,prod_id

,prod_price

FROM products

WHERE prod_price <=5

UNION ALL

SELECT

vend_id

,prod_id

,prod_price

FROM products

WHERE vend_id IN (1001, 1002)

AND prod_price > 5; -- Exclude rows from first query to prevent duplicates

Note that if prod_price is nullable, you will have to change the last line to either "AND (prod_price > 5 OR prod_price IS NULL)", or to "AND COALESCE(prod_price, 9) > 5"

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

Hugo Kornelis (3/29/2016)
Eirikur Eiriksson (3/29/2016)
Quick thought, the first (union) query will use two index seek operators (if the correct indices are in place) while the latter does a single scan. If the cardinality of the table is low this probably doesn't matter but if it high then the difference can be huge.
😎
Further, by changing the UNION to UNION ALL and use a CTE to select distinct value the optimizer will remove the distinct sort operator.
;WITH BASE_DATA AS
(
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE prod_price <=5
UNION ALL
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE vend_id IN (1001, 1002)
)
SELECT DISTINCT
BD.vend_id
,BD.prod_id
,BD.prod_price
FROM BASE_DATA BD;
Even more efficient (and in my opinion easier to udnerstand):
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE prod_price <=5
UNION ALL
SELECT
vend_id
,prod_id
,prod_price
FROM products
WHERE vend_id IN (1001, 1002)
AND prod_price > 5; -- Exclude rows from first query to prevent duplicates
Note that if prod_price is nullable, you will have to change the last line to either "AND (prod_price > 5 OR prod_price IS NULL)", or to "AND COALESCE(prod_price, 9) > 5"

Very nice Hugo,

two index seeks and concatenation eliminating the costly Hash Match Aggregate, brilliant!

😎