What is the difference between JOIN and UNION? Can I have an example?
287
votes
14 Answers
336
votes
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)
65
votes
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.
58
votes
43
votes
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
24
votes
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.
12
votes
5
votes
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.
2
votes
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
1
votes
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;
0
votes
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
0
votes
-1
votes
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.
-1
votes
