Oracle Multi-Table query

basic Syntax Multi-table Query

The Cartesian product is implemented in SQL as a cross join. All connections will be made by Mr Sir as a temporary Cartesian product table. The Cartesian product is a concept in relational algebra that represents the arbitrary combination of data in each of the 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-the MGR in the EMP table represents the number of the superior leader of an employee. If you want to query an employee's superior leader name now, you need to use the EMP table and the EMP table for self-connection operation Select E.ename, E.job, s.ename from EMP S, emp e where s.empno = E.mgr; --Check out each employee's name and work. The name of the employee's direct superior leader, the employee's department named select E.ename, E.job,s.ename mgr_name, d.dname from EMP E, EMP S, dept d where e.mgr = S.empno and E.deptno = d.deptno;--queries the name, salary, and department names of each employee. Salary at the company level and the name of the leader. Leadership of the salary, and the corresponding rank of leader Select E.ename, E.sal, D.dname,decode (g.grade,5, ' first class wages ', 4, ' Second class wages ', 3, ' Third class wages ', 2, ' fourth wages ', 1, ' fifth wages ') 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-wage ') 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.losal 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 did not appear, this is due to the common connection in the side of 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 is now 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 the number of its leaders. Name Select E.empno, E.ename, S.empno, S.enamefrom emp E, emp swhere e.mgr = s.empno;

--but we know 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 mode of the left and right connections is not fixed. The detailed use of the left JOIN or the right connection depends on the reference condition on the left or right side of the equation, and the left connection above the reference condition on the right. It becomes the right connection select E.empno, E.ename, S.empno, S.enamefrom emp E, emp swhere s.empno (+) = E.mgr;

Get the same result value, here is not repeated mapping.

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;

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

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

--can be found based on results. Natural joins are connected by public fields equal. And the resulting results will voluntarily eliminate the repeated column 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;

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

--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 the repeated recording of select * FROM Empunionselect * from Empbak;

--UNION All collection does not remove the repeated recording of select * FROM Empunion allselect * from Empbak;

The following three records are recorded repeatedly, in the above union connection, the extra three repeated records are removed.

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

Oracle Multi-Table query