sql - JOIN 和 UNION 有什么区别？

Question

JOIN 和 UNION 有什么区别？我可以举个例子吗？

Answer 1

UNION 将来自查询的行放在彼此之后，而 JOIN 生成笛卡尔积并将其子集 - 完全不同的操作。 UNION 的简单示例:

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

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)