Oracle (SQL) illiteracy sweeping Mind Map series (III.)--Multi-table connection query subquery

Go on

Some demo

--multiple table queries can be implemented with the join operator, which is the main feature of the relational database model. --when you indicate a connection in the FROM clause, it helps to differentiate the join operation from the search conditions in the WHERE clause. --In general, connection queries are a bit more efficient than nested queries. Therefore, it is recommended to use this method in Transact-SQL. Select *  fromEMP;Select *  fromDept;Select *  fromSalgrade;--First, the inner joins are the most typical and most commonly used connection operations, and the inner joins are usually by connecting predicates or operators (such as join On,natural Join,cross join,=,>=,<=,in,not in, etc.)--The columns of two tables are combined to produce a new result table. --internal connections include: equivalent connection, non-equivalent connection, natural connection, cross connection (Cartesian product connection a*b)--equivalent joins, where a connection between tables is a query connected by an equal field value called an equivalent connection query .--SQL1992, making multiple table joins in the WHERE clauseSelectEname,dname fromEmp,deptwhereEmp.deptno=Dept.deptno;--SQL1999, using join ... on. The way to make table connectionsSelectEname,dname fromEmpJoinDept on(Emp.deptno=Dept.deptno);--A non-equivalent connection that does not use the equals sign in the join condition of the equivalent query, and the use of other comparison operators constitutes a non-equivalent connection query. --The comparison operators you can use are:>, >, =, <, <=,! =, and you can also use between ... Predicates such as and--SQL1992, making multiple table joins in the WHERE clauseSelectEname,grade fromEmp,salgradewhereSalbetweenLosal andhisal;SelectEname,grade fromEmp,salgradewhereSal>=Losal andSal<=hisal;--SQL1999, using join ... on. The way to make table connectionsSelectEname,grade fromEmpJoinSalgrade on(SalbetweenLosal andhisal);SelectEname,grade fromEmpJoinSalgrade on(Sal>=Losal andSal<=hisal);--naturally connected, the natural connection is to look for those fields with the same data type and column names in both tables, then automatically connect them and return all results that match the criteria. SelectEmp.ename,dept.dname fromEMP NaturalJoinDept;--Here we do not specify the conditions of the connection, in fact Oracle for us to take the liberty of the EMP in the Deptno and dept in the Deptno made a connection,--similar to the equivalent connection, the difference is that the natural connection will go heavy, while the equivalent connection is not. --Cross join , which returns the Cartesian product of both tablesSelect *  fromEmp Cross JoinDept;--The main difference between outer joins, outer joins and inner joins is that the internal connection only queries the records that satisfy the connection condition, and the external connection will not be able to generate the connection data is also queried--outer joins can be further divided into left outer join, right outer connection and full connection according to the connection table to keep left table, right table or all rows of table--right outer join, keep the table on the right cannot generate a connection record (Extra records)SelectENAME, Dname fromEMP E Right JoinDept D on(E.deptno=D.deptno);--left OUTER JOIN, left table cannot generate connection record (extra record)SelectE1.ename, E2.ename fromEMP E1 Left JoinEMP E2 on(E1.mgr=e2.empno);--full external link, the left and right side of the record can not generate connections to query outSelectENAME, Dname fromEMP E Full JoinDept D on(E.deptno=D.deptno);--From the connection, the same table will be different aliases, and then as two tables to use. The self-connection can be connected either inside or outside. SelectE1.ename,e2.ename fromEMP E1JoinEMP E2 on(E1.mgr=e2.empno);SelectE1.ename,e2.ename fromEMP E1 Right JoinEMP E2 on(E1.mgr=e2.empno);SelectE1.ename,e2.ename fromEMP E1 Left JoinEMP E2 on(E1.mgr=e2.empno);--use a self-join implementation to find the highest salary without a group function--(1) first to find out that each employee is less than his salary salary staffSelectE1.ename,e1.sal fromEMP E1JoinEMP E2 on(E1.sal<e2.sal);--(2) go to heavySelect distinctE1.ename,e1.sal fromEMP E1JoinEMP E2 on(E1.sal<e2.sal);--(3) The highest salary is not in the result of (2) querySelect distinctSal fromEmpwhereSal not inch    (Select distinctE1.sal fromEMP E1JoinEMP E2 on(E1.sal<e2.sal));--Subqueries , subqueries are essentially a restricted SELECT statement nested into other select,update,insert,delete statementsSelect Max(SAL) fromEMP;SelectEname,sal fromEmpwhereSal=(Select Max(SAL) fromEMP);SelectT1.ename,t1.deptno fromEMP T1Join (SelectDeptnoMax(SAL) Max_sal fromEmpGroup  bydeptno) T2 on(T1.deptno=T2.deptno andT1.sal=T2.max_sal)

Let's get here today.

Oracle (SQL) illiteracy sweeping Mind Map series (III.)--Multi-table connection query subquery