Advanced queries for Oracle databases (multiple-table queries)

The basic sections of some advanced queries are written earlier. Here are some ways to write multiple-table queries. Here the SQL statement field is not very thin, because of the time problem, we just look at the meaning of thinking can.

Multi-table query, which is to 2 tables with connections, use the associated fields, foreign keys. After multiple tables are connected, it becomes a "Cartesian set". For example, the department table is a 2 column field 2 row value. And the employee table is 4 column field 3 row value, they have a foreign key association, the associated table is a 6 column field 6 row values after an association table. This table is called the "Cartesian set". Want to Know "flute card called set" can Baidu check.

But this "Cartesian set" table is an invalid table, just a combination of two tables. Two table associations, the number of columns added, the number of rows multiplied, get 6 columns 6 rows, we need to use the Where condition statement to the two tables associated with the value of the field to extract, this is the associated table we want. equivalent Connection example: Query the employee's information, request to display: Employee number, name, monthly salary, department name (so-called equivalent connection, is the condition with = number to determine the connection) from the table can be seen, the two Table association field DEPTNO

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

non-equivalent connectionHere is the range of wages. s table has two fields, which is taken is greater than the S.lo lower bound, less than the S.hi upper limit

SELECT E.empno,e.ename,e.ename,e.sal,s.grade from EMP E, Salgrade S WHERE e.sal BETWEEN S.lo and S.hi

Outer JoinsAn outer join is a record of an incorrect connection condition, also included in the final results, if we are using only the equivalent connection, and to query the number of departments, if there is no one department, the equivalent of the link there, it will not show, but we want to show the number of all departments, Even if the department has no one to show. This is going to use the outer link. Which primary table you can use, all field values in the primary table are displayed, and the values of the fields in the child table will only be displayed in the same case as the primary table, and will be removed. This is a bit abstract, the outer join is divided into two parts, it depends on which table you want to take the main. Left OUTER join: When the connection condition is not tenable, the table on the left side of the equal sign is still contained;

WHERE E.deptno = D.deptno (+)

Right outer connection, when the connection condition is not tenable, the table on the right side of the equal sign is still included;

WHERE E.deptno (+) = D.deptno

Self-connectedThe self connection is for a table as a two-table connection, by alias, the same table is treated as two tables, the following syntax is in the same table to query the employee's corresponding superior leader's name, in addition to note that the connection is not suitable for operation of large tables, that is, a lot of field values table. We talked about "the Cartesian set", as long as it is a multiple table connection, it will produce a "Cartesian set." That is, if a self connection is just a two-table connection, if there are 3 field values, the number of "Cartesian sets" that he produces is 3 squared, which is 9 count (*), if you have a large table. The resulting "Cartesian set" is too large. However, you can also use "hierarchical query" to solve the above problems

SELECT E.ename employee Name, B.ename boss name from EMP E, emp B WHERE e.mgr = b.empno