What is the difference between JOIN and UNION?

Question

What is the difference between JOIN and UNION? Can I have an example?

Answer 1

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+
| bah |
+-----+
|  23 | 
|  45 | 
+-----+
2 rows in set (0.00 sec)

similary trivial example of JOIN:

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)

Answer 2

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.

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

The UNION operation is different from using JOINs that combine columns from two tables.

UNION Example:

SELECT 1 AS [Column1], 2 AS [Column2]
UNION
SELECT 3 AS [Column1], 4 AS [Column2]

Output:

Column1    Column2
-------------------
1          2
3          4

JOIN Example:

SELECT a.Column1, b.Column2 FROM TableA a INNER JOIN TableB b ON a.Id = b.AFKId

This will output all the rows from both the tables for which the condition a.Id = b.AFKId is true.

Answer 3

You may see the same schematic explanations for both, but these are totally confusing.

For UNION:

For JOIN:

Answer 4

JOIN:

A join is used for displaying columns with the same or different names from different tables. The output displayed will have all the columns shown individually. That is, the columns will be aligned next to each other.

UNION:

The UNION set operator is used for combining data from two tables which have columns with the same datatype. When a UNION is performed the data from both tables will be collected in a single column having the same datatype.

For example:

See the two tables shown below:

Table t1
Articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2

Table t2
manufacturer_id manufacturer
1 ABC Gmbh
2 DEF Co KG

Now for performing a JOIN type the query is shown below.

SELECT articleno, article, manufacturer
FROM t1 JOIN t2 ON (t1.manufacturer_id =
t2.manufacturer_id);

articelno article manufacturer
1 hammer ABC GmbH
2 screwdriver DEF Co KG

That is a join.

UNION means that you have to tables or resultset with the same amount and type of columns and you add this to tables/resultsets together. Look at this example:

Table year2006
Articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2

Table year2007
Articleno article price manufacturer_id
1 hammer 6 $ 3
2 screwdriver 7 $ 4

SELECT articleno, article, price, manufactruer_id
FROM year2006
UNION
SELECT articleno, article, price, manufacturer_id
FROM year2007

articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2
1 hammer 6 $ 3
2 screwdriver 7 $ 4

Answer 5

They're completely different things.

A join allows you to relate similar data in different tables.

A union returns the results of two different queries as a single recordset.

Answer 6

Union makes two queries look like one. Joins are for examining two or more tables in a single query statement

Answer 7

Joins and unions can be used to combine data from one or more tables. The difference lies in how the data is combined.

In simple terms, joins combine data into new columns. If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table’s column in the same row.

Unions combine data into new rows. If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table in another set. The rows are in the same result.

Answer 8

Remember that union will merge results (SQL Server to be sure)(feature or bug?)

select 1 as id, 3 as value
union
select 1 as id, 3 as value

id,value

1,3

select * from (select 1 as id, 3 as value) t1 inner join (select 1 as id, 3 as value) t2 on t1.id = t2.id

id,value,id,value

1,3,1,3

Answer 9

1. The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

2. The SQL UNION operator combines the result of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

for example: table 1 customers/table 2 orders

inner join:

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS


INNER JOIN ORDERS


ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

union:

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS


LEFT JOIN ORDERS


ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION

SELECT ID, NAME, AMOUNT, DATE 
 FROM CUSTOMERS


RIGHT JOIN ORDERS


ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Answer 10

Ussing UNION

UNION is 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.

UNION Example:
SELECT 121 AS [Column1], 221 AS [Column2]
UNION
SELECT 321 AS [Column1], 422 AS [Column2]

Output:

Column1    Column2
-------------------
121          221
321          422

Ussing JOINs

JOINs, you can retrieve data from two or more tables based on logical relationships between the tables.

JOIN Example:
SELECT a.Column1, b.Column2 FROM TblA a INNER JOIN TblB b ON a.Id = b.id

Answer 11

Union Operation is combined result of the Vertical Aggregate of the rows, Union Operation is combined result of the Horizontal Aggregate of the Columns.

Answer 12

In the abstract, they are similar, in that two tables or result sets are being combined , but UNION is really for combining result sets with the SAME NUMBER OF COLUMNS with the COLUMNS HAVING SIMILAR DATA TYPES. The STRUCTURE is the same, only new rows are being added.

In joins, you can combine tables/result sets with any possible structure, including a cartesian join where there are NO shared/similar columns.

Answer 13

The UNION operator is just for combining two or more SELECT statements.

While JOIN is for selecting rows from each table, either by the inner, outer, left or right method.

Refer to here and here . There is a better explanation with examples.

Answer 14

I like to think of the general difference as being:

• JOINS join tables
• UNION (et all) combines queries.