# Can we exchange logical disjunction for UNION ALL?

I saw in that somebody exchange below code:

``````SELECT
PK1
, PK2
, PK3
, PK4
, C
, B
, SUM(NUMERIC_1)
, SUM(NUMERIC_2)
, MAX(NUMERIC_3)
, SUM(NUMERIC_4)
FROM MATDOC_EXTRACT
WHERE A = ''
AND (
(
(
B = ''
OR B = 'K'
)
AND (
C = '01'
OR C = '02'
OR C = '07'
OR C = '08'
)
)
OR (
B = ''
AND (
C = '03'
OR C = '04'
)
)
)
GROUP BY PK1
, PK2
, PK3
, PK4
, C
, B;

``````

for this one

``````SELECT
PK1
, PK2
, PK3
, PK4
, C
, B
, SUM(NUMERIC_1)
, SUM(NUMERIC_2)
, MAX(NUMERIC_3)
, SUM(NUMERIC_4)
FROM sap.MATDOC_EXTRACT
WHERE A = N''
AND B = N''
AND C IN (N'01', N'02', N'07', N'08', N'03', N'04')
GROUP BY PK1
, PK2
, PK3
, PK4
, C
, B
UNION ALL
SELECT
PK1
, PK2
, PK3
, PK4
, C
, B
, SUM(NUMERIC_1)
, SUM(NUMERIC_2)
, MAX(NUMERIC_3)
, SUM(NUMERIC_4)
FROM sap.MATDOC_EXTRACT
WHERE A = N''
AND B = N'K'
AND C IN (N'01', N'02', N'07', N'08')
GROUP BY PK1
, PK2
, PK3
, PK4
, C
, B;
``````

are they equal ?

• Yes, they are, but only because the conditions are non-overlapping and because they are included in the `GROUP BY` clause. Commented Aug 28, 2023 at 11:39

One potentially important difference is that in the second case the literals are `nvarchar` and in the first case they are `varchar`.

Ignoring that aspect however...

Yes

``````SELECT *
FROM   TABLE
WHERE  predicate_a
OR predicate_b
``````

Can be re-written as

``````SELECT *
FROM   TABLE
WHERE  predicate_a
UNION ALL
SELECT *
FROM   TABLE
WHERE  predicate_b
``````

As long as `predicate_a` and `predicate_b` are mutually exclusive.

Otherwise rows that match both `predicate_a` and `predicate_b` will incorrectly be returned twice.

In this case as the two branches of the `UNION ALL` have `predicate_a` includes `B = N''` and `predicate_b` includes `B = N'K'` then this condition is met.

In your case you have 10 different equality predicates on `A, B, C` - so if you have an index with those columns in some order then potentially it could be resolved by ten seek predicates.

A B C
'' '' 01
'' '' 02
'' '' 07
'' '' 08
'' K 01
'' K 02
'' K 07
'' K 08
'' '' 03
'' '' 04

Rewriting `OR` as `UNION ALL` can sometimes be a useful technique when the query written with `OR` doesn't give the desired index seeks or has other undesirable aspects.

If I try

``````CREATE TABLE T1(A VARCHAR(1), B VARCHAR(1), C VARCHAR(2), INDEX IX (A, B, C))

UPDATE STATISTICS T1 WITH ROWCOUNT = 50000000, PAGECOUNT = 500000
UPDATE STATISTICS T1 IX WITH ROWCOUNT = 50000000, PAGECOUNT = 500000

SELECT *
FROM T1
WHERE A = ''
AND (
(
(
B = ''
OR B = 'K'
)
AND (
C = '01'
OR C = '02'
OR C = '07'
OR C = '08'
)
)
OR (
B = ''
AND (
C = '03'
OR C = '04'
)
)
)
``````

Then the execution plan looks like

The execution plan does essentially break it into two separate index seek operators and `UNION ALL` them both together

``````  |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index Seek(OBJECT:([tempdb].[dbo].[T1].[IX]), SEEK:(
[tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='01'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='02'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='07'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='08'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='K' AND [tempdb].[dbo].[T1].[C]='01'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='K' AND [tempdb].[dbo].[T1].[C]='02'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='K' AND [tempdb].[dbo].[T1].[C]='07'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='K' AND [tempdb].[dbo].[T1].[C]='08') ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[T1].[IX]), SEEK:(
[tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='03'
OR [tempdb].[dbo].[T1].[A]='' AND [tempdb].[dbo].[T1].[B]='' AND [tempdb].[dbo].[T1].[C]='04') ORDERED FORWARD)
``````

Between the two seek operators the full 10 index seek ranges are expanded out.

For some reason it doesn't seem to see that they are non overlapping however and has a step to remove duplicates.

The `UNION ALL` rewrite does avoid this in this case

``````SELECT *
FROM T1
WHERE A = ''
AND B = ''
AND C IN ('01', '02', '07', '08', '03', '04')
UNION ALL
SELECT *
FROM T1
WHERE A = ''
AND B = 'K'
AND C IN ('01', '02', '07', '08')
``````