34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT
EmployeeID = n,
EmployeeName = 'E' + RIGHT('000' + CAST(n AS varchar(10)),3)
INTO #Employees
FROM dbo.Nums WHERE n <= 10;
SELECT EmployeeID
INTO #Badboys
FROM (SELECT TOP(4) EmployeeID = n FROM dbo.Nums WHERE n <= 10 ORDER BY NEWID()) tmp
UNION
SELECT NULL;
--问题1:
SELECT * FROM #Employees WHERE EmployeeID IN (SELECT EmployeeID FROM #Badboys);
SELECT * FROM #Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM #Badboys);
--问题2:
SELECT * FROM #Employees WHERE EmployeeName IN (SELECT EmployeeName FROM #Badboys);
SELECT * FROM #Employees WHERE EmployeeName NOT IN (SELECT EmployeeName FROM #Badboys);
--表中字段不允许NULL
--TestCase1: 无重复数据,无索引
CREATE TABLE T1(n int NOT NULL);
CREATE TABLE T2(n int NOT NULL);
INSERT INTO T1
SELECT n FROM dbo.Nums WHERE n <= 100;
INSERT INTO T2
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0;
--TestCase2: 无重复数据,有索引
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n);
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n);
--TestCase3: 有重复数据,无索引
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(n int NOT NULL);
CREATE TABLE T2(n int NOT NULL);
INSERT INTO T1
SELECT n FROM dbo.Nums WHERE n <= 100;
INSERT INTO T2
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0
UNION ALL
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0;
--TestCase4: 有重复数据,有索引
CREATE CLUSTERED INDEX IX_T1 ON T1(n);
CREATE CLUSTERED INDEX IX_T2 ON T2(n);
--表中字段允许NULL
--TestCase5: 无重复数据,无索引
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(n int NULL);
CREATE TABLE T2(n int NULL);
INSERT INTO T1
SELECT n FROM dbo.Nums WHERE n <= 100;
INSERT INTO T2
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0;
--TestCase6: 无重复数据,有索引
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n);
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n);
--TestCase7: 有重复数据,无索引
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(n int NULL);
CREATE TABLE T2(n int NULL);
INSERT INTO T1
SELECT n FROM dbo.Nums WHERE n <= 100;
INSERT INTO T2
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0
UNION ALL
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0;
--TestCase8: 有重复数据,有索引
CREATE CLUSTERED INDEX IX_T1 ON T1(n);
CREATE CLUSTERED INDEX IX_T2 ON T2(n);
--Foreach TestCase above,分别执行以下两组语句并观察执行计划:
--肯定式逻辑
SELECT T1.*
FROM T1
WHERE EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n);
SELECT T1.*
FROM T1
WHERE T1.n IN (SELECT T2.n FROM T2);
SELECT DISTINCT T1.* --不加DISTINCT可能会引起重复
FROM T1
INNER JOIN T2
ON T1.n = T2.n;
--否定式逻辑
SELECT T1.*
FROM T1
WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n);
SELECT T1.*
FROM T1
WHERE T1.n NOT IN (SELECT T2.n FROM T2);
SELECT T1.*
FROM T1
LEFT JOIN T2
ON T1.n = T2.n
WHERE T2.n IS NULL;
--End Foreach
--清场
DROP TABLE T1;
DROP TABLE T2;