05.SQL Basis--Multi-table query

One, multi-table query

In short, obtain the required data from a different table based on a specific join condition:   omit join condition   Invalid join condition   All rows in the first table are connected to all rows in the second table

　　

Second, multi-table query syntax:

SELECT Table1.column, Table2.columnfrom table1, table2where table1.column1 = table2.column2; But notice where you don't save, omit where That is, the Cartesian set, and the Where condition to be valid, the two tables have an identical field, before a valid multi-table query query when the column name, add a table name or table alias prefix (if the field is unique in two tables can not add) in order to simplify SQL writing, you can define an alias for the table name, format: from Table name aliases such as: from EMP e,dept D recommends using table aliases and table prefixes, using table aliases to simplify queries, and using table prefixes to improve query Performance example: query each employee's work number, name, salary, department name and duty station Select Empno,ename,sal, Dname,loc from Emp,dept where Emp.deptno=dept.deptno;

Three, multi-table connection type:

From the way the data is displayed: internal and external connections. Internal connection: Only data that satisfies the join condition is returned. Outer joins: In addition to returning rows that satisfy the join bar, returns a row in the left (right) table that does not meet the criteria, called the Left (right) connection within the connection */select Empno,ename,sal,dname,loc from Emp,deptwhere emp.deptno=  Dept.deptno; --(Oracle 8i and previous notation)--another way of writing an inner connection: select Empno,ename,job,sal,dept.deptno,dname,locfrom EMP Join dept on (emp.deptno= DEPT.DEPTNO); --(Syntax for SQL 99) outer joins: Two tables in a query that uses an outer join to query another table or two data that does not meet the join criteria. The outer join symbol is (+) and (+) is placed after the field name. (+) The other side of the table, will show all. Outer JOIN Syntax */select table1.column, table2.column--right outer join from table1, table2 WHERE table1.column (+) = Table2.column; Select Table1.column, Table2.column--left outer connection from table1, table2 WHERE table1.column = table2.column (+);--Example: SELECT EMP No,ename,job,sal,dept.deptno,dname,locfrom emp,deptwhere Emp.deptno (+) =dept.deptno; --(Oracle 8i and previous notation)--another way of writing (right connection):--(Syntax for SQL 99) Select Empno,ename,job,sal,dept.deptno,dname,loc from emp right join Dept on (EMP.DEPTNO=DEPT.DEPTNO);--left Join: (SQL 99 notation) Select Empno,ename,job,sal,dept.deptno,dname,loc fro M EMP left JOIN dept on (EMP.DEPTNO=DEPT.DEPTNO);--FullConnection (full connection) (SQL 99 notation) Select Empno,ename,job,sal,d.deptno,dname,loc from emp e full join Dept D on (E.DEPTNO=D.DEP TNO); Natural joins (The way SQL 99 is written) base all columns with the same field as two tables, and can take a natural join (natural join) It will select rows from two tables that have equal values in all matching columns. If the column has the same name but the data type can be different, an error */select Empno,ename,job,sal,deptno,dname,loc from EMP Natural join dept;/* Self-connect: treats one mirror of its own table as another table. */select. From emp e,emp D ...-the syntax for SQL 99 is listed below for reference 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)];--in the following syntax Table1.column--Indicates the table and column from which data is retrieved cross JOIN   --Natural join for two tables--joins two tables based on the same column name join Tableusing column_name--Performs an equivalent join based on the name of a column table Ontable1.column_name --Performs an equivalent connection according to the conditions in the ON clause = Table2.column_nameleft/right/full outer/* creates a connection using a using clause if several columns have the same name, but the data type does not match, You can then use the Using clause to modify the natural join clause to specify that the column to be used for the equivalent join will be used when multiple columns match, using uThe SING clause matches only one column in the Reference column do not use table names or aliases natural join and using clauses are incompatible */--for example: SELECT l.city, d.department_name from Locatio   NS L JOIN Departments D USING (location_id) WHERE location_id = 1400; --The following statement is invalid because the WHERE clause qualifies location_id SELECT l.city, d.department_name from locations L JOIN departments D USING (Locati   on_id) WHERE d.location_id = 1400;  Ora-25154:column part of using clause cannot has qualifier--note:--two tables with the same name cannot have any qualifiers when used, the same restriction applies to natural join

　　

Iv. Demo: */

--Descartes Set

Sql> select Empno,ename,dname from Emp,dept;

　　

--using cross joins to achieve a crossover connection, the Cartesian set

Sql> Select Empno,ename,dname from the EMP Cross join dept;

EMPNO ename dname

---------- ---------- --------------

7369 SMITH ACCOUNTING

7499 ALLEN ACCOUNTING

7521 WARD ACCOUNTING

7566 JONES ACCOUNTING

7654 MARTIN ACCOUNTING

7698 BLAKE ACCOUNTING

7782 CLARK ACCOUNTING

7788 SCOTT ACCOUNTING

7839 KING ACCOUNTING

7844 TURNER ACCOUNTING

7876 ADAMS ACCOUNTING

--equivalent connection (Oracle notation)

Sql> Select Empno,ename,dname from emp,dept where emp.deptno = Dept.deptno;

EMPNO ename dname

---------- ---------- --------------

7369 SMITH

7499 ALLEN SALES

7876 ADAMS

--Partial results omitted

7902 FORD

7934 MILLER ACCOUNTING

Rows selected.

--Equivalent connection (SQL 99 notation)

Sql> Select E.empno,e.ename,d.dname from emp E
INNER JOIN Dept D
on e.deptno = D.deptno;

EMPNO ename dname

---------- ---------- --------------

7369 SMITH

7499 ALLEN SALES

7876 ADAMS

--Partial results omitted

7902 FORD

7934 MILLER ACCOUNTING

Rows selected.

--Note: Table aliases do not support as usage

Sql> Select E.empno,e.ename,d.dname from EMP as E

2 INNER JOIN Dept D

3 on e.deptno = D.deptno;

Select E.empno,e.ename,d.dname from EMP as E

*

ERROR at line 1:

Ora-00933:sql command not properly ended

--equivalent connection and additional conditions

Sql> Select E.empno,e.ename,d.dname from emp E,

2 Dept D

3 Where D.deptno = E.deptno

4 and E.ename = ' SCOTT ';

EMPNO ename dname

---------- ---------- --------------

7788 SCOTT

--Non-equivalent connection

--Query the employee's name, salary, level, and departmental records

Sql> Select Ename,sal,grade

2 from Emp,salgrade

3 where Sal between Losal and Hisal

4 and Emp.deptno = 20;

Ename SAL GRADE

---------- ---------- ----------

SCOTT 3000 4

FORD 3000 4

JONES 2975 4

ADAMS 1100 1

SMITH 800 1

--Use SQL 99 to implement the above functions

Sql> Select E.ename,e.sal,s.grade

2 from EMP E

3 Join Salgrade S

4 on e.sal between Losal and Hisal

5 and E.deptno = 20;

Ename SAL GRADE

---------- ---------- ----------

SCOTT 3000 4

FORD 3000 4

JONES 2975 4

ADAMS 1100 1

SMITH 800 1

--Right outer connection

--Note: The plus sign is on the left side of the equals sign when the right outer connection

--you can see that the column in the left table EMP has a null value

Sql> Select E.ename,e.deptno,d.dname from emp e,dept D where e.deptno (+) = D.deptno;

Idle> Select E.ename,e.deptno,d.dname from emp e,dept D where e.deptno (+) = D.deptno; ename       DEPTNO dname----------------------------------CLARK   accountingking   accountingmiller   Accountingjones   researchford   researchadams   researchsmith   20 Researchscott Researchward   salesturner   salesallen   salesjames   salesblake   SALES      OPERATIONS15 rows selected.

--Use SQL 99 to make a right outer join

Sql> Select E.ename,e.deptno,d.dname from emp e right joins dept D on e.deptno = D.deptno;

Idle> Select E.ename,e.deptno,d.dname from emp e right joins dept D on e.deptno = D.deptno; ename       DEPTNO dname----------------------------------CLARK   accountingking   accountingmiller   Accountingjones   researchford   researchadams   researchsmith   20 Researchscott Researchward   salesturner   salesallen   salesjames   salesblake   SALES      OPERATIONS15 rows selected.

--Left outer connection

--Note: The plus sign is on the right side of the equal sign when left outer connection

Select D.dname,e.ename,e.deptno from dept d,emp e where D.deptno = E.deptno (+) Order by D.deptno;

Idle>  Select D.dname,e.ename,e.deptno from dept d,emp e where D.deptno = E.deptno (+) Order by D.deptno;dname       EN AME      DEPTNO----------------------------------ACCOUNTING     CLARK  10ACCOUNTING     KING  10ACCOUNTING     MILLER  10RESEARCH       JONES  20RESEARCH       FORD  20RESEARCH       ADAMS  20RESEARCH       SMITH  20RESEARCH       SCOTT  20SALES       WARD  30SALES       TURNER  30SALES       ALLEN  30SALES       JAMES  30SALES       BLAKE  30SALES       MARTIN  30operations15 rows Selected.

　

--Using SQL 99 to make a LEFT outer join

Sql> Select D.dname,e.ename,e.deptno from dept D left JOIN emp e in D.deptno = E.deptno order by D.deptno;

Idle> Select D.dname,e.ename,e.deptno from  dept D left JOIN emp e in D.deptno = E.deptno  ORDER by D.deptno;dnam E       ename      DEPTNO----------------------------------ACCOUNTING     CLARK  10ACCOUNTING     KING  10ACCOUNTING     MILLER  10RESEARCH       JONES  20RESEARCH       FORD  20RESEARCH       ADAMS  20RESEARCH       SMITH  20RESEARCH       SCOTT  20SALES       WARD  30SALES       TURNER  30SALES       ALLEN  30SALES       JAMES  30SALES       BLAKE  30SALES       MARTIN  30operations15 rows Selected.

　

--Self-connected

Sql> Select E.ename | | ' Works for ' | | M.ename from EMP e,emp m where e.empno = M.mgr;

Idle> Select E.ename | | ' Works for ' | | M.ename from  emp e,emp m where e.empno = M.mgr; e.ename| | ' WorksFor ' | | M.ename-------------------------------JONES works for fordjones works for Scottblake works for Turnerblake works for ALLEN BLAKE works for Wardblake works for Jamesblake works for Martinclark works for Millerscott works for adamsking works for B Lakeking works for jonesking works for Clarkford works for SMITH13 rows selected.

--Natural connection

Sql> Select Empno,ename,job,deptno,dname,loc from EMP Natural Join dept;

Idle> Select Empno,ename,job,deptno,dname,loc from EMP Natural Join dept;     EMPNO ename      JOB    DEPTNO dname  LOC------------------------------------------------------------------      7782 CLARK      MANAGER10 ACCOUNTING  New York      7839 KING       President ACCOUNTING  New York      7934 MILLER     CLERK10 ACCOUNTING  NEW YORK      7566 JONES      MANAGER20 the  DALLAS      7902 FORD       ANALYST20  DALLAS      7876 ADAMS      CLERK20  DALLAS      7369 SMITH      CLERK20  DALLAS      7788 SCOTT      ANALYST20  DALLAS      7521 WARD       SALESMAN30 SALES  CHICAGO      7844 TURNER     SALESMAN30 sales  CHICAGO      7499 ALLEN      SALESMAN30 sales  CHICAGO      7900 JAMES      CLERK30 SALES  CHICAGO      7698 BLAKE      MANAGER30 sales  CHICAGO      7654 MARTIN     SALESMAN30 sales  CHICAGO14 Rows selected.

--Using a using clause to create a connection

Select E.empno,e.ename,d.dname,d.loc
From EMP E
Join Dept D
using (DEPTNO)
where Deptno in (20,40);

idle>  Select e.empno,e.ename,d.dname,d.locfrom emp ejoin Dept dusing (DEPTNO) where Deptno in (20,40);  2    3    4    5       EMPNO ename      dname     LOC-----------------------------------------------      7369 SMITH     DALLAS      7566 JONES     DALLAS      7788 SCOTT     DALLAS      7876 ADAMS     DALLAS      7902 FORD     DALLAS

05.SQL Basis--Multi-table query