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)