0

Will the following 2 SQL statements give same result?

SQL1-

A inner join B on (condition1)
union
A inner join B on (condition2)
union
A inner join B on (condition3)

SQL2-

A inner join B on (condition1) OR (condition2) OR (condition3)
wait
  • 11
  • 3
  • 3
    Yes, they will. – Thorsten Kettner Sep 07 '21 at 06:04
  • Does this answer your question? [What is the difference between JOIN and UNION?](https://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union) – Bishan Sep 07 '21 at 06:06
  • @Bishan: How is that supposed to answer this question? Have you read the other question and its answers? Have you understood what wait's pseudo code queries are supposed to do? – Thorsten Kettner Sep 07 '21 at 06:13
  • Try this example. https://dbfiddle.uk/?rdbms=postgres_13&fiddle=56106d853e41be23903904e349c9ffee – Jon Armstrong Sep 07 '21 at 06:26
  • Good question to get people to think (me too). The presence of rows/results with guaranteed unique detail vs cases where the result is not guaranteed to be unique, makes the difference. It's tough to answer a general question with `yes` when there are specific cases (lots of them, although we only need one) where the answer is `no`. – Jon Armstrong Sep 07 '21 at 06:43
  • 1
    @wait: See Serg's answer. I was a little quick saying yes. The real answer is obviously: "yes, if the selected rows are distinct". – Thorsten Kettner Sep 07 '21 at 07:30

1 Answers1

4

At least it depends on A or B originally having doubles. For example

with A(c) as (
 select 1 union all
 select 1 union all
 select 2 
 ),
B(c)  as (
 select 1 union all
 select 2 union all
 select 3 
 )
select *
from A join B on A.c=B.c
union
select *
from A join B on A.c>B.c

returns 3 rows (distinct).

with A(c) as (
 select 1 union all
 select 1 union all
 select 2 
 ),
B(c)  as (
 select 1 union all
 select 2 union all
 select 3 
 )
select *
from A join B on A.c=B.c or A.c>B.c

returns 4 rows due to A having doubles.

Serg
  • 22,285
  • 5
  • 21
  • 48
  • That is correct. In the general case, the results can be different, so the answer really is `no`. It's just that there are lots of specific cases where they are the same, and that's what most people tend to consider. – Jon Armstrong Sep 07 '21 at 06:32
  • A great aswer. This is a case I didn't consider. – Thorsten Kettner Sep 07 '21 at 07:28