SQL - AND & OR

SQL - Filtering with AND & OR

The AND and OR operators are the foundation of complex data filtering in SQL. They are used within the WHERE clause to combine multiple conditions, allowing you to create precise and powerful queries.

Let’s use a sample customers table for all our examples:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(25),
    salary DECIMAL(18, 2)
);

INSERT INTO customers VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'Hyderabad', 4500.00),
    (7, 'Muffy', 24, 'Indore', 10000.00);

The SQL AND Operator

The AND operator displays a record if all of the conditions separated by AND are TRUE. It’s used to narrow down your search results.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example: Find Wealthy Customers in Mumbai

Let’s find customers who live in ‘Mumbai’ AND have a salary greater than 6000.

SELECT id, name, city, salary
FROM customers
WHERE city = 'Mumbai' AND salary > 6000;

This query returns only ‘Chaitali’, because she is the only customer who meets both conditions.

ID	NAME	CITY	SALARY
4	Chaitali	Mumbai	6500.00

The SQL OR Operator

The OR operator displays a record if any of the conditions separated by OR is TRUE. It’s used to broaden your search results.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example: Find Customers from Kota or Delhi

Let’s find customers who live in ‘Kota’ OR live in ‘Delhi’.

SELECT id, name, city
FROM customers
WHERE city = 'Kota' OR city = 'Delhi';

This returns ‘Khilan’ (from Delhi) and ‘Kaushik’ (from Kota).

ID	NAME	CITY
2	Khilan	Delhi
3	Kaushik	Kota

Combining AND & OR: The Importance of Parentheses

When you combine AND and OR in the same query, you must control the order of operations. SQL evaluates AND before OR. To ensure your logic is correct and readable, always use parentheses () to group your conditions.

Example: Find Young Customers from Major Cities

We want to find customers who are younger than 25 AND live in either ‘Mumbai’ OR ‘Delhi’.

SELECT id, name, age, city
FROM customers
WHERE age < 25 AND (city = 'Mumbai' OR city = 'Delhi');

Without the parentheses, the query WHERE age < 25 AND city = 'Mumbai' OR city = 'Delhi' would be interpreted as (age < 25 AND city = 'Mumbai') OR city = 'Delhi', which is a different question and would yield a different result. The parentheses are essential.

ID	NAME	AGE	CITY

Note: In our sample data, no one under 25 lives in Mumbai or Delhi, so the result is empty. If ‘Khilan’ were 24, he would appear here.

Using AND/OR in UPDATE and DELETE Statements

These operators are just as critical for modifying data as they are for querying it.

UPDATE with AND

Give a salary raise to customers who are older than 30 AND live in ‘Ahmedabad’.

UPDATE customers
SET salary = salary * 1.10 -- 10% raise
WHERE age > 30 AND city = 'Ahmedabad';

DELETE with OR

Delete records for customers who are either under 23 years old OR have a salary less than 1600.

DELETE FROM customers
WHERE age < 23 OR salary < 1600;

**Safety Tip:** Always run a `SELECT *` with the same `WHERE` clause before running an `UPDATE` or `DELETE` to confirm you are targeting the correct rows.

Best Practices and Performance

• Use Parentheses: Always use parentheses when mixing AND and OR to avoid ambiguity.
• Use IN for ORs: For multiple OR conditions on the same column, the IN operator is cleaner and often more performant. WHERE city = 'Kota' OR city = 'Delhi' becomes WHERE city IN ('Kota', 'Delhi').
• Indexing: Conditions in a WHERE clause can benefit greatly from database indexes. An AND on two indexed columns is usually very fast. An OR between two different columns can be much slower as the database might not be able to use indexes effectively.