12. SQL -- 查询解析步骤
先从最简单的 SQL 入手来分析一下 SQL 的执行步骤，为什么了解执行步骤，其实与 JOIN 后的 ON 条件和 WHERE 条件容易混淆有关系。是先执行 ON 还是先执行 WHERE ，很大程度上会决定 SQL 的结果集正确与否。
CREATE TABLE Customers
(
CustomerIDCHAR(5) NOT NULL PRIMARY KEY,
City VARCHAR(10) NOT NULL
);
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
CustomerIDCHAR(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);
试看看以上两个语句有什么不同，你就会发现很有趣的现象。
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)
页:
[1]