提升查询技能，这7条SQL查询错误必须解决

评论

本文将指出一些常见但却总是被忽略的错误，请静下心来，准备好提升查询技能吧！

让我们以一个虚构的业务为例。假设你是亚马逊电子商务分析团队的一员，需要运行几个简单的查询。你手头有两个表，分别为“product（产品）”和“discount （折扣）”。

1.计算 NULL 字段的数目

为了计算 null 字段的数目，要掌握 COUNT 函数的工作原理。假设计算产品数量时，要求计入表格“product”的“product_id”主键列中遗漏的字段。

1
2
SELECT COUNT(product_id)
FROM product;Result: 3

由于要求计入“product_id”列中的 null 值，查询结果应该为 4，但 COUNT（）函数不会将 null 值计数。

解决方法：使用 COUNT（*）函数。该函数可以将 null 值计数。

1
2
Select Count(*)
From product;Result: 4

这个操作很简单，但是在编写复杂的查询时总会被忽略。

2.使用保留字作为列名

1
2
3
SELECT product_id,
RANK() OVER (ORDER BY price desc) As Rank
FROM product;

由于列名“Rank”是 Rank 函数的保留字，该查询结果出错。

解决方法：

1
2
3
SELECT product_id,
RANK() OVER (ORDER BY price desc) As ‘Rank’
FROM product;

加上单引号，即可得到想要的结果。

3.NULL 的比较运算

1
2
3
SELECT product_name
FROM product
WHERE product_id=NULL;

由于使用了比较运算符“=”，此处运算会出现异常，使用另一比较运算符“!=”运算也会出现异常。这里的逻辑问题在于，你编写的查询得出的是“product_id”列的值是否未知，而无法得出这一列的值是否是未知的产品。

解决方法：

1
2
3
SELECT product_name
FROM product
WHERE product_id ISNULL;

4.ON 子句过滤和 WHERE 子句过滤的区别

这是一个非常有趣的概念，会提高你对于 ON 子句过滤和 WHERE 子句过滤之间区别的基本理解。这并不完全是一个错误，只是演示了两者的用法，你可以根据业务需求选择最佳方案。

1
2
3
4
5
6
SELECT d.product_id,
price,
discount
FROM product p RIGHT JOIN discount d ON
p.product_id=d.product_id
WHERE p.product_id>1;

结果：

在这种情况下，过滤条件在两个表格连接之后生效。因此，所得结果不包含 d.product_id≤1 的行（同理，显然也不包含 p.product≤1 的行）。

解决方法：使用 AND，注意结果上的不同。

1
2
3
4
5
6
SELECT d.product_id,
price,
discount
FROM product p RIGHT JOIN discount d ON
p.product_id=d.product_id
AND p.product_id>1;

结果：

在这里，条件语句 AND 在两个表格连接发生之前计算。可以把此查询看作只适用于一个表(“product”表)的 WHERE 子句。现在，由于右连接，结果中出现了 d.product_id≤1 的行(显然还有 p.product_id>1 的行)。

请注意，ON 子句过滤和 WHERE 子句过滤只在左/右/外连接时不同，而在内连接时相同。

5.在同一查询的 WHERE 子句中使用 Windows 函数生成的列&使用 CASE WHEN 子句

注意，不要在同一查询的 WHERE 子句中使用通过 Windows 函数生成的列名以及 CASE WHEN 子句。

1
2
3
4
SELECT product_id,
RANK() OVER (ORDER BY price desc) AS rk
FROM product
WHERE rk=2;

因为列 rk 由 Windows 函数生成，并且在同一查询的 WHERE 子句中使用了该列，这个查询结果会出现异常。

解决方法：这一问题可以通过使用临时表或者子查询解决。

1
2
3
4
5
6
7
8
9
10
WITH CTE AS
(
SELECT product_id,
RANK() OVER (ORDER BY price desc) AS rk
FROM product
)
SELECT product_id
FROM
CTE
WHERE rk=2;

或

1
2
3
4
5
6
7
8
SELECT product_id
FROM
(
SELECT product_id,
RANK() OVER (ORDER BY price desc) AS rk
FROM product;
)
WHERE rk=2;

同样的方法也适用于使用 CASE WHEN 子句创建的列。请记住，Windows 函数只能出现在 SELECT 或 ORDER BY 子句中。

6.BETWEEN 的使用不正确

如果不清楚 BETWEEN 的有效范围，也许会得不到想要的查询结果。BETWEEN x AND y 语句的有效范围包含 x 和 y。

1
2
3
4
SELECT *
FROM discount
WHERE offer_valid_till BETWEEN ‘2019/01/01’ AND ‘2020/01/01’
ORDER BY offer_valid_till;

结果：

在查询中，也许我们只想得到 2019 年的所有日期，但是结果中还包含了 2020 年 1 月 1 日。这是因为 BETWEEN 语句的有效范围包含 2019/01/01 和 2020/01/01。

解决方法：相应地调整范围可以解决这个问题。

1
2
3
4
SELECT *
FROM discount
WHERE offer_valid_till BETWEEN ‘2019/01/01’ AND ‘2019/12/31’
ORDER BY offer_valid_till;

结果：

现在，所有查询结果均为 2019 年的日期。

7.在 GROUP BY 语句后使用 WHERE 子句

在编写 GROUP BY 语句时，请注意 WHERE 子句的位置。

1
2
3
4
5
6
SELECT category,
AVG (price)
FROM product p INNER JOIN discount d ON
p.product_id=d.product_id
GROUP BY category
WHERE discount_amount>10;

由于将 WHERE 子句放在 GROUP BY 语句后，此查询是错误的。这是为什么呢？

WHERE 子句用于过滤查询结果，这一步要在将查询结果分组之前实现，而不是先分组再过滤。正确的做法是先应用 WHERE 条件过滤减少数据，再使用 GROUP BY 子句通过聚合函数将数据分组（此处使用聚合函数 AVG）。

解决方法：

1
2
3
4
5
6
SELECT category,
AVG (price)
FROM product p INNER JOIN discount d ON
p.product_id=d.product_id
WHERE discount_amount>10
GROUP BY category;

请注意主要 SQL 语句的执行顺序：

· FROM 子句

· WHERE 子句

· GROUP BY 子句

· HAVING 子句

· SELECT 子句

· ORDER BY 子句

以上包含了大部分让人不解的错误，尤其是对初学者而言。正如亨利·福特所说：“唯一的错误是我们从中学不到任何东西”，希望这篇文章能帮助你精进查询技能。