MySQL-WHERE clause and logical operators

WHERE clause

We generally use where clauses for data filtering, such as:

 SELECT p_id FROM products WHERE p_price<5;

• Use ORDER BY should be placed after WHERE or an error will be reported.
• In addition to greater than or less than or equal to, the operators of the WHERE clause also include unequal (there are two ways to write unequal: <> and !=) and BETWEEN in the closed interval.

In particular, MySQL has a special WHERE clause IS NULL for null:

 SELECT p_id FROM products WHERE p_price IS NULL;

logical operation

AND operator

When multiple filtering conditions need to be met at the same time, you can use AND, and add an AND for each condition, for example:

 SELECT p_id FROM products WHERE p_price<500 AND p_brand='Apple';

OR operator

It is not necessary to satisfy all the given conditions at the same time, only one condition is satisfied, for example:

 SELECT p_id FROM products WHERE p_price<500 OR p_brand='Apple';

• AND takes precedence over OR in MySQL, so add appropriate parentheses if you want to perform complex logical operations.

IN operator

The IN operator is a bit like enumeration and is used to filter data that meets the conditions in parentheses, and IN is recommended because the syntax is intuitive, the calculation order is easy to manage, the operation is faster than OR, etc., and other SELECT statements can be included, such as:

 SELECT p_id FROM products WHERE p_brand IN （'Apple', 'HUAWEI');

NOT

The NOT operator can filter out the data that does not meet the conditions, for example:

 SELECT p_id FROM products WHERE p_brand NOT IN （'Apple', 'HUAWEI');

---

Reference: Forta B. MySQL crash course[M]. Pearson Education India, 2006.