深入了解子查询

SELECT DISTINCT soh.OrderDate,sod.ProductID
FROM Sales.SalesOrderHeader soh
LEFT JOIN sales.SalesOrderDetail sod 
    ON soh.SalesOrderID =sod.SalesOrderID
WHERE soh.OrderDate =(
    SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)

--子查询
SELECT e.BusinessEntityID,FirstName,LastName
FROM HumanResources.Employee e
JOIN Person.Person pp
    ON e.BusinessEntityID=pp.BusinessEntityID
WHERE e.BusinessEntityID IN 
(SELECT DISTINCT BusinessEntityID FROM HumanResources.JobCandidate)

--用连接代替子查询 JOIN 默认为INNER JOIN
SELECT e.BusinessEntityID,FirstName,LastName
FROM HumanResources.Employee e
JOIN Person.Person pp
    ON e.BusinessEntityID=pp.BusinessEntityID
JOIN HumanResources.JobCandidate jc
    ON jc.BusinessEntityID=e.BusinessEntityID

-- 与NULL值比较总会得到NULL
SELECT e.BusinessEntityID,FirstName,LastName
FROM HumanResources.Employee e
JOIN Person.Person pp
    ON e.BusinessEntityID=pp.BusinessEntityID
WHERE e.BusinessEntityID NOT IN 
(SELECT DISTINCT BusinessEntityID FROM HumanResources.JobCandidate 
    WHERE BusinessEntityID IS NOT NULL)

-- WHERE 子句中的相关子查询
--查询每一位客户在系统中的第一份订单的OrderID, OrderDate
/* 使用外面查询中的CustomerID，进行内部查询，得出一个客户的最早订单日期，返回给外部查询*/

SELECT o1.CustomerID,o1.SalesOrderID,o1.OrderDate
FROM Sales.SalesOrderHeader o1
WHERE o1.OrderDate=(SELECT MIN(o2.OrderDate) 
                    FROM Sales.SalesOrderHeader o2
                    WHERE o2.CustomerID=o1.CustomerID ) -- 内部查询对外部的引用
ORDER BY o1.CustomerID   

-- SELECT 子句中的相关子查询
SELECT pp.FirstName,pp.LastName,
    (SELECT MIN(OrderDate)
        FROM Sales.SalesOrderHeader ord_h
        WHERE ord_h.CustomerID=c.CustomerID        
    ) AS OrderDate
FROM Person.Person pp
JOIN Sales.Customer c
    ON pp.BusinessEntityID=c.PersonID
    
-- 派生表 派生表并不能解决所有问题, 结果相当庞大而且有大量记录要联结时，可能要考虑使用临时表并在其上构建索引（派生表没有索引）
-- 查询要求， 查询订过 迷你水泵 又订过 AWC Logo Cap 帽的所有客户

SELECT DISTINCT    -- DISTINCT 去除重复订购的人
        PP.FirstName ,
        PP.LastName
FROM    Person.Person AS pp
        JOIN ( SELECT   sc.PersonID
               FROM     Sales.Customer sc
                        JOIN Sales.SalesOrderHeader AS ord_h ON sc.CustomerID = ord_h.CustomerID
                        JOIN Sales.SalesOrderDetail AS ord_d ON ord_d.SalesOrderID = ord_h.SalesOrderID
                        JOIN Production.Product AS prd ON prd.ProductID = ord_d.ProductID
               WHERE    prd.Name = 'Minipump'
             ) pumps ON pp.BusinessEntityID = pumps.PersonID
        JOIN ( SELECT   sc.PersonID
               FROM     Sales.Customer sc
                        JOIN Sales.SalesOrderHeader AS ord_h ON sc.CustomerID = ord_h.CustomerID
                        JOIN Sales.SalesOrderDetail AS ord_d ON ord_d.SalesOrderID = ord_h.SalesOrderID
                        JOIN Production.Product AS prd ON prd.ProductID = ord_d.ProductID
               WHERE    prd.Name = 'AWC Logo Cap'
             ) caps ON pp.BusinessEntityID = caps.PersonID
             
-- Exists 运算符             
SELECT  e.BusinessEntityID ,
        FirstName ,
        LastName
FROM    HumanResources.Employee e
        JOIN Person.Person pp ON e.BusinessEntityID = pp.BusinessEntityID
WHERE   EXISTS ( SELECT BusinessEntityID
                 FROM   HumanResources.JobCandidate jc
                 WHERE  e.BusinessEntityID = jc.BusinessEntityID )

 

-- 以上摘自SQL Server 2008 高级程序设计