SQL Select query principle--Query statement execution principle < go >

1. Single-Table query: Filter the records in the table according to where conditions, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select to return the final result.

1) Simple single-table query

SELECT Field
From table name
WHERE condition Expression

What is the order in which they are executed? The parser first looks at the first word of the statement, and when it finds out that the first word is the SELECT keyword, it jumps to the FROM keyword and then finds the table name from the keyword and loads the table into memory. Then find the WHERE keyword, if not found, return to the Select to find the field resolution, if found where, then analyze the conditions, complete and then return to the Select analysis field. Finally form a virtual table we want.
The WHERE keyword is followed by a conditional expression. If you learn programming languages such as C, you will know that after the conditional expression calculation is complete, there will be a return value, that is, 0 or 0, not 0 is true (true), and 0 is False (false). Similarly, the condition behind the where also has a return value, true or FALSE, to determine that the next hold does not execute the SELECT.

Cases:
SELECT *
From STUDENT
WHERE SNO = ' 1 ';

The parser first finds the keyword Select, jumps to the FROM keyword, imports the student table into memory, finds the first record through the pointer P1, and then finds the conditional expression where the keyword evaluates it, and if it is true then put this record in a virtual table, P1 points to the next record. If it is false then the P1 directly points to the next record without doing anything else. Retrieves the entire table and returns the virtual table to the user.

Besides the exists predicate, the EXISTS predicate is also part of the conditional expression. Of course it also has a return value (TRUE or false).

Cases:
SELECT Sname
From Student
WHERE EXISTS
(SELECT *
From SC
WHERE SC. Sno = Student.sno and SC. Cno = ' 1 ');

This is a nested use of a SQL statement, but it is the same as the execution of the SQL statement described above. Nesting means that when you parse the main SQL statement (the outside select) into the WHERE keyword, it goes into another SQL statement. In other words, the parser first finds the table student and loads the memory, and a pointer (for example, p1) points to the first record in the student table. Then into the where to parse the SQL statements, then the SC table into memory, another pointer (for example, p2) to the first record in the SC table, analysis where the conditional expression, followed by analysis, and finally analyzed a virtual table 2, it becomes

SELECT Sname
From Student
WHERE EXISTS Virtual Table 2

If the virtual table is an empty table, EXISTS virtual table 2 is false, does not return to select, and P1 points to the next record. If the virtual table 2 is not empty and there is a record, then exists virtual table 2 is true, return to select and add the record pointed to by P1 to the virtual table 1 of the main SQL statement. (This is why a nested SQL statement is followed by a general *, because it exists returns only true or FALSE, the name of the field is meaningless, with a * on the line, of course, nothing else is wrong.) ）

Note that although the nested SQL statement is finished parsing, but the main SQL statement is executed only once, that is, P1 points to the first record of student, P1 also point to the student table of the next record and analysis, and then into the nested SQL statement, as described above analysis. When P1 is also at the end of the student table, the entire SQL statement ends. Returns a column of virtual table 1Sname.

Nesting is like:

for (int i = 0,i < N, ++i)
for (int j = 0, J < N, ++j)

2, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then the records of the intermediate table are filtered based on the Where condition and the query results are returned based on the column specified by select.

3. Multi-Table Connection query: First and second table according to two table connection query, and then use the query results and the third table for the connection query, and so on, until all the tables are connected, and eventually form an intermediate result table, and then filter the records of the intermediate table according to the Where condition, and returns the result of the query based on the column specified by select.

4. Different types of connection queries when to use

Check that two tables correlate columns with equal data within the connection.
The left Table connection column is a subset of the right table, with a right outer join.
The connection column of the right table is a subset of the left table, with a left outer join.
The connection columns of the left table and the right table have overlapping with each other but are not subsets of each other.
Use a federated query when seeking differential operations.
When querying multiple tables, these different connection types can be written to a piece.
On only the connection operation, where only the records of the intermediate table are filtered

Transfer from http://blog.csdn.net/chenjian198819/article/details/6794316

SQL Select query principle--Query statement execution principle < go >