Java Web----Multi-table Query

There are several types of multi-table queries:

Merging result sets

Connection Query

Internal connection

External connection

Left outer connection

Right outer connection

Full external connection (MySQL not supported)

Natural connection

Sub-query

1 Merging result sets

1. Function: Merging the result set is the result of merging the query results of two SELECT statements together!

2. There are two ways to combine result sets:

UNION: Remove duplicate records, for example: SELECT * FROMt1 UNION select * from T2;

UNION all: Do not remove duplicate records, for example: select* from T1 UNION all SELECT * from T2.

3. Requirements: Two results merged: number of columns, column type must be the same.

2 Connection Query

Connection query is to find out the product of multiple tables, such as T1 connection T2, then the result of the query is T1*T2.

The connection query produces a Cartesian product, assuming the set a={a,b}, set b={0,1,2}, then the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases.

So many table queries produce such results is not what we want, then how to remove duplicate, unwanted records, of course, through the conditional filtering. There is an association relationship between the multiple tables that are usually queried, and the Cartesian product is removed by the association relationship.

Can you imagine the results of an EMP and a Dept Table connection query? EMP Total 14 rows of records, Dept table total 4 rows of records, then the connection after the query results are 56 rows of records.

You just want to query the EMP table at the same time, each employee's department information displayed, then you need to use the primary foreign key to remove useless information.

Use the primary foreign key relationship as a condition to remove useless information

SELECT * from Emp,dept WHERE Emp.deptno=dept.deptno;

The above query results will query all the columns of the two tables, perhaps you do not need so many columns, then you can specify the column to query.

SELECT emp.ename,emp.sal,emp.comm,dept.dname from emp,dept WHERE Emp.deptno=dept.deptno;

You can also specify an alias for the table, and then use the alias when referencing the column.

SELECT e.ename,e.sal,e.comm,d.dname from EMP as e,dept as Dwhere E.deptno=d.deptno;

2.1 Internal Connection

The connection statement of a polygon is an inner connection, but it is not a query in the SQL standard, it can be understood as a dialect! The SQL standard internal connection is:

SELECT * from EMP e INNER joins dept D on E.deptno=d.deptno;

Internal connection features: The query results must meet the conditions. For example, we insert a record into the EMP table

Where Deptno is 50, and in the Dept table only 10, 20, 30, 40 departments, then the above query results will not appear in the "Zhang San" this record, because it can not meet e.deptno=d.deptno this condition.

2.2 Outer connection (left, right connection)

The characteristics of the outer joins: The result of the query has the possibility of not satisfying the condition.

Left JOIN connection:

SELECT * from emp E left OUTER joins dept D on E.deptno=d.deptno;

Left join is the first query out the left table (that is, the left table is the main), and then query the right table, the right table to meet the conditions of the display, does not meet the condition of the display null.

So you may not be quite sure, we still use the above example to illustrate. In the EMP table "Zhang San" This record, the department number is 50, and the Dept table does not have a department number 50 records, so "Zhang San" This record, can not meet e.deptno=d.deptno this condition. But in the left connection, because the EMP table is the left table, so the records in the left table will be queried, that is, "Zhang San" This record will also be detected, but the corresponding right table section shows null.

Connection Query Experience :

Connection is not limited to two tables, the connection query can also be three, four, or even n table connection query. It is not usually possible to connect queries that require the entire Cartesian product, but only a subset of them, so you need to use conditions to remove unwanted records. This condition is most often removed using the primary foreign key relationship.

Two table connection query must have a primary foreign key relationship, three table connection query there must be two primary foreign key relationship, so in everyone is not very familiar with the connection query, the first to learn to remove the useless Cartesian product, then is the main foreign key relationship as a condition to deal with. If two tables are queried, then there is at least one primary foreign key condition, and three table connections have at least two primary foreign key conditions.

3 Natural Connections

As you all know, a connection query produces a useless Cartesian product, and we usually use the primary foreign key relationship equation to remove it. And the natural connection does not require you to give the primary foreign key equation, it will automatically find this type:

Two connected tables with names and type-complete columns as conditions, such as EMP and dept tables, have DEPTNO columns and are of the same type, so they will be found by natural connections!

Of course there are other ways to find the natural connection, but there are other ways that can be problematic!

SELECT * from EMP NATURAL JOIN dept; SELECT * from EMP NATURAL left JOIN dept; SELECT * from EMP NATURAL right JOIN dept;

4 Sub-query

A subquery is a nested query, that is, select contains a SELECT, if there are two or more than two select in a statement, then it is a subquery statement.

Where the subquery appears:

• Where, as part of the condition;
• From after, as a table to be queried;

You can also use the following keywords when a subquery appears as a condition after the Where:

• Any
• All

The form of a subquery result set:

• Single row (for conditions)
• Single-row multi-column (for conditions)
• Multiline single row (for conditions)
• Multi-row multiple columns (for tables)

Practice:

1. Employees with wages higher than ganning.

Analysis:

Query conditions: wages > Ganning wages, where ganning wages require a subquery.

The first step: query ganning Wages

SELECT sal from emp WHERE ename= ' ganning '

Step two: Query employees who are above ganning wages

SELECT * from emp WHERE sal > (${First step})

Results:

SELECT * from emp where sal > (SELECT sal from emp where ename= ' ganning ')

• Sub-query as condition
• subquery as a single row

2. Employee information for salaries above 30 department owners

Analysis:

Search conditions: Wages are higher than the 30-door owner's wages, of which 30 are the sub-query of the owner's wages. Above all you need to use the ALL keyword.

Step one: Check the 30-door owner's salary

SELECT sal from EMP WHERE deptno=30;

Step Two: Query employee information that is higher than the 30-door owner's salary

SELECT * from emp WHERE sal > All (${first step})

Results:

SELECT * from emp where sal > all [Q1] (select Sal from emp where deptno=30)

• Sub-query as condition
• A subquery is a multiline column (the all or any keyword can be used when the subquery result set is a multi-row column)

3. Query work and payroll exactly the same employee information as Yan Tianjong

Analysis:

Query conditions: Work and wages are exactly the same as Yan Tianjong, which is a subquery

The first step: to find out Yan Tianjong work and wages

SELECT job,sal from emp WHERE ename= ' Yintian positive '

The second step: to find out with Yintian is working and pay the same person

SELECT * from emp WHERE (job,sal) in (${first step})

Results:

SELECT * from emp where (job,sal) in (select Job,sal from emp where ename= ' Yintian positive ')

• Sub-query as condition
• Subquery as single-row multi-column

4. Query Employee number 1006 Employee name, employee salary, department name, department address

Analysis:

Query columns: Employee name, employee salary, department name, department address

Query table: EMP and dept, analyzed, no external connection required (external connection characteristics: one row (or some rows) record will appear half of the value, half of the null value)

Condition: Employee number is 1006

The first step: To remove the multi-table, only one table, here to remove the Department table, only check the employee table

SELECT ename, sal from emp e WHERE empno=1006

Step two: Let the first step and dept do inner JOIN query, add main foreign key condition to remove useless cartesian product

SELECT E.ename, E.sal, D.dname, D.loc From EMP E, Dept D WHERE E.deptno=d.deptno and empno=1006

The Dept table in the second step represents a complete table for all the columns of all rows, where you can replace dept with all rows, but only the dname and LOC column tables, which require subqueries.

The third step: Query the Dept table in the Dname and loc two columns, because DEPTNO will be used as a condition to remove the useless Cartesian product, so you need to query it.

SELECT Dname,loc,deptno from Dept;

Fourth Step: Replace the Dept in the second step

SELECT E.ename, E.sal, D.dname, D.loc From EMP E, (SELECT dname,loc,deptno from dept) d WHERE E.deptno=d.deptno and e.empno=1006

• Sub-query as table
• Subquery as multi-row multi-column

Java Web----Multi-table Query