MySQL AND / OR

AND and OR are logical operators used to combine conditions.

SELECT *
FROM students
WHERE age > 18
AND course = 'Python';

This query returns students who satisfy both conditions.

The AND operator returns a record only when all conditions are true.

SELECT *
FROM students
WHERE age > 18
AND fee > 10000;

The student must be older than 18 and have a fee greater than 10,000.

SELECT column_name
FROM table_name
WHERE condition1
AND condition2;

Both conditions must be satisfied for a row to appear in the result.

For example, find students whose age is greater than 20 and whose course is Python.

SELECT *
FROM students
WHERE age > 20
AND course = 'Python';

You can use more than two conditions with AND.

SELECT *
FROM students
WHERE age > 18
AND fee > 10000
AND course = 'Python';

All three conditions must be true.

The OR operator returns a record when at least one condition is true.

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java';

This returns students studying either Python or Java.

SELECT column_name
FROM table_name
WHERE condition1
OR condition2;

Only one of the conditions needs to be true for a row to match.

For example, find students whose age is 18 or 19.

SELECT *
FROM students
WHERE age = 18
OR age = 19;

You can use multiple OR conditions.

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java'
OR course = 'PHP';

This returns students from any of the three courses.

AND can combine multiple text conditions.

SELECT *
FROM students
WHERE course = 'Python'
AND name = 'Rahul';

This requires both the course and name conditions to match.

OR can be used to search for different text values.

SELECT *
FROM students
WHERE name = 'Rahul'
OR name = 'Amit';

AND can combine different comparison operators.

SELECT *
FROM students
WHERE age >= 18
AND fee <= 20000;

This finds students aged 18 or older whose fee is 20,000 or less.

OR can also combine different comparison operators.

SELECT *
FROM students
WHERE age < 18
OR fee > 20000;

AND and OR can be used together in one query.

SELECT *
FROM students
WHERE course = 'Python'
AND age > 20
OR fee > 20000;

When combining operators, use parentheses when you want the intended grouping to be clear.

Parentheses help group conditions clearly.

SELECT *
FROM students
WHERE course = 'Python'
AND (age > 20 OR fee > 15000);

The condition inside parentheses is evaluated as a group.

AND can be used together with BETWEEN.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND course = 'Python';

This finds Python students whose age falls within the specified range.

IN can be combined with AND.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
AND age > 20;

This finds students from Python or Java who are older than 20.

Although IN already handles multiple alternatives, it can also be combined with OR.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
OR fee > 20000;

A student can match either the course condition or the fee condition.

AND can combine pattern matching with another condition.

SELECT *
FROM students
WHERE name LIKE 'A%'
AND age > 18;

This finds students whose names start with A and who are older than 18.

OR can be used with multiple LIKE patterns.

SELECT *
FROM students
WHERE name LIKE 'A%'
OR name LIKE 'R%';

This finds names beginning with A or R.

AND can be combined with NULL checks.

SELECT *
FROM students
WHERE mobile IS NULL
AND course = 'Python';

This finds Python students whose mobile number is NULL.

OR can also be used with NULL conditions.

SELECT *
FROM students
WHERE mobile IS NULL
OR email IS NULL;

This returns records where either mobile or email is NULL.

Logical operators can be combined with ORDER BY.

SELECT name, course, fee
FROM students
WHERE course = 'Python'
AND fee > 10000
ORDER BY fee DESC;

The matching records are filtered first and then sorted.

LIMIT can be used after filtering with AND or OR.

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java'
LIMIT 5;

This returns up to five matching records.

AND and OR can be used when updating selected records.

UPDATE students
SET fee = fee + 1000
WHERE course = 'Python'
AND age > 20;

Only students satisfying both conditions are updated.

Logical operators can also control which records are deleted.

DELETE FROM students
WHERE course = 'PHP'
AND age < 18;

Only records satisfying both conditions are deleted.

• Using AND when any one condition should match
• Using OR when all conditions should match
• Forgetting parentheses when combining complex conditions
• Using incorrect column names
• Forgetting quotes around text values
• Writing conditions in the wrong order
• Using OR without understanding the resulting records

Suppose we want Python students older than 20, or any student whose fee is above 20,000.

SELECT id, name, course, age, fee
FROM students
WHERE (course = 'Python' AND age > 20)
OR fee > 20000
ORDER BY fee DESC;

Parentheses make the intended logic clear.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, fee)
VALUES
('Rahul', 'Python', 22, 15000.00),
('Priya', 'Java', 21, 18000.00),
('Amit', 'Python', 24, 12000.00),
('Neha', 'PHP', 17, 10000.00),
('Ravi', 'Java', 26, 22000.00);

SELECT id, name, course, age, fee
FROM students
WHERE (course = 'Python' AND age > 20)
OR fee > 20000
ORDER BY fee DESC;

This query returns Python students older than 20 or students whose fee is greater than 20,000.