第三章 联接
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';
