Search code examples
sqldatabasejoinunion

What is the difference between JOIN and UNION?

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

Solution

  • 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);
+-----+-----+
| bah | bah |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)