交叉联接
语法
SELECT
C.custid,
E.empid
FROM Sales.Customers AS C
CROSS JOIN HR.Employees AS E
--ANSI SQL-92语法
SELECT
C.custid,
E.empid
FROM Sales.Customers AS C,HR.Employees AS E
--ANSI SQL-89语法
--两者之间没有逻辑或性能差异，但建议使用ANSI SQL-92语法
自交叉联接
SELECT
E1.empid,E1.firstname,E1.lastname,
E2.empid,E2.firstname,E2.lastname
FROM HR.Employees AS E1
CROSS JOIN HR.Employees AS E2
生成数字表
SELECT
1 + D1.digit*100 + D2.digit + D3.digit * 10 AS N
FROM dbo.Digits AS D1
CROSS JOIN dbo.Digits AS D2
CROSS JOIN dbo.Digits AS D3
内部联接
--ANSI SQL-92
SELECT
E.empid,E.firstname,E.lastname,
O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
ON E.empid = O.empid
--内部联接，应在表名称之间指定INNER JOIN关键词，由于内部联接是默认是内部联接所以INNER可以省略
--ANSI SQL-89
SELECT
E.empid,E.firstname,E.lastname,
O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
WHERE E.empid = O.empid
内部联接安全性
推荐使用ANSI-SQL 92
更多联接示例
复合联接
SELECT
OD.orderid,OD.productid,OD.qty,
ODA.dt,ODA.loginname,ODA.oldval,ODA.newval
FROM Sales.OrderDetails AS OD
JOIN Sales.OrderDetailsAudit AS ODA
ON OD.orderid = ODA.orderid
AND OD.productid = ODA.productid
WHERE ODA.columnname = N'qty';
不等联接
--不等联接就是联接条件涉及除等号以外的任何运算符
SELECT
E1.empid,E1.firstname,E1.lastname,
E2.empid,E2.firstname,E2.lastname
FROM HR.Employees AS E1
JOIN HR.Employees AS E2
ON E1.empid < E2.empid
多联接查询
--下面查询联接Customer和Orders表以匹配客户的订单
--然后以第一个联接的结果和OrderDetails表以匹配订单的订单行
SELECT
C.custid,C.companyname,O.orderid,
OD.productid,OD.qty
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON C.custid = O.custid
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
外部联接
LEFT OUTER JOIN在两张表进行连接查询时，会返回左表所有的行，即使右表中没有匹配的记录，右表符合ON或者WHERE下的条件。
RIGHT OUTER JOIN在两张表进行连接查询时，会返回右表所有的行，即使左表中没有匹配的记录，左表符合ON或者WHERE下的条件。
FULL OUTER JOIN两张表同时符合ON和WHERE下的条件。
SELECT
C.custid,C.companyname,
O.custid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
第一阶段：FROM
第二阶段：ON
第三阶段：... OUTER JOIN
第四阶段：WHERE
练习
--3.6.1
SELECT
empid,firstname,lastname,n
FROM HR.Employees JOIN dbo.Nums
ON n <= 5
--3.6.3
SELECT
O.custid,COUNT(DISTINCT OD.orderid) AS numbers,SUM(qty) AS totalqty
FROM Sales.Customers AS C JOIN Sales.Orders AS O
ON C.country = 'USA' AND C.custid = O.custid
JOIN Sales.OrderDetails OD ON O.orderid = OD.orderid
GROUP BY O.custid
--3.6.4
SELECT
C.custid,companyname,orderid,orderdate
FROM Sales.Customers AS C LEFT JOIN Sales.Orders AS D
ON C.custid = D.custid
--3.6.5
SELECT
C.custid,companyname,orderid,orderdate
FROM Sales.Customers AS C LEFT JOIN Sales.Orders AS D
ON C.custid = D.custid
WHERE orderid IS NULL
--3.6.6
SELECT
*
FROM Sales.Customers AS C JOIN Sales.Orders AS O
ON orderdate = '20070212' AND C.custid = O.custid;
--3.6.7
SELECT
C.custid,companyname,
CASE WHEN o.orderid IS NULL THEN 'NO'
ELSE 'YES'
END AS HasORderOn20070212
FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O
ON O.custid = C.custid AND orderdate = '20070212';
