Oracle SQL multi-Table query

Oracle SQL multi-Table query was a time when I was confused about oracle multi-table queries. The reason is that oracle's own syntax is mixed with the SQL international standard syntax. This article is only applicable to oracle cainiao... Multi-table join type (SQL 1999 standard) • Cross joins • Natural joins • USING clause • Full (or two-sided) outer joins • Arbitrary join conditions for outer joinsSQL1999 Syntax: SELECT table1.column, table2.columnFROM table1 [cross join table2] | [natural join table2] | [JOIN table2 USING (column_name)] | [JOIN table2ON (table1.column _ name = table2.column _ name)] | [LEFT | RIGHT | full outer join table2ON (table1.column _ name = table2.column _ Name)] | [cross join table2]; Syntax explanation: table1.column -- specify the table from which data is retrieved and the column cross join -- returns the Cartesian set natural join of the two tables -- connects two tables to JOIN tableUSING column_name based on the same column name -- performs equivalent JOIN table based on the column name ONtable1.column _ name -- execute equivalent join = table2.column _ name LEFT/RIGHT/full outer according to the conditions in the ON clause, in terms of data display mode, data is divided into internal connections and external connections: only data that meets the connection conditions is returned. Outer Join: In addition to returning rows that meet the connection bar, it also returns rows that do not meet the conditions in the left (right) table. This is called the demonstration of left (right) join, which is mainly based on SQL standards, oracle statement for comparison. The example user locks scott and HR: alter user scott identified by tiger account unlock; alter user hr identified by hr account unlock; intranet connection-Oracle statement select empno, ename, sal, dname, loc from emp, deptwhere emp. deptno = dept. deptno; -- SQL 99 standard syntax select empno, ename, job, sal, dept. deptno, dname, locfrom emp join dept on emp. deptno = dept. deptno; or change join to inner join Outer join left Outer join Oracle Outer join Syntax: SELECT table1.column, table2.column -- right Outer join FROM Table1, table2WHERE table1.column (+) = table2.column; SELECT table1.column, table2.column -- left Outer Join FROM table1, table2WHERE table1.column = table2.column (+ ); for standard external connection Syntax of SQL 1999, see SQL1999 syntax above. The External Connection Symbol of Oracle is (+), and (+) should be placed after the field name. The table opposite to (+) is displayed in all. When the left Outer Join is performed, the plus sign is on the right of the equal sign SQL> select d. dname, e. ename, e. deptnofrom dept d, emp ewhere d. deptno = e. deptno (+) order by d. deptno; dname ename deptno ------------ ---------- accounting clark 10 accounting king 10 accounting miller 10 research jones 20 research ford 20 research adams 20 research smith 20 research scott 20 sales ward 30 sales turner 30 sales allen 30 SALES JAMES 30 sales blake 30 sales martin 30OPERATIONS15 Rows selected -- SQL 99 standard writing method followed by SQL> select d. dname, e. ename, e. deptnofrom dept dleft join emp eon d. deptno = e. deptnoorder by d. deptno; dname ename deptno ------------ ---------- accounting clark 10 accounting king 10 accounting miller 10 research jones 20 research ford 20 research adams 20 research smith 20 research scott 20 sales ward 30 sales turner 30 sales allen 30 SALES JAMES 30 sales blake 30 SALES MA RTIN 30OPERATIONS15 rows selected right outer join -- oracle writing SQL> select empno, ename, job, sal, dept. deptno, dname, loc2 from emp, dept3 where emp. deptno (+) = dept. deptno; empno ename job sal deptno dname loc ----- ---------- --------- ------ -------------- ------------- 7782 clark manager 2450.00 10 accounting new YORK7839 king president 5000.00 10 accounting new YORK7934 miller clerk 1300.00 10 Nting new YORK7566 jones manager 2975.00 20 RESEARCH DALLAS7902 ford analyst 3000.00 20 RESEARCH DALLAS7876 adams clerk 1100.00 20 research smith clerk 800.00 20 RESEARCH DALLAS7788 scott analyst 3000.00 20 RESEARCH DALLAS7521 ward salesman 1250.00 30 SALES chicag7844 TURNER SALESMAN 1500.00 30 SALES chicag7499 allen salesman 1600.00 30 SALES chicag7900 james clerk 950.00 30 SALES CH Icag7698 blake manager 2850.00 30 SALES chicag7654 martin salesman 1250.00 30 SALES chicag40 OPERATIONS BOSTON15 rows selected -- SQL1999 standard syntax SQL> select empno, ename, job, sal, dept. deptno, dname, loc2 from emp3 right join dept4 on emp. deptno = dept. deptno; empno ename job sal deptno dname loc ----- ---------- --------- ------ -------------- ------------- 7782 clark manager 2450.00 10 ACCO Unting new YORK7839 king president 5000.00 10 accounting new YORK7934 miller clerk 1300.00 10 accounting new YORK7566 jones manager 2975.00 20 research ford analyst 3000.00 20 RESEARCH DALLAS7876 adams clerk 1100.00 20 RESEARCH DALLAS7369 smith clerk 800.00 20 RESEARCH DALLAS7788 scott analyst 3000.00 20 RESEARCH DALLAS7521 ward salesman 1250.00 30 SALES chicag7844 turner salesman 1500. 00 30 SALES Chicago 7499 allen salesman 1600.00 30 SALES Chicago 7900 james clerk 950.00 30 SALES Chicago 7698 blke MANAGER 2850.00 30 SALES Chicago 7654 martin salesman 1250.00 30 SALES Chicago 40 OPERATIONS BOSTON15 rows selected full connection -- SQL1999 standard writing SQL> select empno, ename, job, sal, d. deptno, dname, loc2 from emp e3 full join dept d4 on e. deptno = d. deptno; empno ename job sal deptno dname loc -------- ------- --------- ------ ------------ ------------- 7369 smith clerk 800.00 20 RESEARCH lead allen salesman 1600.00 30 SALES Chicago 7521 ward salesman 1250.00 30 SALES Chicago 7566 jones manager 2975.00 20 RESEARCH lead martin salesman 1250.00 30 SALES Chicago 7698 blke MANAGER 2850.00 30 SALES chicag7782 clark manager 2450.00 10 accounting new YORK7788 scott analyst 3000.00 20 RESEARCH history king president 5000.00 10 accounting new YORK7844 turner salesman 1500.00 30 SALES chicag7876 adams clerk 1100.00 20 RESEARCH DALLAS7900 james clerk 950.00 30 SALES Chicago 7902 ford analyst 3000.00 20 RESEARCH DALLAS7934 miller clerk 1300.00 10 ACCOUNTING NEW YORK40 OPERATIONS BOSTON15 rows selected self-join treats the image of the table as another table -- oracle writing SQL> select e. ename | 'Works for '| m. enam E2 from emp e, emp m3 where e. empno = m. mgr; E. ENAME | 'worksfor' | M.ENAME-------------------------------JONES works for FORDJONES works for scottblke works for turnerblke works for allenblke works for WARDBLAKE works for jamesblke works for MARTINCLARK works for MILLERSCOTT works for ADAMSKING works for blakkeking works for JONESKING works for CLARKFORD works for SMITH13 rows selected -- SQL1999 Standard SQL> select e. ename | 'Works for '| m. ename2 from emp e3 join emp m4 on e. empno = m. mgr; E. ENAME | 'worksfor' | M.ENAME-------------------------------JONES works for FORDJONES works for scottblke works for turnerblke works for allenblke works for WARDBLAKE works for jamesblke works for MARTINCLARK works for MILLERSCOTT works for ADAMSKING works for blakkeking works for JONESKING works for C LARKFORD works for SMITH13 rows selected are naturally connected based on all columns with the same fields in two tables. natural join can be used) it selects the rows in the two tables that have the same values in all matched columns. If a column has the same name but different data types, an error is returned. -- SQL1999 standard syntax SQL> select empno, ename, job, sal, deptno, dname, loc2 from emp natur_3 join dept; empno ename job sal deptno dname loc ----- ---------- --------- ------ -------------- accept 7782 clark manager 2450.00 10 accounting new YORK7839 king president 5000.00 10 accounting new YORK7934 miller clerk 1300.00 10 accounting new Employee jones manager 2975.00 20 RESEARCH DALLAS7902 Ford analyst 3000.00 20 RESEARCH DALLAS7876 adams clerk 1100.00 20 research analyst smith clerk 800.00 20 RESEARCH DALLAS7788 scott analyst 3000.00 20 RESEARCH DALLAS7521 ward salesman 1250.00 30 SALES Chicago 7844 turner salesman 1500.00 30 SALES Chicago 7499 allen salesman 1600.00 30 SALES Chicago 7900 james clerk 950.00 30 SALES Chicago 7698 blke MANAGER 2850.00 30 SALES Chicago 7654 MARTIN SAL ESMAN 1250.00 30 SALES chicag14 rows selected use the using clause to create a connection. If several columns have the same name but the data type does not match, you can use the using clause to modify the natural join clause to specify the columns to be used for equivalent join. When multiple columns match, use the using clause to match only one column, do not use the table name or alias in the reference column. Natural join and using clauses are incompatible. SELECT l. city, d. department_nameFROM locations l JOIN orders ments d USING (location_id) WHERE location_id = 1400; -- The following statement is invalid because the where Clause limits location_id to d. location_idSELECT l. city, d. department_nameFROM locations l JOIN orders ments d USING (location_id) WHERE d. location_id = 1400; ORA-25154: column part of USING clause cannot have qualifier Note: When USING columns with the same name in both tables, there is no qualifier, this restriction applies to natural joincross join for cross join. The result set generated by the connection is the Cartesian Product -- oracle statement: select empno, ename, dnamefrom emp, dept; in oracle syntax, the two directly from tables are cross join -- SQL1999 standard syntax select empno, ename, dnamefrom empcross join dept; when adding conditions for equijoin, all the above multi-Table connections are equijoin (most commonly used). You can also add conditions after the equijoin. -- Oracle statement: select e. empno, e. ename, d. dnamefrom emp e, dept dwhere d. deptno = e. deptnoand e. ename = 'Scott '; -- SQL1999 standard syntax select e. empno, e. ename, d. dnamefrom emp einner join dept don d. deptno = e. deptnoand e. ename = 'Scott '; non-equivalent join -- oracle statement: select ename, sal, gradefrom emp, salgradewhere sal between losal and hisaland emp. deptno = 20; -- SQL1999 standard statement select e. ename, e. sal, s. gradefrom emp ejoin salgra De son e. sal between losal and hisaland e. deptno = 20; In summary, the Oracle syntax is more user-friendly, except the left and right connections. If you want to make the code simple and clear, use the oracle syntax, and try to use the standard SQL syntax if you want to make the written code more compatible (may be transplanted to DB2, etc. For more information, see oracle official documentation Oracle®DatabaseSQL Language Reference11g Release 2 (11.2) E17118-049 SQL Queries and Subqueries Section