SQL connection Query

1. SQL92 syntax

Connection query: You can also call a cross-table query, you need to correlate multiple tables for querying

• Displays each employee information and displays the name of the department to which it belongs

Select  from EMP, dept;

Sql> Select ename, dname from EMP, dept; Ename dname ---------- -------------- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING's TURNER ADAMS JAMES FORD MILLER SMITH SALES ALLEN SALES WARD SALES JONES SALES MARTIN SALES BLAKE SALES CLARK SALES SCOTT SALES KING SALES TURNER SALES ADAMS SALES JAMES SALES FORD SALES MILLER SALES SMITH OPERATIONS ALLEN OPERATIONS WARD OPERATIONS JONES OPERATIONS MARTIN OPERATIONS BLAKE OPERATIONS CLARK OPERATIONS SCOTT OPERATIONS KING OPERATIONS TURNER OPERATIONS ADAMS OPERATIONS JAMES OPERATIONS FORD OPERATIONS MILLER OPERATIONS 56 rows have been selected.

The above output, incorrect, output 56 data, in fact, is the product of two table records, this situation we call: "Flute Descartes Product", the cause of the error is: No connection conditions are specified

Specify connection conditions

1 Select  from where emp.deptno=dept.deptno; 2 -- You can also use aliases (but you can't add the AS keyword) 3 Select  from where a.deptno=B.deptno;

The above results are output correctly because the correct connection is added

The above query is also referred to as an " inner join ", which refers to querying for equal data

• Get the name of the employee and the manager who owns it

Select  from where a.mgr=b.empno;

The above is called "self-Connection", only a table connection, the specific query method, a table as two tables can be, such as the above example: the first table EMP a code The employee table, EMP B represents the manager table, equivalent to the employee table and the Department table

2. SQL99 syntax

• (internal connection) displays employee information with a salary greater than 2000 and displays the name of the department to which it belongs

1 --use sql99 syntax:2 SelectENAME, Dname fromEMP AJoinDept BOn A.deptno=b.deptno whereSal> -;3 --or4 SelectENAME, Dname fromEMP AINNER JOINDept B onA.deptno=B.deptnowhereSal> -;5 --Note: Inner keywords are generally omitted6 --use SQL92 Syntax:7 SelectENAME, Dname fromEMP A, Dept bwhereA.deptno=B.deptno andSal> -;

Sql92 syntax and sql99 syntax differences: 99 syntax can be used to make the table connection and query condition separation, especially when multiple tables are connected, will be clearer than sql92

• (Outer connection) displays employee information with a salary greater than 2000, and displays the name of the department to which it belongs, and if a department does not have an employee, the department must also show it (the associated 2 tables have a master-slave relationship, then if the main table is on the left side of the join, use the left connection, if the The right connection is used)

-- Right Connection Select  from  Join on A.deptno=b.deptno; -- You can also use the left join  Select  from  Join on A.deptno=B.deptno;

 --  add outer   a.ename, b.dname from  dept B left   outer   join  EMP A on  a.deptno=  B.DEPTNO;  --  join outer  select  a.ename, b.dname from  emp a right   outer   join  Dept B on  a.deptno=  B.deptno; 

Left join can complete the function of the right connection must be completed

Use the SQL92 syntax to complete the related query for the left connection and right connection:

Use (+) to place the opposite of the main table

• Get the name of the employee and the manager, and if there is no superior manager, you should also inquire.

Select  from where m.empno (+= e.mgr;

SQL connection Query