MySQL AND,OR and NOT Operators
The AND, OR, and NOT operators in MySQL are logical operators used to filter records based on multiple conditions in SQL queries particularly within the WHERE clause to filter the records.
- AND - The AND operator returns only if all the conditions is TRUE
- OR - The OR operator returns if at least one of the conditions are TRUE
- NOT -The NOT operator returns records where the condition is not true.
Demo Database:
- Table Name : products
| product_id | product_name | product_code | price | unit | category_id |
|---|---|---|---|---|---|
| 1 | The Psychology | 7550 | 250.00 | Piece | 1 |
| 2 | Stories for Children | 3098 | 350.00 | Piece | 1 |
| 3 | Harry Potter | 8472 | 275.00 | Piece | 1 |
| 4 | Tecno Spark | 9468 | 8000.00 | Nos | 2 |
| 5 | Samsung Galaxy | 7188 | 10000.00 | Nos | 2 |
| 6 | Panasonic Eluga | 3433 | 7000.00 | Nos | 2 |
| 7 | Lenovo IdeaPad | 6708 | 45000.00 | Nos | 3 |
| 8 | ASUS Celeron Dual Core | 3583 | 43000.00 | Nos | 3 |
1. AND
The AND operator combines two or more conditions and returns records only if all conditions are true.
Example
SELECT * FROM products where unit='NOS' AND category_id='2';Try it Yourself
2. OR
The OR operator combines two or more conditions and returns records if at least one condition is true.
Example
SELECT * FROM products where unit='NOS' OR price > 5000;Try it Yourself
3. NOT()
The NOT operator returns the records where the condition is not true.
Example
SELECT * FROM products where NOT unit='NOS';Try it Yourself