CODEWITHSUNDEEP
sql
HaloKu
HaloKu

Reputation: 435

How Union behaves in SQL query (in terms of priority)

We know that in SQL the order the code is executed is:

  1. FROM
  2. WHERE
  3. GROUP
  4. SELECT

Now, in SQL I can write (Example1):

SELECT colA, colB
FROM tableA
UNION
SELECT colA, colB
FROM tableB
WHERE colA > 1

and I can also write (Example2):

SELECT * 
FROM (
  SELECT colA, colB
  FROM tableA
  UNION
  SELECT colX, colA
  FROM tableB
)
WHERE colA > 1

My question is about Example1: the WHERE condition is on tableB or on the UNION of tableA and tableB? Unlike Example2, where it's very clear, in Example1 it's not (?).

I didn't find any documentation in any DB that explains this about the UNION.

Upvotes: 1

Views: 1095

Answers (3)

Andreas
Andreas

Reputation: 159086

To re-cap the diagrams below, keeping it simple:

A query_block has no set operator (e.g. UNION) and no ORDER BY. The WHERE clause applies at this level:

[WITH ...] SELECT ... FROM ... [WHERE ...] [GROUP BY ... [HAVING ...]]

A subquery is one or more query_blocks separated by set operators, with optional ORDER BY. The ordering applies after the set operators:

query_block [UNION query_block]* [ORDER BY ...]

So to answer your question: In Example1, the WHERE condition is on tableB.

The diagrams in the Oracle 12c Language Reference shows it pretty good:

select::=
select

subquery::=
subquery

query_block::=
query_block

Upvotes: 2

user330315
user330315

Reputation:

The WHERE clause as written in the first statement belongs to the second part of the UNION. You can test this by using different column names in the two tables:

create table t1 (c1 int, c2 int);
create table t2 (c3 int, c4 int);

Then the following query:

select c1, c2 
from t1
union all
select c3, c4
from t2
where c1 > 1

will result in an error claiming that the column c1 does not exist (or can't be used in that context - depending on the DBMS product).

The same rule applies to a GROUP BY or HAVING, but not to a possible ORDER BY - that always sorts the result of the union.

Postgres example

Oracle example

SQL Server example

Upvotes: 1

VBoka
VBoka

Reputation: 9083

Hi @HaloKu Have you tested it ? I believe that a small test would give an answer to your question...

Please check this example.

You can see that for the Example 1, the where clause will influence only the tableB. The example is for Oracle Database. Please can you give us the information what database do you use?

Here is the TEST for MySQL: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=62675070b512ba4441603bdef2b9b771 It acts the same way as Oracle does...

Also, please check this question here: UNION with WHERE clause

Upvotes: 0

Related Questions