Oracle Multi-Table query

Multi-table query basic Syntax

The Cartesian product is implemented in SQL as a cross join. All connections are made to the temporary Cartesian product table, the Cartesian product is a concept in relational algebra that represents any combination of rows of data in two tables.

--Cartesian Product SELECT * from EMP, dept;

--using common fields, remove the Cartesian product select * from EMP, dept where emp.deptno = Dept.deptno;

--Query out the name of each employee, work, the name of the employee's direct superior leader--Mgr in the EMP table represents the number of an employee's superior leader, and if you want to query an employee's superior leader name now, you need to use the EMP table with the EMP table for the self-connect operation Select E.ename, E.job, s.ename from EMP S, emp e where s.empno = E.mgr; --Query out the name of each employee, work, the name of the employee's direct superior leader, the name of the employee's department, select E.ename, E.job,s.ename mgr_name, d.dname from EMP E, EMP S, dept D where E.M GR = S.empno and E.deptno = d.deptno;--query out the name of each employee, salary, department name, salary at the company level and the name of the leader, the salary of the leader, and the rank of the leader Select E.ename, E.sal, D.dname,decode (g.grade,5, ' first-rate wages ', 4, ' second-rate ', 3, ' third-rate ', 2, ' fourth-wage ', 1, ' fifth-rate ') E_grade, S.ename mgr_name, S.sal mgr_sal, Decode (g2.grade,5, ' first-rate wages ', 4, ' second-rate ', 3, ' third-rate ', 2, ' fourth-wage ', 1, ' fifth-rate ') m_gradefrom emp e,dept D,salgrade g,emp S, Salgrade G2where (E.deptno=d.deptno) and (E.sal between G.losal and G.hisal) and (S.empno=e.mgr) and (S.sal BETWEEN g2.losa L and G2.hisal);

left and right connections

--Use EMP and dept to make a connection query, query results include employee number, employee name, department number, department name, department location select E.empno, E.ename, D.deptno, D.dname, d.locfrom emp e,dept D where E.deptno = D.deptno;

--through observation is not difficult to find, in the above results, the department number 40 does not appear, this is because in the ordinary connection when both sides have appeared in the results, there is no department 40 in the EMP table, so the result is no 40select e.empno, E.ename, D.deptno, D.dname, D.locfrom emp E, Dept dwhere e.deptno (+) = D.deptno;

Department 40 appears in the results. At this point we are using the right connection.

(+) on = left indicates right connection
(+) on = right indicates left connection

Left join to left table as benchmark, right to right table

--left connection to left table as the benchmark, right to connect to the right table as the benchmark select E.empno, E.ename, D.deptno, D.dname, D.locfrom emp E, dept dwhere e.deptno = D.deptno (+);

--Query the employee's number, name, and its leader's number, name select E.empno, E.ename, S.empno, S.enamefrom emp E, emp swhere e.mgr = s.empno;

--but we know that the EMP table should have 14 records, in the above results are missing King, because King is the highest leader, so his leader number is empty, so there is no way to find out. To show it, we use the left/right connection to implement select E.empno, E.ename, S.empno, S.enamefrom emp E, emp swhere e.mgr = s.empno (+);

-The connection of the right and left connection is not fixed, the specific use of the left or right connection depends on the reference condition on the left or right side of the equation, the upper left connection to the right of the reference condition, it becomes a e.empno, E.ename, S.empno, S.enamefrom emp E, emp swhere s.empno (+) = E.mgr;

The same result is obtained, the map is not repeated here.

Sql:1999sql Definition

Syntax format

SELECT Table1.column,table2.column

from table1 [Cross JOIN table2]|

[NATURAL JOIN table2]|

[JOIN table2 USING (column_name)]|

[JOIN table2 on (table1.column_name=table2.column_name)]|

[left| Right| Full OUTER JOIN table2 on (table1.column_name=table2.column_name)]

--Cross join: The Cartesian product operation SELECT * from EMP Cross JOIN dept;

SELECT * FROM EMP Cross Join dept where emp.empno = 7369;

--Natural Connection NATURAL Joinselect * from EMP NATURAL join dept;

--based on the results, it can be found that natural connections are connected by common fields, and the resulting results automatically eliminate duplicate columns select * from EMP E, dept dwhere e.deptno = D.deptno;

--using (column_name) to specify a connection field between two tables select * from EMP Join Dept USING (DEPTNO);

--On (Tab1.column_name=tab2.column_name) is used to specify the connection condition of the two tables select * from EMP Join dept on emp.deptno = Dept.deptno;

--Left[outer] Join left connection OUTER Optional select E.empno, E.ename, D.deptno, D.dname, d.locfrom emp e ieft Join dept Don E.deptno = D.deptno;select E.empno, E.ename, D.deptno, D.dname, d.locfrom EMP e left OUTER JOIN dept Don E.deptno = D.deptno;

--Right Join Select E.empno, E.ename, D.deptno, D.dname, D.locfrom EMP e r Join dept Don E.deptno = D.deptno;

--INNER JOIN takes intersection of CREATE table Empbak as SELECT * from EMP where emp.empno in (7369, 7499, 7521, 7566, 7654, 7698); Select E 1.empno, E1.empno, E1.job, E1.mgr, E1.hiredate, E1.sal, E1.comm, E1.deptno from EMP E1 INNER join Empbak e2 on e1.empno = E2.empno;

--Full OUTER JOIN fetch and set update Empbak e set e.empno = 8369 where e.empno = 7369;update Empbak e set e.empno = 8499 where e.e Mpno = 7499;update Empbak e set e.empno = 8521 where e.empno = 7521;select * from emp e full join Empbak s on e.empno = S. Empno

--for records that do not match, NULL is the value of the SELECT * from emp e full join Empbak s on e.empno = S.empnowhere e.empno are NOT null and s.empno are Not null;

--The UNION takes and sets out duplicate records SELECT * FROM Empunionselect * from Empbak;

--UNION All collection does not remove duplicate records select * FROM Empunion allselect * from Empbak;

The bottom three records are duplicate records, in the above union connection, the extra three duplicate records are removed.

Oracle Multi-Table query