MySQL must know and know -- Chapter 7 data filtering

Posted by irandoct on Thu, 13 Jan 2022 05:54:01 +0100

Data filtering

This chapter teaches how to combine WHERE clauses to create more powerful and advanced search conditions. We will also learn how to use the NOT and IN operators.

Combine WHERE clause

Last chapter( MySQL must know and know - Chapter 6 filtering data )All the WHERE clauses introduced in this article use a single condition when filtering data. For stronger filtering control, MySQL allows multiple WHERE clauses to be given. Usage: used as an AND clause OR as an OR clause.

Operators are used to join or change the keywords of clauses in WHERE clauses. Also known as logical operator.

AND operator

You can use the AND operator to attach conditions to the WHERE clause to filter through more than one column:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1003 AND prod_price <= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

This SQL statement retrieves the names AND prices of all products manufactured by supplier 1003 with a price less than or equal to 10. The WHERE clause in this SELECT statement contains two filter conditions AND joins them with the AND keyword. AND indicates that MySQL only returns rows that meet all filter conditions.

AND is a keyword used in the WHERE clause to indicate that rows satisfying all given conditions are retrieved.

The above example only contains a statement with the keyword AND, which combines the two filter conditions. You can also add multiple filter conditions, AND you need to use an AND for each one.

OR operator

Unlike the AND operator, the IN operator instructs MySQL to retrieve rows that match any of the following criteria:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

This SQL statement retrieves the product names AND prices of all products manufactured by any specified supplier (1002, 1003). The OR operator tells MySQL to match any condition instead of all conditions at the same time (if AND is used here, there will be no result, because no goods belong to two suppliers at the same time).

The keyword used in the OR WHERE clause to retrieve rows that match any given condition.

Calculation order

WHERE can contain any number of AND and AND OR operators. Allows a combination of the two for complex AND advanced filtering. However, the combination of the two will bring some problems. For example, we need to list products with a price of more than 10 (inclusive) AND manufactured by 1002 OR 1003:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
6 rows in set (0.00 sec)

Two of the rows returned by this statement have prices less than 10, which is not in line with our expected results. This is the problem caused by the calculation order. SQL gives priority to the AND operator before dealing with the OR operator. Therefore, this SQL statement retrieves products with a price of more than 10 manufactured by supplier 1003 OR products manufactured by supplier 1002.

Facing the problem of calculation order, we can use parentheses to explicitly group the corresponding operators.

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
4 rows in set (0.00 sec)

The only difference between this SELECT statement AND the previous one is that the first two conditions of this statement are enclosed in parentheses. Parentheses have higher priority than AND and OR operators. MySQL first filters the OR conditions in parentheses. Therefore, this SQL statement becomes a search for products manufactured by suppliers 1002 OR 1003 with prices above 10 (inclusive), which is in line with our expectations.

Use parentheses in the WHERE clause. Whenever you use a WHERE clause with AND and OR operators, you should use parentheses to explicitly group operators. Parentheses can eliminate ambiguity.

IN operator

Parentheses can also be used with the IN operator IN the WHERE clause. The IN operator is used to specify the condition range, and each condition IN the range can be matched. The condition list of IN is enclosed IN parentheses and separated by commas.

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id IN (1002, 1003)
    -> ORDER BY prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

This SELECT statement retrieves all products manufactured by suppliers 1002 and 1003.

The function of the IN operator is the same as that of the OR. For example, the following code functions are the same as the previous example:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003
    -> ORDER BY prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

Advantages of the IN operator:

When there are many filter conditions, the syntax of the IN operator is clearer and more intuitive.
When using IN, the calculation order is better managed.
The IN operator generally executes faster than the OR operator.
The biggest advantage of IN is that it can contain other SELECT statements and more dynamically establish WHERE clauses.

The keyword used to specify the list of values to be matched in the IN WHERE clause has the same function as OR.

NOT operator

The NOT operator in the WHERE clause has and has only one function, negating any condition after it.

The keyword in the NOT WHERE clause used to negate a condition followed by a condition.

For example, list the products of all suppliers except 1002 and 1003:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id NOT IN (1002, 1003)
    -> ORDER BY prod_name;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
5 rows in set (0.00 sec)

NOT in this statement negates the following conditions. Therefore, MySQL matches products from vendors other than 1002 and 1003.

NOT is more practical IN complex clauses. For example, it can be used IN combination with the IN operator to find out the rows that do NOT match the condition list.

NOT IN MySQL supports NOT to negate IN, BETWEEN, and EXISTS clauses.

Topics: Database MySQL SQL

Programmer Think