Can we exchange logical disjunction for UNION ALL?

Question

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 ?

Answer 1

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')