为什么我要在FROM
子句中使用 SQL 中的LEFT JOIN
,并在实体"为 null"的地方附加一个WHERE
子句?有人告诉我这是一个非常有效的脚本,我应该学习它背后的方法论。
例如:
FROM
something
LEFT JOIN aRow a AND bRow b AND cRow c AND dRow d
WHERE
bRow.b IS NULL;
当您特别想知道"从未订购过任何东西的所有客户的列表"之类的内容时,会使用这种结构:
SELECT
customer.*
FROM
customers
LEFT JOIN
orders
ON
orders.customerid = customers.id
WHERE
orders.id IS NULL
或者引用我的一位老经理的话:"你能让数据库给我一个数据库中没有的所有内容的列表吗?
我>"当然,你能给我一个清单,列出数据库应该告诉你它没有的东西吗?">
他>"我怎么知道呢?
这确实是一个相当通用的、非 RDBMS 特定的问题。该逻辑几乎适用于任何类型的 SQL。这是任何使用数据查询的人都应该熟悉的技术。
出于所有意图和目的(并跳过 OP 中有缺陷的语法(,这与以下查询相同:
SELECT *
FROM table1
WHERE table1.col1 NOT IN (
SELECT table2.col1 FROM table2 WHERE table2.col2 = <filterHere>
)
当您处理几百行时,您可能不会看到性能的显着差异。但是,当您在两个表中只处理几百万行时,您肯定会看到性能显着提高
SELECT table1.*
FROM table1
LEFT OUTER JOIN table2 ON table1.col1 = table2.col1
AND table2.col2 = 42
WHERE table2.id IS NULL
让我们说明这些查询发生了什么。
创建测试表。
CREATE TABLE table1 (col1 int, col2 varchar(10)) ;
INSERT INTO table1 ( col1, col2 )
VALUES (1,'a')
, (2,'b')
, (3,'c')
, (4,'d')
CREATE TABLE table2 (col1 int, col2 varchar(10)) ;
INSERT INTO table2 ( col1, col2 )
VALUES (1,'a')
, (3,'c')
这给了我们
table1
col1 col2
1 a
2 b
3 c
4 d
table2
col1 col2
1 a
3 c
现在我们想要table1
但不在table2
中的列。
SELECT t1.col1, t1.col2
FROM table1 t1
WHERE t1.col1 NOT IN (
SELECT t2.col1 FROM table2 t2
)
我们不能从table2
SELECT
任何东西,因为该表只是一个子查询,而不是整个查询的一部分。我们无法使用它。
这分解为
SELECT t1.col1, t1.col2
FROM table1 t1
WHERE t1.col1 NOT IN ( 1,3 )
这进一步细分为
SELECT t1.col1, t1.col2
FROM table1 t1
WHERE t1.col1 <> 1
OR t1.col1 <> 3
这些查询给了我们
col1 col2
2 b
4 d
这是一个子查询,分为 2 个不同的OR
语句来过滤我们的结果。
所以让我们看一个JOIN
.我们希望所有记录都在左侧,并且只包括右侧匹配的记录。所以
SELECT t1.col1 AS t1_col1, t1.col2 AS t1_col2, t2.col1 AS t2_col1, t2.col2 AS t2_col2
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.col1 = t2.col1
使用JOIN
,两个表都可供我们的SELECT
使用,因此我们可以看到tablel2
中的哪些记录与table1
中的记录匹配。以上给了我们
t1_col1 t1_col2 t2_col1 t2_col2
1 a 1 a
2 b NULL NULL
3 c 3 c
4 d NULL NULL
通过额外的数据,我们可以看到 2 和 4 的 col1 在两个表中不匹配。现在,我们可以通过一个简单的WHERE
语句过滤掉这些语句。
SELECT t1.col1, t1.col2
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.col1 = t2.col1
WHERE t2.col1 IS NULL
给我们
col1 col2
2 b
4 d
筛选器中没有子查询,只有一个语句。此外,这允许引擎的优化器制定更有效的查询计划。
当我们只处理几行时,不可能看到性能的差异,但是将这些表乘以几百万行,您肯定会看到JOIN
的速度有多快。