SQL IN, BETWEEN and LIKE

IN matches any value in a list, BETWEEN matches a range, and LIKE matches a text pattern. They are shortcuts that make WHERE clauses shorter and easier to read.

Syntax

WHERE column IN (value1, value2, ...)
WHERE column BETWEEN low AND high
WHERE column LIKE 'pattern'

IN

1. Match a list of values

Customers from the four southern metros:

SELECT customer_name, city
FROM customers
WHERE city IN ('Bengaluru', 'Chennai', 'Hyderabad', 'Kochi');

Result:

(5 rows)

This is the same as writing city = 'Bengaluru' OR city = 'Chennai' OR ..., just shorter.

2. NOT IN

Orders that are still open (not delivered, cancelled or returned):

SELECT order_id, status
FROM orders
WHERE status NOT IN ('Delivered', 'Cancelled', 'Returned');

Result:

(4 rows)

BETWEEN

3. A numeric range

Products priced from ₹500 to ₹2,500, inclusive:

SELECT product_name, price
FROM products
WHERE price BETWEEN 500 AND 2500
ORDER BY price;

Result:

(7 rows)

Both ends are included: a product at exactly ₹2,500 would appear.

4. A date range

Orders from the April–June 2025 quarter:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2025-04-01' AND '2025-06-30';

Result:

(5 rows)

This is safe because order_date is a DATE. For TIMESTAMP columns, use order_ts >= '2025-04-01' AND order_ts < '2025-07-01' so you don't lose orders placed after midnight on the last day.

LIKE and wildcards

5. Starts with

Customers whose name starts with "A":

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

Result:

(3 rows)

6. Contains

Customers with a Gmail address:

SELECT customer_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Result:

(8 rows)

7. Case-insensitive search with ILIKE

LIKE is case-sensitive in PostgreSQL. A search box needs to match "face", "Face" and "FACE":

SELECT product_name
FROM products
WHERE product_name ILIKE '%face%';

Result:

(2 rows)

In MySQL or SQL Server, use LOWER(product_name) LIKE '%face%'.

Common mistakes

NOT IN with a NULL in the list. This returns nothing, because x <> NULL is never true:

SELECT customer_name
FROM customers
WHERE phone NOT IN ('9876543210', NULL);

Result:

(0 rows)

Keep NULLs out of NOT IN lists, or use NOT EXISTS (covered in the EXISTS lesson).

• BETWEEN with the high value first. BETWEEN 2500 AND 500 returns nothing.
• A leading % on huge tables. LIKE '%term' can't use a normal index, so it scans every row. Fine for learning, slow on millions of rows.

Practice question

Find products from the brands Prestige, Milton or Hawkins that cost between ₹500 and ₹1,000.

SELECT product_name, brand, price
FROM products
WHERE brand IN ('Prestige', 'Milton', 'Hawkins')
  AND price BETWEEN 500 AND 1000;

Key takeaways

• IN replaces a chain of OR conditions on one column.
• BETWEEN is inclusive at both ends; be careful with timestamps.
• % matches any run of characters, _ matches exactly one.
• Use ILIKE (PostgreSQL) or LOWER() for case-insensitive search.