SQL-Query parsing steps

First of all, from the simplest SQL to analyze the SQL execution steps, why understand the execution steps, in fact, after the JOIN of the on condition and the WHERE condition is easy to confuse the relationship.

Whether to execute on first or execute WHERE, to a large extent, determines whether the result set of SQL is correct or not.

CREATE TABLE Customers

(

CustomerID CHAR (5) Not NULL PRIMARY KEY,

City VARCHAR (Ten) not NULL

);

CREATE TABLE Orders

(

OrderID INT not NULL PRIMARY KEY,

CustomerID CHAR (5) NULL REFERENCES Customers (CustomerID)

);

INSERT into Customers VALUES (' Fissa ', ' Madrid ');

INSERT into Customers VALUES (' Frndo ', ' Madrid ');

INSERT into Customers VALUES (' Krlos ', ' Madrid ');

INSERT into Customers VALUES (' mrphs ', ' Zion ');

INSERT into Orders VALUES (1, ' Frndo ');

INSERT into Orders VALUES (2, ' Frndo ');

INSERT into Orders VALUES (3, ' Krlos ');

INSERT into Orders VALUES (4, ' Krlos ');

INSERT into Orders VALUES (5, ' Krlos ');

INSERT into Orders VALUES (6, ' mrphs ');

INSERT into Orders VALUES (7,null);

Try to see how the above two statements are different and you will find interesting phenomena.

SELECT C.customerid,count (O.orderid) as Numorders

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

WHERE c.city= ' Madrid '

GROUP by C.customerid

Having COUNT (O.orderid) <3

ORDER by Numorders;

SELECT C.customerid,count (O.orderid) as Numorders

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

and c.city= ' Madrid '

GROUP by C.customerid

Having COUNT (O.orderid) <3

ORDER by Numorders;

--STEP1, a Cartesian product is first performed on the table behind the from, generating a virtual table STEP1

With STEP1

As

(

SELECT c.customerid c_customerid,c.city C_city,o.orderid o_orderid,o.customerid O_customerid

From Customers c,orders O

)

SELECT * from STEP1

--STEP2, the conditions in the ON statement are applied again, and if there is no outside association, the on and where are actually not the same, generating a virtual table STEP2

With STEP2

As

(

SELECT c.customerid c_customerid,c.city C_city,o.orderid o_orderid,o.customerid O_customerid

From Customers C

JOIN Orders O

On C.customerid=o.customerid

)

SELECT * from STEP2

--STEP3, if a OUTER JOIN is specified, SQL automatically adds the unmatched rows from the STEP2 table to the STEP3 as an outer row, where Customerid=fissa,city=madrid is found, a user with no orders but a related name

With STEP3

As

(

SELECT c.customerid c_customerid,c.city C_city,o.orderid o_orderid,o.customerid O_customerid

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

)

SELECT * from STEP3

--STEP4, applying a WHERE condition to filter records that do not meet the criteria

As

(

SELECT c.customerid c_customerid,c.city C_city,o.orderid o_orderid,o.customerid O_customerid

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

WHERE c.city= ' Madrid '

)

SELECT * from STEP4

--STEP5, grouping the result sets above

With STEP5

As

(

SELECT C.customerid,count (O.orderid) as Numorders

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

WHERE c.city= ' Madrid '

GROUP by C.customerid

)

SELECT * from STEP5

--STEP6, dealing with statements such as CUBE, ROLLUP, no such requirement here

Slightly

SELECT * from STEP6

--STEP7 handles having a filter, which is somewhat similar to the WHERE condition

With STEP7

As

(

SELECT C.customerid,count (O.orderid) as Numorders

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

WHERE c.city= ' Madrid '

GROUP by C.customerid

Having COUNT (O.orderid) <3

)

SELECT * from STEP7

--STEP8, processes the SELECT list, where the alias conversion converts COUNT (O.orderid) to Numorders

Slightly

SELECT * from STEP8

--STEP9, apply the DISTINCT statement, no such requirement here

Slightly

SELECT * from STEP9

--STEP10, applying the ORDER BY statement

SELECT C.customerid,count (O.orderid) as Numorders

From Customers C

Left OUTER JOIN Orders O

On C.customerid=o.customerid

WHERE c.city= ' Madrid '

GROUP by C.customerid

Having COUNT (O.orderid) <3

ORDER by Numorders

--STEP11, perform the TOP option, no such requirement here

Slightly

SELECT * from STEP11

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1622857

. SQL--Query parsing steps