SQL multi-Table query

------   

Cartesian product: TableA * TableB

SELECT * FROM Emp,dept

　　 

one, 92 standard multi-table query(1) equivalent query

Two tables are a lateral relationship

1, equivalent query

select empno,ename,dname
from Emp,dept
Span style= "font-family: italics; font-size:16px; " > WHERE ename = ' SMITH '
and EMP. DEPTNO = DEPT. DEPTNO;

2, non-equivalent query

select E.empno,e.ename,e.sal,s.losal,s.hisal, S.grade
from EMP e,salgrade S
WHERE e.ename = ' SMITH '
and E.sal >=s.losal
and E.sal <=s.hisal;

(2) External connection query

Primary and secondary table relationship

1. Left Outer connection

SELECT E.empno,e.ename,d.dname,d.deptno
From EMP e,dept D
WHERE E.deptno = D.deptno (+);

2. Right outer connection

SELECT E.empno,e.ename,d.dname,d.deptno
From EMP e,dept D
WHERE E.deptno (+) = D.deptno;

Remark: When the condition is too many, the connection condition is many, the filter condition is many, it is easy to cause confusion　　two, 99 standard multi-table query

(1) Cross Join

SELECT * from Emp,dept; --92 Standard

SELECT * from the EMP cross JOIN DEPT; --99 Standard

(2) Natural connection NATURAL join similar to equivalent connection

SELECT E.empno,e.ename,d.dname

From EMP E, DEPT D
WHERE E.deptno = D.deptno; --92 does not specify a connected column

SELECT E.empno,e.ename,d.dname,deptno
From EMP E NATURAL JOIN DEPT D
WHERE DEPTNO = 10; --99

(3) Internal chain query inner join inner can save

The inner connection is divided into equivalent connection and unequal connection two kinds

select *  from t_student s,t_class c where  s.classid = c.classid      equals        select * from t_student s  inner join t_class c on  S.classid = c.classid

SELECT * from t_student s inner joins T_class C on s.classid <> c.classid

(4) Using clause: when many of the same names appear in the table of the necklace, the natural connection will not meet the requirements, and you can use the Using clause to set the column name for the equivalent connection at connection time

SELECT E.empno,e.ename,d.dname,deptno

From EMP E NATURAL JOIN DEPT D
USING (DEPTNO)
WHERE DEPTNO = 10;

--using to define which column of the same name to use to connect

(5) The ON clause is the condition used when generating temporary tables where is the filtering of temporary tables (on and and on where both filter the resulting temporal table)

SELECT e.empno,e.ename,d.dname from EMP E join DEPT D on e.deptno = D.deptno

SELECT e.empno,e.ename,d.dname from EMP E join DEPT D on e.deptno = D.deptno
and e.empno = 7788

SELECT e.empno,e.ename,d.dname from EMP E join DEPT D on e.deptno = D.deptno
WHERE E.empno = 7788

(6) External connection

1. Left OUTER JOIN

SELECT E.empno,e.ename,d.dname,d.deptno
From EMP E left join DEPT D
on e.deptno = D.deptno;

2. Right outer join

SELECT E.empno,e.ename,d.dname,d.deptno
From EMP E right join DEPT D
On e.deptno = D.deptno (+);

3. Cross-connect full join

SELECT E.empno,e.ename,d.dname,d.deptno
From EMP E full join DEPT D
On e.deptno = D.deptno (+);

------

SQL multi-Table query