Skip to content
SQL

SQL WHERE Clause: Filter Rows with AND, OR, IN, BETWEEN and LIKE

Ihsan Ullah · Updated · 12 min read
SQL WHERE clause cover for Lesson 5 of the free SQL course: filter rows with AND, OR, IN, BETWEEN and LIKE

SQL course · Module 2: Querying one table · Lesson 5 of 40

Quick answer: The SQL WHERE clause keeps only the rows where a condition is true, for example SELECT name, price FROM products WHERE price > 50;. You can combine conditions with AND, OR and NOT, test a list of values with IN, test a range with BETWEEN (both ends included), and match text patterns with LIKE, where % means “any characters” and _ means “exactly one character”. In PostgreSQL, LIKE is case-sensitive and ILIKE ignores case.

In this lesson you will learn:

  • the comparison operators for numbers, text and dates
  • how AND, OR and NOT combine, and why parentheses matter
  • how to use IN, NOT IN and BETWEEN
  • how to search text with LIKE and ILIKE, and how other databases differ

Before you start: Lesson 4, SQL SELECT Statement: Columns, Aliases, DISTINCT and LIMIT.

All examples use the shop practice database from Lesson 3. We ran them on PostgreSQL 16.15; the operators used here are described the same way in the current PostgreSQL 18 manual, so they work the same on PostgreSQL 17 and 18.

How WHERE works

WHERE comes after FROM and before ORDER BY. PostgreSQL checks the condition for each row and keeps only the rows where it is true.

SELECT id, name, price FROM products WHERE price > 50 ORDER BY id;
 id |            name             | price  
----+-----------------------------+--------
  2 | Mechanical Keyboard         |  89.00
  8 | Noise-Cancelling Headphones | 149.00
  9 | Ergonomic Chair             | 259.00
 10 | HD Webcam                   |  54.90
(4 rows)

Four of the ten products cost more than 50. The others were checked and dropped.

Comparison operators

OperatorMeaning
=equal to
<> or !=not equal to
< and >less than, greater than
<= and >=less than or equal, greater than or equal

Text values go in single quotes:

SELECT id, name, city FROM customers WHERE country = 'Pakistan' ORDER BY id;
 id |     name      |  city   
----+---------------+---------
  1 | Ayesha Malik  | Lahore
  4 | bilal ahmed   | Karachi
  7 | Hira Siddiqui | 
 10 | Omar Haddad   | Lahore
(4 rows)

For “not equal”, the PostgreSQL docs say <> “is the standard SQL notation” and != is an alias for it (PostgreSQL docs: Comparison Operators). Both give the same result:

SELECT id, order_date, status FROM orders WHERE status <> 'delivered' ORDER BY id;
 id | order_date |  status   
----+------------+-----------
  4 | 2026-06-22 | cancelled
 12 | 2026-08-18 | cancelled
 19 | 2026-10-14 | shipped
 20 | 2026-10-21 | shipped
 21 | 2026-10-27 | shipped
 22 | 2026-10-30 | pending
(6 rows)
SELECT count(*) FROM orders WHERE status != 'delivered';
 count 
-------
     6
(1 row)

Dates work the same way. Write them in quotes as 'YYYY-MM-DD'; because order_date is a date column, PostgreSQL reads the text as a date:

SELECT id, order_date, status FROM orders WHERE order_date >= '2026-10-01' ORDER BY id;
 id | order_date |  status   
----+------------+-----------
 18 | 2026-10-06 | delivered
 19 | 2026-10-14 | shipped
 20 | 2026-10-21 | shipped
 21 | 2026-10-27 | shipped
 22 | 2026-10-30 | pending
(5 rows)

Text comparison is case-sensitive

Customer 8 was typed in capitals. An exact match must use the same case:

SELECT id, name FROM customers WHERE name = 'noah wilson';
 id | name 
----+------
(0 rows)
SELECT id, name FROM customers WHERE name = 'NOAH WILSON';
 id |    name     
----+-------------
  8 | NOAH WILSON
(1 row)

No error, just zero rows. That makes this mistake easy to miss. ILIKE, later in this lesson, solves it.

Combine conditions with AND, OR and NOT

AND keeps a row only when both conditions are true:

SELECT id, name, category, price FROM products
WHERE category = 'Electronics' AND price < 50
ORDER BY id;
 id |      name      |  category   | price 
----+----------------+-------------+-------
  1 | Wireless Mouse | Electronics | 24.99
  3 | USB-C Hub      | Electronics | 39.50
(2 rows)

OR keeps a row when at least one condition is true:

SELECT id, name, country FROM customers
WHERE country = 'Canada' OR country = 'USA'
ORDER BY id;
 id |     name     | country 
----+--------------+---------
  2 | Lucas Martin | Canada
  8 | NOAH WILSON  | USA
(2 rows)

AND is applied before OR

Suppose you want Electronics or Stationery products that cost less than 40. This looks right, but it is not:

SELECT id, name, category, price FROM products
WHERE category = 'Electronics' OR category = 'Stationery' AND price < 40
ORDER BY id;
 id |            name             |  category   | price  
----+-----------------------------+-------------+--------
  1 | Wireless Mouse              | Electronics |  24.99
  2 | Mechanical Keyboard         | Electronics |  89.00
  3 | USB-C Hub                   | Electronics |  39.50
  6 | Notebook Pack               | Stationery  |  12.50
  7 | Gel Pens 10-Pack            | Stationery  |   6.75
  8 | Noise-Cancelling Headphones | Electronics | 149.00
 10 | HD Webcam                   | Electronics |  54.90
(7 rows)

The headphones cost 149, yet they are in the result. In the PostgreSQL operator precedence table, NOT comes first, then AND, then OR (PostgreSQL docs: Lexical Structure, Operator Precedence). So PostgreSQL read the condition as “Electronics, OR (Stationery AND under 40)”: every Electronics product passed, whatever its price. Parentheses say what you mean:

SELECT id, name, category, price FROM products
WHERE (category = 'Electronics' OR category = 'Stationery') AND price < 40
ORDER BY id;
 id |       name       |  category   | price 
----+------------------+-------------+-------
  1 | Wireless Mouse   | Electronics | 24.99
  3 | USB-C Hub        | Electronics | 39.50
  6 | Notebook Pack    | Stationery  | 12.50
  7 | Gel Pens 10-Pack | Stationery  |  6.75
(4 rows)

Whenever a WHERE clause mixes AND and OR, add parentheses, even when they are not strictly needed.

NOT

NOT turns true into false and false into true:

SELECT id, name, category FROM products WHERE NOT category = 'Electronics' ORDER BY id;
 id |       name       |  category   
----+------------------+-------------
  4 | Laptop Stand     | Accessories
  5 | Desk Lamp        | Home Office
  6 | Notebook Pack    | Stationery
  7 | Gel Pens 10-Pack | Stationery
  9 | Ergonomic Chair  | Home Office
(5 rows)

IN and NOT IN: match a list

IN is a short way to write several = conditions joined by OR (PostgreSQL docs: Row and Array Comparisons):

SELECT id, name, country FROM customers
WHERE country IN ('Canada', 'France', 'Italy')
ORDER BY id;
 id |     name     | country 
----+--------------+---------
  2 | Lucas Martin | Canada
  3 | Sofia Rossi  | Italy
  9 | Chloe Dubois | France
(3 rows)

NOT IN keeps rows that match none of the values. Here are the orders that are still open:

SELECT id, order_date, status FROM orders
WHERE status NOT IN ('delivered', 'cancelled')
ORDER BY id;
 id | order_date | status  
----+------------+---------
 19 | 2026-10-14 | shipped
 20 | 2026-10-21 | shipped
 21 | 2026-10-27 | shipped
 22 | 2026-10-30 | pending
(4 rows)

Be careful when the list could contain NULL: NOT IN then returns no rows at all. Lesson 6 shows this trap.

BETWEEN: match a range

a BETWEEN x AND y means a >= x AND a <= y. The docs say: “BETWEEN treats the endpoint values as included in the range” (PostgreSQL docs: Comparison Operators).

SELECT id, name, price FROM products WHERE price BETWEEN 32 AND 45 ORDER BY price;
 id |     name     | price 
----+--------------+-------
  5 | Desk Lamp    | 32.00
  3 | USB-C Hub    | 39.50
  4 | Laptop Stand | 45.00
(3 rows)

Both 32.00 and 45.00 are included. It works for dates too. Here are all the orders in September 2026:

SELECT id, customer_id, order_date FROM orders
WHERE order_date BETWEEN '2026-09-01' AND '2026-09-30'
ORDER BY id;
 id | customer_id | order_date 
----+-------------+------------
 14 |           4 | 2026-09-02
 15 |           8 | 2026-09-02
 16 |           5 | 2026-09-17
 17 |           1 | 2026-09-29
(4 rows)

That works because order_date holds only dates. If a column also holds a time (type timestamp), '2026-09-30' means midnight at the start of that day, so anything later on 30 September is outside the range:

SELECT TIMESTAMP '2026-09-30 14:00' BETWEEN '2026-09-01' AND '2026-09-30' AS in_range,
       TIMESTAMP '2026-09-30 14:00' >= '2026-09-01' AND TIMESTAMP '2026-09-30 14:00' < '2026-10-01' AS half_open;
 in_range | half_open 
----------+-----------
 f        | t
(1 row)

psql shows t for true and f for false. For timestamps, use >= the first day and < the day after the last day.

LIKE: match a text pattern

LIKE compares text with a pattern. An underscore (_) “matches any single character” and a percent sign (%) “matches any sequence of zero or more characters” (PostgreSQL docs: Pattern Matching).

SELECT id, name FROM customers WHERE name LIKE 'D%' ORDER BY id;
 id |     name     
----+--------------
  6 | Daniel Weber
(1 row)

A pattern must match the whole value, so “contains” needs % on both sides:

SELECT id, name FROM products WHERE name LIKE '%Pack%' ORDER BY id;
 id |       name       
----+------------------
  6 | Notebook Pack
  7 | Gel Pens 10-Pack
(2 rows)

And _ stands for exactly one character:

SELECT id, name FROM products WHERE name LIKE 'USB-_ Hub' ORDER BY id;
 id |   name    
----+-----------
  3 | USB-C Hub
(1 row)

LIKE is case-sensitive; ILIKE is not

SELECT id, name FROM customers WHERE name LIKE '%wilson%';
 id | name 
----+------
(0 rows)
SELECT id, name FROM customers WHERE name ILIKE '%wilson%';
 id |    name     
----+-------------
  8 | NOAH WILSON
(1 row)

ILIKE makes the match “case-insensitive according to the active locale”. The docs add that it “is not in the SQL standard but is a PostgreSQL extension”. It also finds the customer typed in lower case:

SELECT id, name FROM customers WHERE name ILIKE 'b%' ORDER BY id;
 id |    name     
----+-------------
  4 | bilal ahmed
(1 row)

A way that works in every database is to change the column to lower case first and write the pattern in lower case:

SELECT id, name FROM customers WHERE lower(name) LIKE '%wilson%';
 id |    name     
----+-------------
  8 | NOAH WILSON
(1 row)

NOT LIKE keeps the rows that do not match. Note that “Ergonomic Chair” has only a capital E, so it passes:

SELECT id, name FROM products WHERE name NOT LIKE '%e%' ORDER BY id;
 id |      name       
----+-----------------
  3 | USB-C Hub
  4 | Laptop Stand
  9 | Ergonomic Chair
(3 rows)

Dialect note. Case rules differ between databases. In MySQL the default collation is utf8mb4_0900_ai_ci, “so nonbinary string comparisons are case-insensitive by default”, and LIKE 'a%' finds values starting with A or a (MySQL 9.7 manual: Case Sensitivity in String Searches). In SQLite, 'a' LIKE 'A' is true, but only for ASCII letters (SQLite: Expressions). In SQL Server, “LIKE comparisons are affected by collation” (Microsoft Learn: LIKE). Of the four databases in this course, only PostgreSQL has ILIKE; in our SQLite 3.53.1 test it failed with near "ILIKE": syntax error. Lesson 2 covers more differences.

A first look at NULL in WHERE

Here is a surprise. This query asks for customers who do not live in Lahore:

SELECT id, name, city FROM customers WHERE city <> 'Lahore' ORDER BY id;
 id |     name     |  city   
----+--------------+---------
  2 | Lucas Martin | Toronto
  3 | Sofia Rossi  | Milan
  4 | bilal ahmed  | Karachi
  5 | Emma Clarke  | London
  6 | Daniel Weber | Berlin
  8 | NOAH WILSON  | Chicago
  9 | Chloe Dubois | Paris
(7 rows)

Ten customers, two in Lahore, but only seven rows. Customer 7, Hira Siddiqui, is missing because her city is NULL (unknown). An unknown city is neither “Lahore” nor “not Lahore”, so WHERE drops the row. Lesson 6 explains why and how to include her.

Try it yourself

Exercise 1: Cheap electronics

List the id, name and price of Electronics products that cost less than 50.

Exercise 2: August orders that were not cancelled

Show the id, date and status of orders placed in August 2026, leaving out cancelled ones.

Exercise 3: Find “ah” in any case

Find customers whose name contains the letters “ah”. Try it with LIKE, then with ILIKE. Why are the results different?

Solutions

Solution 1

SELECT id, name, price FROM products
WHERE category = 'Electronics' AND price < 50
ORDER BY id;
 id |      name      | price 
----+----------------+-------
  1 | Wireless Mouse | 24.99
  3 | USB-C Hub      | 39.50
(2 rows)

Both conditions must be true, so AND is the right choice.

Solution 2

SELECT id, order_date, status FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31'
  AND status <> 'cancelled'
ORDER BY id;
 id | order_date |  status   
----+------------+-----------
 10 | 2026-08-04 | delivered
 11 | 2026-08-11 | delivered
 13 | 2026-08-25 | delivered
(3 rows)

August had four orders; order 12 was cancelled, so three remain.

Solution 3

SELECT id, name FROM customers WHERE name LIKE '%ah%' ORDER BY id;
 id |    name     
----+-------------
  4 | bilal ahmed
(1 row)
SELECT id, name FROM customers WHERE name ILIKE '%ah%' ORDER BY id;
 id |    name     
----+-------------
  4 | bilal ahmed
  8 | NOAH WILSON
(2 rows)

LIKE does not match the capital “AH” in “NOAH WILSON”; ILIKE ignores case and finds both.

Common mistakes

  • Double quotes around a text value. WHERE country = "Pakistan" fails with ERROR: column "Pakistan" does not exist, because double quotes mean a name. Use 'Pakistan'.
  • Using = with a wildcard. WHERE name = 'D%' returns 0 rows without an error; = looks for the exact text D%. Use LIKE 'D%'.
  • Mixing AND and OR without parentheses. AND is applied first, so you get extra rows, as the headphones example showed.
  • Using a column alias in WHERE. SELECT name, price * 0.9 AS sale_price FROM products WHERE sale_price < 20; fails with ERROR: column "sale_price" does not exist. Repeat the expression: WHERE price * 0.9 < 20. Lesson 11 explains why.
  • Comparing a number with text that is not a number. WHERE price > 'cheap' fails with ERROR: invalid input syntax for type numeric: "cheap".

Frequently asked questions

Is BETWEEN inclusive in SQL?

Yes. BETWEEN 32 AND 45 includes 32 and 45, the same as >= 32 AND <= 45. Put the smaller value first. With timestamps, prefer >= and < the next day, so you do not lose times on the last day.

How do I make LIKE case-insensitive?

In PostgreSQL, use ILIKE. For SQL that also runs on other databases, compare in one case: lower(name) LIKE '%wilson%'. In MySQL, LIKE is usually case-insensitive already, because of its default collation.

What is the difference between WHERE and HAVING?

WHERE filters single rows before they are grouped. HAVING filters groups after GROUP BY, for example “countries with more than two customers”. Lesson 10 covers GROUP BY and HAVING.

Summary

  • WHERE condition keeps only rows where the condition is true; it goes after FROM and before ORDER BY.
  • Text and dates go in single quotes; = on text is case-sensitive in PostgreSQL.
  • AND is applied before OR, so use parentheses when you mix them.
  • IN (...) matches a list, and BETWEEN x AND y includes both ends.
  • LIKE uses % and _ and is case-sensitive; ILIKE ignores case, but MySQL, SQLite and SQL Server do not have it.

Filtering is a skill you will use in every query from now on, whether you follow the data analyst roadmap or the data engineer roadmap.

Next lesson

Next: Lesson 6, SQL NULL Values: IS NULL, COALESCE and Three-Valued Logic.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *