KAHIBARO
Discord Login Register ☰
☰

10.7. WHERE

Table of Contents

Introduction

In SQL, the WHERE clause lets you filter rows. Without WHERE, a SELECT, UPDATE, or DELETE statement affects all rows in a table. With WHERE, you pick only the rows that match some condition.

You will use WHERE in almost every non trivial SQL query.

Rule:
WHERE filters rows before grouping and aggregation. It decides which rows participate in the query.

We will focus on WHERE with SELECT queries. The same syntax applies to UPDATE and DELETE.

Assume a table:

sql 
CREATE TABLE users (
    id        SERIAL PRIMARY KEY,
    name      TEXT,
    age       INT,
    country   TEXT,
    is_active BOOLEAN
);

Basic WHERE Syntax

The general form with SELECT is:

sql 
SELECT column_list
FROM table_name
WHERE condition;

For example, get all active users:

sql 
SELECT id, name, age, country
FROM users
WHERE is_active = TRUE;

If you omit WHERE, you get everything:

sql 
SELECT id, name, age, country
FROM users;

Comparison Operators in WHERE

You can compare values in WHERE using comparison operators.

OperatorMeaningExample
=Equal toage = 30
<> or !=Not equal tocountry <> 'USA'
<Less thanage < 18
>Greater thanage > 65
<=Less than or equalage <= 21
>=Greater than or equalage >= 21

Examples:

sql 
-- Users from a specific country
SELECT *
FROM users
WHERE country = 'Germany';
-- Users younger than 18
SELECT *
FROM users
WHERE age < 18;
-- Users not from USA
SELECT *
FROM users
WHERE country <> 'USA';

Combining Conditions with AND, OR, NOT

You often need more than one condition. Use AND, OR, and NOT.

OperatorMeaning
ANDAll conditions must be true
ORAt least one condition is true
NOTNegates a condition

AND

sql 
-- Active adult users from USA
SELECT *
FROM users
WHERE is_active = TRUE
  AND age >= 18
  AND country = 'USA';

All three conditions must be true for a row to be selected.

OR

sql 
-- Users from USA or Canada
SELECT *
FROM users
WHERE country = 'USA'
   OR country = 'Canada';

A user from either country matches.

NOT

sql 
-- All inactive users
SELECT *
FROM users
WHERE NOT is_active;
-- All users not from USA
SELECT *
FROM users
WHERE NOT country = 'USA';

Operator Precedence and Parentheses

AND and OR follow a precedence order:

  1. NOT
  2. AND
  3. OR

So this:

sql 
WHERE country = 'USA' OR country = 'Canada' AND is_active = TRUE

is interpreted as:

sql 
WHERE country = 'USA'
   OR (country = 'Canada' AND is_active = TRUE);

If you want a different grouping, use parentheses:

sql 
-- Active users from USA or Canada
SELECT *
FROM users
WHERE (country = 'USA' OR country = 'Canada')
  AND is_active = TRUE;

Rule: Always use parentheses when mixing AND and OR. This avoids logical mistakes and makes the query easier to understand.

Filtering Ranges with BETWEEN

BETWEEN is a shortcut for a value in a closed range.

sql 
value BETWEEN low AND high

is equivalent to:

sql 
value >= low AND value <= high

Example:

sql 
-- Users aged between 18 and 30 (inclusive)
SELECT *
FROM users
WHERE age BETWEEN 18 AND 30;

You can also use NOT BETWEEN:

sql 
-- Users younger than 18 or older than 30
SELECT *
FROM users
WHERE age NOT BETWEEN 18 AND 30;

Rule: BETWEEN is inclusive. Both boundary values are included.

Filtering Lists with IN and NOT IN

Use IN to check if a value matches any in a list.

sql 
-- Users from selected countries
SELECT *
FROM users
WHERE country IN ('USA', 'Canada', 'Mexico');

Equivalent without IN:

sql 
WHERE country = 'USA'
   OR country = 'Canada'
   OR country = 'Mexico';

Use NOT IN to exclude values:

sql 
-- Users not from USA or Canada
SELECT *
FROM users
WHERE country NOT IN ('USA', 'Canada');

Pattern Matching with LIKE

LIKE is used with text columns to match patterns.

PatternMeaning
%Any sequence of characters, including empty
_Exactly one character

Examples:

sql 
-- Names starting with 'A'
SELECT *
FROM users
WHERE name LIKE 'A%';
-- Names ending with 'son'
SELECT *
FROM users
WHERE name LIKE '%son';
-- Names containing 'ann'
SELECT *
FROM users
WHERE name LIKE '%ann%';
-- Names with 4 letters, starting with 'J'
SELECT *
FROM users
WHERE name LIKE 'J___';

Negation:

sql 
-- Names NOT starting with 'A'
SELECT *
FROM users
WHERE name NOT LIKE 'A%';

Handling NULL in WHERE

NULL means "unknown" or "no value". You cannot compare to NULL with = or <>.

Use IS NULL and IS NOT NULL.

sql 
-- Users with unknown age
SELECT *
FROM users
WHERE age IS NULL;
-- Users with known age
SELECT *
FROM users
WHERE age IS NOT NULL;

Rule:
Never use = NULL or <> NULL.
Use IS NULL and IS NOT NULL.

Example of a bug:

sql 
-- Wrong: returns no rows
SELECT *
FROM users
WHERE age = NULL;

Boolean Columns in WHERE

If a column is already boolean, you do not need to compare to TRUE or FALSE.

sql 
-- Active users
SELECT *
FROM users
WHERE is_active;
-- Inactive users
SELECT *
FROM users
WHERE NOT is_active;

These are equivalent to:

sql 
WHERE is_active = TRUE;
WHERE is_active = FALSE;

WHERE with Calculated Values

You can use expressions in WHERE as long as they produce a boolean result.

Examples:

sql 
-- Users older than 2 * 10 years
SELECT *
FROM users
WHERE age > 2 * 10;
-- Users whose age plus 5 is at least 30
SELECT *
FROM users
WHERE age + 5 >= 30;

You can also apply functions:

sql 
-- Users whose name, in lower case, equals 'alice'
SELECT *
FROM users
WHERE LOWER(name) = 'alice';

Be careful with functions on indexed columns, because this can affect performance. That topic belongs to SQL performance.

WHERE with UPDATE and DELETE

The WHERE clause is also critical for UPDATE and DELETE.

sql 
-- Deactivate all users from a specific country
UPDATE users
SET is_active = FALSE
WHERE country = 'France';
-- Delete users younger than 13
DELETE FROM users
WHERE age < 13;

Rule:
Never run UPDATE or DELETE without a WHERE clause unless you really intend to affect all rows in the table.

Example of a dangerous statement:

sql 
-- This deactivates EVERY user
UPDATE users
SET is_active = FALSE;

Common Practical Examples

Assume more columns in users:

sql 
ALTER TABLE users
ADD COLUMN created_at TIMESTAMP,
ADD COLUMN email TEXT;

Some useful filters:

sql 
-- Users created after a certain date
SELECT *
FROM users
WHERE created_at >= '2024-01-01';
-- Users whose email is a Gmail address
SELECT *
FROM users
WHERE email LIKE '%@gmail.com';
-- Active adult users from US or UK
SELECT *
FROM users
WHERE is_active
  AND age >= 18
  AND country IN ('USA', 'UK');
-- Users with no email provided
SELECT *
FROM users
WHERE email IS NULL;

Summary

These tools let you precisely control which rows your SQL statements affect.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3506 / 24h
Impressum / Imprint
Datenschutz / Data Policy