What is the difference between JOIN and UNION?

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


Solution 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)

Solution 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.