Is Union works on any Implicit joins

Question

In UNION their will be no duplicate values so it will search other table entirely before considering a row.

dose it make UNION as a kind of join?

Short answer, no. `JOIN` and `UNION` are different operations. — Felix Pamittan, Jan 11 '16 at 07:08
UNION operator is just for combining two or more SELECT statements. While, JOIN is for select rows from each tables either Inner, Outer, Left or right method. you can refer http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union — bmsqldev, Jan 11 '16 at 07:09
Why the SQL Server and Oracle tags? Don't tag products not involved... — jarlh, Jan 11 '16 at 08:01

score 2 · Answer 1 · answered Jan 11 '16 at 07:10

2

dose it make UNION as a kind of join?

NO

UNION is used to combine the result of two queries. Whereas JOIN is used to get the data using a logical relationship between the tables.

UNION

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

JOIN

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table.

answered Jan 11 '16 at 07:10

Rahul Tripathi

168,305
31
280
331

@NarasimhaMaiya:- You are comparing oranges with apple. Also there is no linking, so which background process you are talking about? – Rahul Tripathi Jan 11 '16 at 12:09
Before i wrote a answer at the top. I thought like union do not contains any duplicate data set from two table. so i thought it will compare 2 data and then it will add it to the result set. For those kind of comparison their is a implicit join called `anti-join`. But i proved wrong. – Narasimha Maiya Jan 11 '16 at 12:16
Go through my answer. – Narasimha Maiya Jan 11 '16 at 12:16
@NarasimhaMaiya:- If you are clear about it then heads up! I know what it does. Thanks – Rahul Tripathi Jan 11 '16 at 12:17
Thank you too for finding time for answering such a silly question. – Narasimha Maiya Jan 11 '16 at 12:19

score 1 · Answer 2 · answered Jan 11 '16 at 07:20

1

To make VERY simple:
- with JOIN you put 2 datasets - generally with different columns, "side by side",
- with UNION you put 2 datasets - with identical columns - one above the other

answered Jan 11 '16 at 07:20

iDevlop

24,841
11
90
149

For that comparison is necessary and as per my Knowledge without join comparing is not possible. So oracle Engine should use join. – Narasimha Maiya Jan 11 '16 at 07:25

score 0 · Accepted Answer · edited Jan 11 '16 at 12:30

0

Sorry I asked question too soon. i.e without trying everything.

I misunderstood the working of UNION so I created a plan for union and found that before UNION UNION ALL will execute and from the result set we are extracting unique set of data.

explain plan for

select * from table_a
union
select * from table_b

plan output came like this

Thank you for reply.

edited Jan 11 '16 at 12:30

Jonnus

2,988
2
24
33

answered Jan 11 '16 at 07:35

Narasimha Maiya

1,009
4
12
35

Is Union works on any Implicit joins

3 Answers3