连接表

1SELECT 
2    c.CustomerID,
3    c.CustomerName,
4    o.OrderID,
5    o.OrderDate
6FROM Customers AS c
7INNER JOIN Orders AS o
8    ON c.CustomerID = o.CustomerID;

1-- 选择所有客户及其订单日期，包括没有订单的客户
2SELECT
3    c.CustomerName,
4    o.OrderDate
5FROM
6    Customers AS c
7LEFT JOIN
8    Orders AS o ON c.CustomerID = o.CustomerID
9ORDER BY
10    o.OrderDate DESC;

1-- 选择所有订单以及下单客户的姓名
2SELECT
3    c.CustomerName,
4    o.OrderDate
5FROM
6    Orders AS o
7RIGHT JOIN
8    Customers AS c ON o.CustomerID = c.CustomerID;

1-- 模拟 FULL OUTER JOIN 以显示所有客户和所有订单
2-- （此语法在 MySQL 中有效）
3SELECT c.CustomerName, o.OrderDate
4FROM Customers AS c
5LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID
6
7UNION
8
9SELECT c.CustomerName, o.OrderDate
10FROM Customers AS c
11RIGHT JOIN Orders AS o ON c.CustomerID = o.CustomerID;

1-- 查找推荐客户的姓名以及他们推荐的客户
2SELECT
3    referrer.CustomerName AS ReferrerName,
4    referred.CustomerName AS ReferredCustomerName
5FROM
6    Customers AS referrer
7INNER JOIN
8    Customers AS referred ON referrer.CustomerID = referred.ReferralID;

1-- 获取客户姓名、他们订购的产品以及每种产品的数量
2SELECT
3    c.CustomerName,
4    p.ProductName,
5    od.Quantity
6FROM
7    Customers AS c
8JOIN
9    Orders AS o ON c.CustomerID = o.CustomerID
10JOIN
11    Order_Details AS od ON o.OrderID = od.OrderID
12JOIN
13    Products AS p ON od.ProductID = p.ProductID
14ORDER BY
15    c.CustomerName;