What is the difference between union and join?

What 开发者_高级运维is the difference between the SQL keywords union and join?

The UNION operator is used to combine the result-set of two or more SELECT statements.

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

The tutorials on these two topics (linked to above) on w3schools.com go into further detail.

Think of joins as horizontal and unions as vertical

Union is a combination of elements from multiple sets.

Join is a subset of the cross product of multiple sets

If need combinate more then one queries use operator Union, this operator execute first query and the second query, and then return all result in one dataset see more

select field from t1
union 
select field from t2

If need create query to select data from two or more table then use operator join see more

select t1.field, t2.field
from t1.number inner join t2.key on t1.number=t2.key

Union:

1. In UNION you need to take care That col length, datatype and no.of columns used in SELECT statement should be same in both tables
2. The result is displayed with first table column names as a combination of data from two tables by eliminating duplicates
3. To use UNION you should have at least two SELECT statements

Join:

1. In JOINs there is no need to have rules mentioned in point 1.
2. The result is displayed with all columns mentioned in query depending on the type of JOIN used(Outer, Inner,Cartesian..)
3. One SELECT statement is enough

Hope this helps u..