sql - 有人能解释一下 union All 和 join in oracle SQL 有什么区别吗

Question

自从过去 2 天以来，我一直在搜索这个问题，UNION 和 JOIN 之间有什么区别。特别是 UNION ALL 和 Natural JOIN。所以我在这里有两个疑问第一个是

CREATE OR REPLACE VIEW VU_m1 AS 
SELECT department_id,manager_id FROM employees 
NATURAL JOIN departments ;
SELECT * FROM vu_m1;

第二个是

CREATE OR REPLACE VIEW VU_m2 AS 
SELECT department_id,manager_id FROM employees 
UNION ALL
SELECT department_id,manager_id FROM departments; 
SELECT * FROM vu_m2;

两个查询的结果完全相同。那么现在 UNION ALL 和 Natural JOIN 有什么区别

详细信息:我正在使用 Oracle 11gR2 处理示例 HR 数据库。

Answer 1

这是我的 2 美分

+----------------------------------+--------------------------------------------+
+- UNION                           +- JOIN                                      +
+----------------------------------+--------------------------------------------+
+SELECT 1 AS bah FROM DUAL         + SELECT *                                   +
+      EMP.DEPTNO                  + FROM                                       +
+SELECT 2 AS bah FROM DUAL;        + (SELECT 1 AS bah from dual)  foo           +
+                                  + JOIN                                       +
+                                  + (SELECT 2 AS bah from dual)  bar           +
+                                  + ON 1=1;                                    +
+----------------------------------+--------------------------------------------+
+-                              RESULTS                                         +
+----------------------------------+--------------------------------------------+
+This gives everything in vertical + This gives everything in horizontal        +
+    BAH                           + BAH  BAH                                   +
+    1                             + 1    2                                     +
+    2                             +                                            +
+----------------------------------+--------------------------------------------+
+-                             OBSERVATIONS                                     +
+----------------------------------+--------------------------------------------+
+ UNION puts lines from queries    + JOIN makes a Cartesian product             +
+ after each other                 + and subsets it                             +
+                                  +                                            +
+ UNION combines the results of    + JOIN can retrieve data from two or         +
+ two or more queries into a single+ more tables based on logical relationships +
+ result set that includes all the + between the tables. Joins indicate how SQL +
+ rows that belong to all queries  + should use data from one table to select   +
+ in the union.                    + the rows in another table.                 +
+                                  +                                            +
+ UNION makes two queries look     + JOIN examine two or more tables in         +
+ like one.                        + a single query statement                   +
+                                  +                                            +
+ UNION returns the results of two + JOIN relates similar data in different     +
+ different queries as a single    + tables.                                    +
+ recordset.                       +                                            +
+                                  +                                            +
+----------------------------------+--------------------------------------------+