WHERE

Updated on 2025-02-27 GMT+08:00

View PDF

[Rule] Do not compare the same table columns in WHERE conditions.
For example, the following statement does not meet specifications:
```
SELECT * FROM t1 WHERE col1 = col1;
```
The following modification should be considered:
```
SELECT * FROM t1 WHERE col1 IS NOT NULL;
```

[Rule] Do not include implicit data type conversion in WHERE conditions.
After implicit conversion is performed in the database, the created index may fail to be used.

You are advised to enable the GUC parameter check_implicit_conversions and disable enable_fast_query_shipping during development to check whether query statements contain implicit data types that may affect performance.
```
SET enable_fast_query_shipping = off;
SET check_implicit_conversions = true;
```
Implicit data type conversion check requires extra overhead. After the query statement is developed, disable the check_implicit_conversions parameter and reset enable_fast_query_shipping.
Example:
- The following lines of code do not meet specifications:
```
-- The phonenumber column in the t_tablename table is of the VARCHAR type instead of the numeric type.
SELECT column1
  INTO i_l_variable1
  FROM t_tablename
  WHERE phonenumber = 13512345678;
```
- The following modification should be considered:
```
-- The phonenumber column in the t_tablename table is of the VARCHAR type instead of the numeric type.
SELECT column1
  INTO i_l_variable1
  FROM t_tablename
  WHERE phonenumber = '13512345678';
```

[Rule] Do not use expressions or functions in WHERE condition columns.

When expressions or functions are used in condition columns, indexes become invalid, and each row of data is calculated, causing unnecessary performance consumption.

Example:

The following lines of code do not meet specifications:

SELECT income FROM table WHERE abs(income) > ?;
SELECT income FROM table WHERE income * 10 > ?;
SELECT create_time
  FROM table 
  WHERE date_format(create_time, '%Y%m%d %H:%i:%s') = '20090101 00:00:0';

The following modification should be considered:

SELECT income FROM table WHERE income > ?  OR income < (-1) * ?;
SELECT income FROM table WHERE income > ?/10;
SELECT create_time
  FROM table 
  WHERE create_time = str_to_date('20090101 00:00:0', '%Y%m%d %H:%i:%s');

[Rule] Do not use comparison operator (!=) when comparing with NULL in query conditions. Instead, use IS NULL or IS NOT NULL.
[Rule] Do not use comparison operator (!=) in the index columns of the query condition to avoid invalid indexes.
[Rule] Do not compare the index columns of the query condition with NULL (IS NULL and IS NOT NULL instead).
[Rule] Do not use NOT in the index columns of the query condition.
[Rule] Do not use NOT IN in the index columns of the query condition.
[Rule] Do not place % at the beginning of the LIKE statement for fuzzy query.
If % is placed at the beginning of the LIKE statement, the index cannot be used and the entire table will be scanned.
[Recommendation] The number of IN clauses in the WHERE statement shall be less than or equal to 500.
- During the query, the values of all the IN clauses will be compared to check whether they are equal, which increases the overhead.
- If the included values are relatively fixed, consider creating a temporary table, writing the clause data into the table, and then using INNER JOIN to implement the inclusion query.

[Recommendation] If the IN clause in the WHERE condition is not a constant but a column in the table, you are advised to change it to a subquery.
In this case, it is actually an unequal JOIN, which is executed through the nestloop plan. If the table is too large, the execution efficiency is low. You are advised to change the query to a subquery of the JOIN type.
Example:
- The following code does not meet specifications:
```
SELECT col1, COALESCE(max(col2 - 1), 0) 
  FROM t1, t2 
  WHERE t1.col1 = ANY(VALUES(id1), (id2))
  GROUP BY col1;
```
- The following modification should be considered:
```
SELECT col1, COALESCE(max(tmp), 0) FROM
(
   (
      SELECT col1, (col2-1) AS tmp
        FROM t1, t2 
        WHERE t1.col1 = t2.id1 AND t1.col1 != t2.id2
   ) UNION ALL (
      SELECT col1, (col2-1) AS tmp 
        FROM t1, t2 
        WHERE t1.col1 = t2.id2
   )
) GROUP BY col1;
```

[Recommendation] Use equal operators, instead of not equal operators.
If a not equal operator (IN, BETWEEN, <, <=, >, or >=) is used in the WHERE condition, no index can be used in the following conditions because two range conditions cannot be used at the same time.

Parent topic: Database Design Specifications

Previous topic: Object Access Programming Specifications

Next topic: SELECT