MySQL learning footprint record 05 -- Data Filtering -- AND, OR, NOT, IN

MySQL learning footprint record 05 -- Data Filtering -- AND, OR, NOT, IN 1. AND operator * searches for rows matching all given conditions

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

 

2. the OR operator * searches for rows that match any given condition.

 eg: mysql> SELECT prod_name,prod_price FROM products           -> WHERE vend_id=1002 or vend_id=1003;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| Detonator      |      13.00 || Bird seed      |      10.00 || Carrots        |       2.50 || 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.01 sec)

 

3. calculation order * WHERE can contain any number of and or operators.

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

 

4. Use () to change the computing order

Eg: mysql> SELECT prod_name, prod_price FROM products-> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price> = 10; # Calculate OR first, re-calculate AND + ---------------- + ------------ + | 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)

 

5. The IN operator * is used to specify the condition range.

eg:    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)

 

The preceding statement is equivalent:

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)

 

6. The NOT operator * denies any conditions followed by it.

eg:   mysql> SELECT vend_id,prod_price FROM products            -> WHERE vend_id NOT IN (1002,1003)           -> ORDER BY prod_name;+---------+------------+| vend_id | prod_price |+---------+------------+|    1001 |       5.99 ||    1001 |       9.99 ||    1001 |      14.99 ||    1005 |      35.00 ||    1005 |      55.00 |+---------+------------+5 rows in set (0.00 sec)