MySQL AND, OR, NOT
Combining multiple conditions in SQL queries
🔍 What are Logical Operators?
MySQL logical operators (AND, OR, NOT) allow you to combine multiple conditions in WHERE clauses. They help filter data based on complex criteria, making your queries more powerful and flexible.
-- Simple example with AND
SELECT * FROM users
WHERE age > 18 AND country = 'USA';
Logical Operators Overview
AND Operator
Returns true when all conditions are true
OR Operator
Returns true when any condition is true
NOT Operator
Reverses the result of a condition
Combined Logic
Mix operators for complex queries
🔹 The AND Operator
The AND operator displays records where all conditions separated by AND are true. It requires every condition to be satisfied for a row to be included in the result set. Use AND when you need strict filtering with multiple requirements that must all be met simultaneously.
-- Find users who are adults AND from USA
SELECT * FROM users
WHERE age >= 18 AND country = 'USA';
-- Multiple AND conditions
SELECT * FROM products
WHERE price > 100 AND stock > 0 AND category = 'Electronics';Sample Output:
| id | name | age | country |
|---|---|---|---|
| 1 | John | 25 | USA |
| 3 | Sarah | 30 | USA |
🔹 The OR Operator
The OR operator displays records where at least one condition is true. It provides flexibility by allowing any of the specified conditions to be satisfied. Use OR when you want to include rows that match any of several alternative criteria, making your search broader and more inclusive.
-- Find users from USA OR Canada
SELECT * FROM users
WHERE country = 'USA' OR country = 'Canada';
-- Multiple OR conditions
SELECT * FROM products
WHERE category = 'Books' OR category = 'Music' OR category = 'Movies';Sample Output:
| id | name | country |
|---|---|---|
| 1 | John | USA |
| 2 | Emma | Canada |
🔹 The NOT Operator
The NOT operator reverses the result of a condition, displaying records where the condition is false. It's useful for excluding specific values or patterns from your results. Use NOT when you want to filter out certain data rather than include it, creating exclusion-based queries.
-- Find users NOT from USA
SELECT * FROM users
WHERE NOT country = 'USA';
-- NOT with comparison
SELECT * FROM products
WHERE NOT price > 1000;Sample Output:
| id | name | country |
|---|---|---|
| 2 | Emma | Canada |
| 4 | Luis | Mexico |
🔹 Combining Operators
You can combine AND, OR, and NOT operators to create complex queries. Use parentheses to control the order of evaluation and ensure your logic works correctly. This allows you to build sophisticated filtering conditions that match your exact data requirements with precision and clarity.
-- Combine AND and OR with parentheses
SELECT * FROM users
WHERE (country = 'USA' OR country = 'Canada') AND age >= 18;
-- Complex combination
SELECT * FROM products
WHERE (category = 'Electronics' AND price < 500)
OR (category = 'Books' AND NOT stock = 0);Sample Output:
| id | name | age | country |
|---|---|---|---|
| 1 | John | 25 | USA |
| 2 | Emma | 22 | Canada |