第三章 联接
3.1交叉联接
交叉联接是最简单的联接类型。
交叉联接仅执行一个逻辑查询处理阶段——笛卡尔乘积
将一个输入表的每一行与另一个表的所有行匹配
SQL Server支持交叉联接的两种标准语法:
ANSI SQL-92和ANSI SQL-89语法,
建议使用ANSI SQL-92语法
3.1.1 ANSI SQL-92语法
SELECT C.custid, E.empid
FROM Sales.Customers AS C
CROSS JOIN HR.Employees AS E;
使用关键字CROSS JOIN
可以对表使用别名,如果未使用别名,需要使用完整的源表名称作为前缀。
3.1.2 ANSI SQL-89语法
SELECT C.custid, E.empid
FROM Sales.Customers AS C, HR.Employees AS E;
简单地在表名称之间制定一个逗号
两种语法之间没有逻辑或性能差异。
3.1.3 自交叉联接
可以联接同一个表的多个实例,此功能称为自联接,并且被所有基本联接类型支持(交叉联接、内部联接和外部联接)。
SELECT
E1.empid, E1.firstname, E1.lastname,
E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
CROSS JOIN HR.Employees AS E2;
在自联接中,为表指定别名不是可选项。没有表别名,联接结果中的所有列名都将不明确。
3.1.4 生成数字表
交叉联接的一种用途是,生成一个整数数列结果集。
先创建一个名为Digits的表,其中包含名为digit的列,并且以数字0~9填充该表。
CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.Digits(digit)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT digit FROM dbo.Digits;
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM dbo.Digits AS D1
CROSS JOIN dbo.Digits AS D2
CROSS JOIN dbo.Digits AS D3
ORDER BY n;
SQL Server 2022支持新语法:
SELECT * FROM GENERATE_SERIES(1, 1000);
3.2 内部联接
内部联接应用两个逻辑查询处理阶段——首先对作为交叉联接的两个输入表应用一个笛卡尔乘积,然后按指定的谓词筛选行。
也有两个标准语法:ANSI SQL-92和ANSI SQL-89
3.2.1 ANSI SQL-92语法
在表名称之间指定INNER JOIN关键字。
由于内部联接是默认联接,所以INNER关键字是可选的,可以仅指定JOIN关键字。指定的谓词用用于在名为ON的特定子句中筛选行,该谓词也称为联接条件。
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
INNER JOIN Sales.Orders AS O
ON E.empid = O.empid;
对大多数人来说,思考内部联接的最简单方式是,内部联接匹配每个雇员行到与其具有相同雇员ID的所有订单行,这是联接的一个简单思考方式。
联接的更正式思考方式是基于关系代数的,联接首先执行两个表的笛卡尔乘积,然后基于谓词E.empid = O.empid筛选行。
3.2.2 ANSI SQL-89语法
像交叉联接一样在表名称之间指定一个逗号,并在查询的WHERE子句中指定联接条件。
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O
WHERE E.empid = O.empid;
注意,ANSI SQL-89语法没有ON子句。
3.2.3 内部联接安全性
建议使用ANSI SQL-92联接语法,因为它在几个方面都是安全的。
假设打算写一个内部联接查询,并且失误忘记了指定联接条件。对于ANSI SQL-92语法,查询将无效,解析器会生成一个错误。
如果使用ANSI SQL-89语法时忘记指定联接条件,你会得到一个执行交叉联接的有效查询。因为查询不会失败,一段时间内可能不会注意到逻辑错误,你的应用程序哦用户可能最终使用了错误结果。对于简单的查询,程序员不太坑你会忘记指定联接条件,然而,多数生产查询更为复杂并具有多个表、筛选和其他查询元素,在这些情况下,忘记指定联接条件的可能性就增加了。
3.3.1 复合联接
复合联接是谓词涉及每侧多个属性的简单联接。
当需要联接两个基于主外键关系并且是复合关系(即基于多个属性)的表时,通常需要复合联接。
FROM dbo.Table1 AS T1
INNER JOIN dbo.Table2 AS T2
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
3.3.2 不等联接
当联接条件仅涉及等号运算时,称为相等联接。
当联接条件涉及除等号之外的任何运算符时,称为不等联接。
下面是一个不等联接的示例,此查询联接Employees表的两个实例,生成唯一的雇员对。
SELECT
E1.empid, E1.firstname, E1.lastname,
E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
INNER JOIN HR.Employees AS E2
ON E1.empid < E2.empid;
3.3.3 多联接查询
当FROM子句中出现多个表运算符时,表运算符从左到右进行逻辑处理。也就是说,第一个表运算符的结果表将被视为第二个表运算符的左侧输入,第二个表运算符的结果将被视为第三个表运算符的左侧输入,以此类推。
SELECT
C.custid, C.companyname, O.orderid,
OD.productid, OD.qty
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
INNER JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid;
3.4.1 外部联接的基础知识
外部联接是在ANSI SQL-92中引入的,并且与内部联接和交叉联接不同,它只有一个标准语法——在表名之间制定一个JOIN关键字,并且在ON子句中指定联接条件。外部联接应用内部联接锁应用的两个逻辑处理阶段,再加上一个叫做“添加外部行”的第三个阶段,此阶段是此联接类型唯一具有的阶段。
在外部联接中,需要在表名之间使用关键字LEFT OUTER JOIN、RIGHT OUTER JOIN或FULL OUTER JOIN标记一个表为“保留表”。
OUTER关键字是可选的。LEFT关键字表示保留左侧表中的行,RIGHT表示保留右侧表中的行,FULL表示左侧和右侧表中的行都保留。
3.4.2 超越外部联接基础知识(可选内容)
1 包含缺失值
SELECT DATEADD(day, Nums.n - 1, CAST('20140101' AS DATE)) AS orderdate,
O.orderid, O.custid, O.empid
FROM dbo.Nums
LEFT OUTER JOIN Sales.Orders AS O
ON DATEADD(day, Nums.n - 1, CAST('20140101' AS DATE)) = O.orderdate
WHERE Nums.n <= DATEDIFF(day, '20140101', '20161231') + 1
ORDER BY orderdate;
2 从外部联接的非保留侧筛选属性
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.orderdate >= '20160101';
3 在多联接查询中使用外部联接
4 在外部联接使用COUNT聚合
SELECT C.custid, COUNT(*) AS numorders
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid;

章节代码

---------------------------------------------------------------------
-- Microsoft SQL Server 2012 T-SQL Fundamentals
-- Chapter 03 - Joins
-- ?Itzik Ben-Gan 
---------------------------------------------------------------------

---------------------------------------------------------------------
-- CROSS Joins
---------------------------------------------------------------------

-- ANSI SQL-92
USE TSQL2012;

SELECT C.custid, E.empid
FROM Sales.Customers AS C
  CROSS JOIN HR.Employees AS E;

-- ANSI SQL-89
SELECT C.custid, E.empid
FROM Sales.Customers AS C, HR.Employees AS E;

-- Self Cross-Join
SELECT
  E1.empid, E1.firstname, E1.lastname,
  E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1 
  CROSS JOIN HR.Employees AS E2;
GO

-- All numbers from 1 - 1000

-- Auxiliary table of digits
USE TSQL2012;
IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL DROP TABLE dbo.Digits;
CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);

INSERT INTO dbo.Digits(digit)
  VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT digit FROM dbo.Digits;
GO

-- All numbers from 1 - 1000
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM         dbo.Digits AS D1
  CROSS JOIN dbo.Digits AS D2
  CROSS JOIN dbo.Digits AS D3
ORDER BY n;

---------------------------------------------------------------------
-- INNER Joins
---------------------------------------------------------------------

-- ANSI SQL-92
USE TSQL2012;

SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
  JOIN Sales.Orders AS O
    ON E.empid = O.empid;

-- ANSI SQL-89
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O
WHERE E.empid = O.empid;
GO

-- Inner Join Safety
/*
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
  JOIN Sales.Orders AS O;
GO
*/

SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O;
GO

---------------------------------------------------------------------
-- Further Join Examples
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Composite Joins
---------------------------------------------------------------------

-- Audit table for updates against OrderDetails
USE TSQL2012;
IF OBJECT_ID('Sales.OrderDetailsAudit', 'U') IS NOT NULL
  DROP TABLE Sales.OrderDetailsAudit;
CREATE TABLE Sales.OrderDetailsAudit
(
  lsn        INT NOT NULL IDENTITY,
  orderid    INT NOT NULL,
  productid  INT NOT NULL,
  dt         DATETIME NOT NULL,
  loginname  sysname NOT NULL,
  columnname sysname NOT NULL,
  oldval     SQL_VARIANT,
  newval     SQL_VARIANT,
  CONSTRAINT PK_OrderDetailsAudit PRIMARY KEY(lsn),
  CONSTRAINT FK_OrderDetailsAudit_OrderDetails
    FOREIGN KEY(orderid, productid)
    REFERENCES Sales.OrderDetails(orderid, productid)
);

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';

---------------------------------------------------------------------
-- Non-Equi Joins
---------------------------------------------------------------------

-- Unique pairs of employees
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;

---------------------------------------------------------------------
-- Multi-Join Queries
---------------------------------------------------------------------

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;

---------------------------------------------------------------------
-- Fundamentals of Outer Joins 
---------------------------------------------------------------------

-- Customers and their orders, including customers with no orders
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid;

-- Customers with no orders
SELECT C.custid, C.companyname
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE O.orderid IS NULL;
GO

---------------------------------------------------------------------
-- Beyond the Fundamentals of Outer Joins
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Including Missing Values
---------------------------------------------------------------------

SELECT DATEADD(day, n-1, '20060101') AS orderdate
FROM dbo.Nums
WHERE n <= DATEDIFF(day, '20060101', '20081231') + 1
ORDER BY orderdate;

SELECT DATEADD(day, Nums.n - 1, '20060101') AS orderdate,
  O.orderid, O.custid, O.empid
FROM dbo.Nums
  LEFT OUTER JOIN Sales.Orders AS O
    ON DATEADD(day, Nums.n - 1, '20060101') = O.orderdate
WHERE Nums.n <= DATEDIFF(day, '20060101', '20081231') + 1
ORDER BY orderdate;

---------------------------------------------------------------------
-- Filtering Attributes from Non-Preserved Side of Outer Join
---------------------------------------------------------------------

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE O.orderdate >= '20070101';

---------------------------------------------------------------------
-- Using Outer Joins in a Multi-Join Query
---------------------------------------------------------------------

SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
  JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid;

-- Option 1: use outer join all along
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
  LEFT OUTER JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid;

-- Option 2: change join order
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Orders AS O
  JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid
  RIGHT OUTER JOIN Sales.Customers AS C
     ON O.custid = C.custid;

-- Option 3: use parentheses
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
  LEFT OUTER JOIN
      (Sales.Orders AS O
         JOIN Sales.OrderDetails AS OD
           ON O.orderid = OD.orderid)
    ON C.custid = O.custid;

---------------------------------------------------------------------
-- Using the COUNT Aggregate with Outer Joins
---------------------------------------------------------------------

SELECT C.custid, COUNT(*) AS numorders
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid;

SELECT C.custid, COUNT(O.orderid) AS numorders
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid; 

练习代码

---------------------------------------------------------------------
-- Microsoft SQL Server 2012 T-SQL Fundamentals
-- Chapter 03 - Joins
-- Solutions
-- ?Itzik Ben-Gan 
---------------------------------------------------------------------

-- 1 
-- 1-1 
-- Write a query that generates 5 copies out of each employee row
-- Tables involved: TSQL2012 database, Employees and Nums tables

--Desired output
empid       firstname  lastname             n
----------- ---------- -------------------- -----------
1           Sara       Davis                1
2           Don        Funk                 1
3           Judy       Lew                  1
4           Yael       Peled                1
5           Sven       Buck                 1
6           Paul       Suurs                1
7           Russell    King                 1
8           Maria      Cameron              1
9           Zoya       Dolgopyatova         1
1           Sara       Davis                2
2           Don        Funk                 2
3           Judy       Lew                  2
4           Yael       Peled                2
5           Sven       Buck                 2
6           Paul       Suurs                2
7           Russell    King                 2
8           Maria      Cameron              2
9           Zoya       Dolgopyatova         2
1           Sara       Davis                3
2           Don        Funk                 3
3           Judy       Lew                  3
4           Yael       Peled                3
5           Sven       Buck                 3
6           Paul       Suurs                3
7           Russell    King                 3
8           Maria      Cameron              3
9           Zoya       Dolgopyatova         3
1           Sara       Davis                4
2           Don        Funk                 4
3           Judy       Lew                  4
4           Yael       Peled                4
5           Sven       Buck                 4
6           Paul       Suurs                4
7           Russell    King                 4
8           Maria      Cameron              4
9           Zoya       Dolgopyatova         4
1           Sara       Davis                5
2           Don        Funk                 5
3           Judy       Lew                  5
4           Yael       Peled                5
5           Sven       Buck                 5
6           Paul       Suurs                5
7           Russell    King                 5
8           Maria      Cameron              5
9           Zoya       Dolgopyatova         5

(45 row(s) affected)

-- Solution
SELECT E.empid, E.firstname, E.lastname, N.n
FROM HR.Employees AS E
  CROSS JOIN dbo.Nums AS N 
WHERE N.n <= 5
ORDER BY n, empid;

-- 1-2  (Optional, Advanced)
-- Write a query that returns a row for each employee and day 
-- in the range June 12, 2009 ?June 16 2009.
-- Tables involved: TSQL2012 database, Employees and Nums tables

--Desired output
empid       dt
----------- -----------------------
1           2009-06-12 00:00:00.000
1           2009-06-13 00:00:00.000
1           2009-06-14 00:00:00.000
1           2009-06-15 00:00:00.000
1           2009-06-16 00:00:00.000
2           2009-06-12 00:00:00.000
2           2009-06-13 00:00:00.000
2           2009-06-14 00:00:00.000
2           2009-06-15 00:00:00.000
2           2009-06-16 00:00:00.000
3           2009-06-12 00:00:00.000
3           2009-06-13 00:00:00.000
3           2009-06-14 00:00:00.000
3           2009-06-15 00:00:00.000
3           2009-06-16 00:00:00.000
4           2009-06-12 00:00:00.000
4           2009-06-13 00:00:00.000
4           2009-06-14 00:00:00.000
4           2009-06-15 00:00:00.000
4           2009-06-16 00:00:00.000
5           2009-06-12 00:00:00.000
5           2009-06-13 00:00:00.000
5           2009-06-14 00:00:00.000
5           2009-06-15 00:00:00.000
5           2009-06-16 00:00:00.000
6           2009-06-12 00:00:00.000
6           2009-06-13 00:00:00.000
6           2009-06-14 00:00:00.000
6           2009-06-15 00:00:00.000
6           2009-06-16 00:00:00.000
7           2009-06-12 00:00:00.000
7           2009-06-13 00:00:00.000
7           2009-06-14 00:00:00.000
7           2009-06-15 00:00:00.000
7           2009-06-16 00:00:00.000
8           2009-06-12 00:00:00.000
8           2009-06-13 00:00:00.000
8           2009-06-14 00:00:00.000
8           2009-06-15 00:00:00.000
8           2009-06-16 00:00:00.000
9           2009-06-12 00:00:00.000
9           2009-06-13 00:00:00.000
9           2009-06-14 00:00:00.000
9           2009-06-15 00:00:00.000
9           2009-06-16 00:00:00.000

(45 row(s) affected)

-- Solution
SELECT E.empid,
  DATEADD(day, D.n - 1, '20090612') AS dt
FROM HR.Employees AS E
  CROSS JOIN Nums AS D
WHERE D.n <= DATEDIFF(day, '20090612', '20090616') + 1
ORDER BY empid, dt;

-- 2
-- Return US customers, and for each customer the total number of orders 
-- and total quantities.
-- Tables involved: TSQL2012 database, Customers, Orders and OrderDetails tables

--Desired output
custid      numorders   totalqty
----------- ----------- -----------
32          11          345
36          5           122
43          2           20
45          4           181
48          8           134
55          10          603
65          18          1383
71          31          4958
75          9           327
77          4           46
78          3           59
82          3           89
89          14          1063

(13 row(s) affected)

-- Solution
SELECT C.custid, COUNT(DISTINCT O.orderid) AS numorders, SUM(OD.qty) AS totalqty
FROM Sales.Customers AS C
  JOIN Sales.Orders AS O
    ON O.custid = C.custid
  JOIN Sales.OrderDetails AS OD
    ON OD.orderid = O.orderid
WHERE C.country = N'USA'
GROUP BY C.custid;

-- 3
-- Return customers and their orders including customers who placed no orders
-- Tables involved: TSQL2012 database, Customers and Orders tables

-- Desired output
custid      companyname     orderid     orderdate
----------- --------------- ----------- ------------------------
85          Customer ENQZT  10248       2006-07-04 00:00:00.000
79          Customer FAPSM  10249       2006-07-05 00:00:00.000
34          Customer IBVRG  10250       2006-07-08 00:00:00.000
84          Customer NRCSK  10251       2006-07-08 00:00:00.000
...
73          Customer JMIKW  11074       2008-05-06 00:00:00.000
68          Customer CCKOT  11075       2008-05-06 00:00:00.000
9           Customer RTXGC  11076       2008-05-06 00:00:00.000
65          Customer NYUHS  11077       2008-05-06 00:00:00.000
22          Customer DTDMN  NULL        NULL
57          Customer WVAXS  NULL        NULL

(832 row(s) affected)

-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid;

-- 4
-- Return customers who placed no orders
-- Tables involved: TSQL2012 database, Customers and Orders tables

-- Desired output
custid      companyname
----------- ---------------
22          Customer DTDMN
57          Customer WVAXS

(2 row(s) affected)

-- Solution
SELECT C.custid, C.companyname
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid
WHERE O.orderid IS NULL;

-- 5
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Tables involved: TSQL2012 database, Customers and Orders tables

-- Desired output
custid      companyname     orderid     orderdate
----------- --------------- ----------- -----------------------
66          Customer LHANT  10443       2007-02-12 00:00:00.000
5           Customer HGVLZ  10444       2007-02-12 00:00:00.000

(2 row(s) affected)

-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  JOIN Sales.Orders AS O
    ON O.custid = C.custid
WHERE O.orderdate = '20070212';

-- 6 (Optional, Advanced)
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Also return customers who didn't place orders on Feb 12, 2007 
-- Tables involved: TSQL2012 database, Customers and Orders tables

-- Desired output
custid      companyname     orderid     orderdate
----------- --------------- ----------- -----------------------
72          Customer AHPOP  NULL        NULL
58          Customer AHXHT  NULL        NULL
25          Customer AZJED  NULL        NULL
18          Customer BSVAR  NULL        NULL
91          Customer CCFIZ  NULL        NULL
...
33          Customer FVXPQ  NULL        NULL
53          Customer GCJSG  NULL        NULL
39          Customer GLLAG  NULL        NULL
16          Customer GYBBY  NULL        NULL
4           Customer HFBZG  NULL        NULL
5           Customer HGVLZ  10444       2007-02-12 00:00:00.000
42          Customer IAIJK  NULL        NULL
34          Customer IBVRG  NULL        NULL
63          Customer IRRVL  NULL        NULL
73          Customer JMIKW  NULL        NULL
15          Customer JUWXK  NULL        NULL
...
21          Customer KIDPX  NULL        NULL
30          Customer KSLQF  NULL        NULL
55          Customer KZQZT  NULL        NULL
71          Customer LCOUJ  NULL        NULL
77          Customer LCYBZ  NULL        NULL
66          Customer LHANT  10443       2007-02-12 00:00:00.000
38          Customer LJUCA  NULL        NULL
59          Customer LOLJO  NULL        NULL
36          Customer LVJSO  NULL        NULL
64          Customer LWGMD  NULL        NULL
29          Customer MDLWA  NULL        NULL
...

(91 row(s) affected)

-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid
    AND O.orderdate = '20070212';

-- 7 (Optional, Advanced)
-- Return all customers, and for each return a Yes/No value
-- depending on whether the customer placed an order on Feb 12, 2007
-- Tables involved: TSQL2012 database, Customers and Orders tables

-- Desired output
custid      companyname     HasOrderOn20070212
----------- --------------- ------------------
1           Customer NRZBB  No
2           Customer MLTDN  No
3           Customer KBUDE  No
4           Customer HFBZG  No
5           Customer HGVLZ  Yes
6           Customer XHXJV  No
7           Customer QXVLA  No
8           Customer QUHWH  No
9           Customer RTXGC  No
10          Customer EEALV  No
...

(91 row(s) affected)

-- Solution
SELECT DISTINCT C.custid, C.companyname, 
  CASE WHEN O.orderid IS NOT NULL THEN 'Yes' ELSE 'No' END AS [HasOrderOn20070212]
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid
    AND O.orderdate = '20070212';

  

04-18 08:56