SQL SERVER 2005 高级查询（子查询查询）

--SQL高级程序设计：子查询
use AdventureWorks
GO
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL;
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
 ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN (SELECT DISTINCT EmployeeID from HumanResources.JobCandidate
      WHERE EmployeeID IS NOT NULL)
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
 ON e.ContactID = c.ContactID
WHERE e.EmployeeID NOT IN (
       SELECT DISTINCT EmployeeID
       from HumanResources.JobCandidate
       WHERE EmployeeID IS NOT NULL
       )
SELECT CustomerID,MIN((OrderDate)) AS OrderDate
INTO #MinOrderDates
from Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID
SELECT o.CustomerID,o.SalesOrderID,o.OrderDate
from Sales.SalesOrderHeader o
inner join #MinOrderDates t
 on o.CustomerID = t.CustomerID
 and o.OrderDate = t.OrderDate
order by o.CustomerID
DROP TABLE #MinOrderHeaders
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 CustomerID
SELECT c.LastName,ISNULL(CAST((SELECT MIN(OrderDate) from Sales.SalesOrderHeader o
     WHERE o.ContactID = c.ContactID) AS VARCHAR),'NEVER RECORD') AS "Order Date"
from Person.Contact c
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
 ON e.ContactID = c.ContactID
WHERE EXISTS(SELECT DISTI