ORACLE的SQL JOIN方式小结
程序小败类
8年前
<p>在ORACLE数据库中，表与表之间的SQL JOIN方式有多种（不仅表与表，还可以表与视图、物化视图等联结），官方的解释如下所示</p> <p>A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.</p> <p>SQL JOIN 归纳起来有下面几种方式，下面一起来梳理一下这些概念。SQL JOIN其实是一个逻辑概念，像NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式。</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/1abfa7125b2c75d0f1af6b68b904aed9.png"></p> <p>我们先准备一个两个测试表M与N（仅仅是为了演示需要），如下脚本所示</p> <pre> <code class="language-sql">SQL> CREATE TABLE M 2 ( 3 NAME VARCHAR2(12) 4 ,SEX VARCHAR2(6) 5 ); Table created. SQL> CREATE TABLE N 2 ( 3 NAME VARCHAR2(12) 4 ,GRADE NUMBER(2) 5 ); Table created. SQL> INSERT INTO M 2 SELECT 'kerry', 'male' FROM DUAL UNION ALL 3 SELECT 'jimmy', 'male' FROM DUAL UNION ALL 4 SELECT 'tina' , 'female' FROM DUAL UNION ALL 5 SELECT 'wendy', 'female' FROM DUAL; 4 rows created. SQL> COMMIT; Commit complete. SQL> INSERT INTO N 2 SELECT 'kerry', 3 FROM DUAL UNION ALL 3 SELECT 'jimmy', 2 FROM DUAL UNION ALL 4 SELECT 'ken' , 6 FROM DUAL UNION ALL 5 SELECT 'richard',5 FROM DUAL; 4 rows created. SQL> COMMIT; Commit complete.</code></pre> <p><strong>内连接：</strong> <strong>INNER JOIN</strong></p> <p>INNER JOIN 它表示返回两个表或记录集连接字段的匹配记录。如下所示，INNER JOIN 可以有三种实现方式：</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, M.SEX, N.GRADE 2 FROM M INNER JOIN N ON M.NAME=N.NAME; NAME SEX GRADE ------------ ------ ---------- kerry male 3 jimmy male 2 SQL> SELECT M.NAME, M.SEX, N.GRADE 2 FROM M, N 3 WHERE M.NAME=N.NAME; NAME SEX GRADE ------------ ------ ---------- kerry male 3 jimmy male 2</code></pre> <p>第三种方式，使用USING，如下所示，这种写法一般较少人使用。</p> <pre> <code class="language-sql">SQL> SELECT NAME, M.SEX,N.GRADE 2 FROM M INNER JOIN N USING(NAME); NAME SEX GRADE ------------ ------ ---------- kerry male 3 jimmy male 2 SQL></code></pre> <p><img src="https://simg.open-open.com/show/c8770c485c97309adc1bbd4b449992d3.png"></p> <p>注意， INNER JOIN可以用使用简写JOIN方式，如下所示，但是建议使用INNER JOIN 而不是JOIN这样的语法。</p> <p><img src="https://simg.open-open.com/show/b8e2aba680adde6859938f0d1aa80ad9.png"></p> <p>如果我们用韦恩图来解释INNER JOIN，则非常一目了然、形象生动。可以用下面图来表示（此图以及下面的韦恩图均来自链接http://pafumi.net/SQL_Joins.html ，本来想自己画，无奈有些图使用word不好实现，R语言不会。故在此借其图用用）</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/d3c5c787d97fd265d0c4902b7f88abbf.png"></p> <p><strong>外连接：</strong> <strong>OUTER JOIN</strong></p> <p><strong>1</strong> <strong>全连接：</strong> <strong>full join</strong></p> <p>全连接 ：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。不符合条件的，以空值代替。如下所示：</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M FULL OUTER JOIN N ON M.NAME=N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 ken 6 richard 5 tina female wendy female 6 rows selected.</code></pre> <p><img src="https://simg.open-open.com/show/e1f5e0628cc3cc5e96823434b5de752e.png"></p> <p>FULL OUTER JOIN的韦恩图如下所示：</p> <p><img src="https://simg.open-open.com/show/7e065a6cf6be4f3e4692b96e06ba5520.png"></p> <p><strong>2</strong> <strong>左外连接：</strong> <strong>LEFT JOIN</strong></p> <p>左外连接又叫左连接 ：意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐。换句话说就是，列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M LEFT OUTER JOIN N ON M.NAME=N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 tina female wendy female SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M LEFT JOIN N ON M.NAME=N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 tina female wendy female</code></pre> <p>在ORACLE 9i以及之前，使用在(+)来表示左连接，哪个带(+)哪个需要条件符合的，另一个全部的。即放左表示右连接，放右表示左连接。这种写法，如果不熟悉，就会有点陌生。其实也不是什么新鲜事物，只是你不太熟悉而已。</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M, N 3 WHERE M.NAME=N.NAME(+); NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 tina female wendy female SQL></code></pre> <p><img src="https://simg.open-open.com/show/26d9ef5e18fe661f654cf63da1d5ad80.png"></p> <p>LEFT OUTER JOIN的韦恩图如下所示：</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/b9823133e5321290028e44a3bd46ceb7.png"></p> <p><strong>3</strong> <strong>右外连接：</strong> <strong> RIGHT JOIN </strong></p> <p>右外连接又叫右连接： 意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐，换句话说，与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件的用空值替代。如下所示</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M RIGHT OUTER JOIN N ON M.NAME=N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 ken 6 richard 5 SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M RIGHT JOIN N ON M.NAME=N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 ken 6 richard 5 SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE 2 FROM M, N 3 WHERE M.NAME(+) = N.NAME; NAME NAME SEX GRADE ------------ ------------ ------ ---------- kerry kerry male 3 jimmy jimmy male 2 ken 6 richard 5</code></pre> <p><img src="https://simg.open-open.com/show/4fc8176b4469b7aff369d7dda226cab6.png"></p> <p><strong>笛卡尔积：</strong> <strong>CROSS JOIN</strong></p> <p>CROSS JOIN就是笛卡尔乘积连接���不需要任何关联条件，实现M*N的结果集，其实这种SQL JOIN方式基本上只在理论上有意义，实际当中，很少有用的CORSS JOIN方式。</p> <p>注意： cross join跟inner join、outer join等有所不同，不需要关键词on，因为它不需要相关字段做关联。</p> <pre> <code class="language-sql">SQL> SELECT M.NAME, M.SEX, N.NAME,N.GRADE 2 FROM M CROSS JOIN N; NAME SEX NAME GRADE ------------ ------ ------------ ---------- kerry male kerry 3 kerry male jimmy 2 kerry male ken 6 kerry male richard 5 jimmy male kerry 3 jimmy male jimmy 2 jimmy male ken 6 jimmy male richard 5 tina female kerry 3 tina female jimmy 2 tina female ken 6 NAME SEX NAME GRADE ------------ ------ ------------ ---------- tina female richard 5 wendy female kerry 3 wendy female jimmy 2 wendy female ken 6 wendy female richard 5 16 rows selected.</code></pre> <p><img src="https://simg.open-open.com/show/4d4227a3c17aa5ede58864307fb8d8ba.png"></p> <p>注意：笛卡尔积用维恩图是无法体现出来的。</p> <p><strong>自然连接：</strong> <strong>NATURAL JOIN</strong></p> <p>NATURAL JOIN: 在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。如下所示</p> <pre> <code class="language-sql">SQL> SELECT * FROM M NATURAL JOIN N; NAME SEX GRADE ------------ ------ ---------- kerry male 3 jimmy male 2</code></pre> <p>官方解释：</p> <p>The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias</p> <p>有种说法是，对两张表中字段名和数据类型都相同的字段进行等值连接，并返回符合条件的结果 ，其实只要字段名相同，数据类型不同，也可以做NATURAL JOIN，如下所示：</p> <pre> <code class="language-sql">SQL> CREATE TABLE TEST1 2 ( 3 ID NUMBER(10), 4 NAME VARCHAR2(12) 5 ); Table created. SQL> CREATE TABLE TEST2 2 ( 3 ID VARCHAR2(10), 4 NT VARCHAR2(12) 5 ); Table created. SQL> INSERT INTO TEST1 2 VALUES(1000, 'KERRY'); 1 row created. SQL> COMMIT; Commit complete. SQL> INSERT INTO TEST2 2 VALUES('1000', 'KKK'); 1 row created. SQL> SELECT * FROM TEST1 NATURAL JOIN TEST2; ID NAME NT ---------- ------------ ------------ 1000 KERRY KKK SQL></code></pre> <p><img src="https://simg.open-open.com/show/bb1e3b6e3adada3bf713425c9a091de8.png"></p> <p>自然连接的两个表的有多个字段都满足有相同名称，那么他们会被作为自然连接的条件，如下案例所示</p> <pre> <code class="language-sql">SQL> DROP TABLE TEST1; Table dropped. SQL> DROP TABLE TEST2; Table dropped. SQL> CREATE TABLE TEST1 2 ( 3 ID NUMBER(10), 4 NAME VARCHAR2(12) 5 ) 6 ; Table created. SQL> CREATE TABLE TEST2 2 ( 3 ID NUMBER(10), 4 NAME VARCHAR2(12) 5 ); Table created. SQL> INSERT INTO TEST1 2 SELECT 1000, 'KERRY' FROM DUAL UNION ALL 3 SELECT 1001, 'KEN' FROM DUAL; 2 rows created. SQL> COMMIT; Commit complete. SQL> INSERT INTO TEST2 2 SELECT 1000, 'KKK' FROM DUAL UNION ALL 3 SELECT 1001, 'KEN' FROM DUAL; 2 rows created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TEST1 NATURAL JOIN TEST2; ID NAME ---------- ------------ 1001 KEN</code></pre> <p><img src="https://simg.open-open.com/show/b8e6b082e83f622c2305886dd2c3c14c.png"></p> <p>NATURAL JOIN的韦恩图,其实和内连接是一样的。如下所示：</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/d3c5c787d97fd265d0c4902b7f88abbf.png"></p> <p><strong>SEMI JOIN </strong></p> <p> SEMI JOIN 多在子查询exists中使用，对外部row source的每个键值，查找到内部row source匹配的第一个键值后就返回，如果找到就不用再查找内部row source其他的键值了。官方介绍案例如下</p> <p><strong>Using Semijoins: Example </strong></p> <p>In the following example, only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery. If no index has been defined on thesalary column in employees, then a semijoin can be used to improve query performance.</p> <p>SELECT * FROM departments</p> <p> WHERE EXISTS</p> <p> (SELECT * FROM employees</p> <p> WHERE departments.department_id = employees.department_id</p> <p> AND employees.salary > 2500)</p> <p> ORDER BY department_name;</p> <p><strong>ANTI JOIN </strong></p> <p>ANTI JOIN多用于!=或not in 等查询；如果找到满足条件（!= not in）的不返回，不满足条件（!= not in ） 的返回。和 join 相反。</p> <p><strong>Using Antijoins: Example </strong></p> <p>The following example selects a list of employees who are not in a particular set of departments:</p> <p>SELECT * FROM employees</p> <p> WHERE department_id NOT IN</p> <p> (SELECT department_id FROM departments</p> <p> WHERE location_id = 1700)</p> <p> ORDER BY last_name;</p> <p><strong>SELF JOIN </strong></p> <p>SELF JOIN其实就是某个表和其自身连接，连接方式可以是内连接，外连接，交叉连接</p> <p><strong>Using Self Joins: Example </strong></p> <p>The following query uses a self join to return the name of each employee along with the name of the employee’s manager. A WHERE clause is added to shorten the output.</p> <p>SELECT e1.last_name||’ works for ‘||e2.last_name</p> <p> “Employees and Their Managers”</p> <p> FROM employees e1, employees e2</p> <p> WHERE e1.manager_id = e2.employee_id</p> <p> AND e1.last_name LIKE ‘R%’;</p> <p>Employees and Their Managers </p> <p>——————————-</p> <p>Rajs works for Mourgos</p> <p>Raphaely works for King</p> <p>Rogers works for Kaufling</p> <p>Russell works for King</p> <p>参考资料：</p> <p><u><a href="/misc/goto?guid=4959719541481562448" rel="nofollow,noindex">https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm</a> </u></p> <p><a href="/misc/goto?guid=4959719541568942196" rel="nofollow,noindex">http://pafumi.net/SQL_Joins.html</a></p> <p><u><a href="/misc/goto?guid=4959719541655592292" rel="nofollow,noindex">http://www.itpub.net/thread-420946-1-1.html</a> </u></p> <p> </p> <p>来自：http://www.58maisui.com/2016/10/08/670/</p> <p> </p>