Understanding the WHERE Clause in SQL

Introduction

The WHERE clause is a fundamental part of SQL that filters rows based on specified conditions. While the SELECT statement chooses which columns to display, WHERE determines which records appear in the result set.

Basic Syntax

SELECT *
FROM table_name
WHERE column_name operator value;

• column_name – the field you want to evaluate.
• operator – a comparison or logical operator.
• value – the constant or expression to compare against.

Comparison Operators

Examples - WHERE first_name = 'Leslie' returns only rows where the first name is Leslie. - WHERE salary > 50000 excludes salaries exactly equal to 50,000. - WHERE salary >= 50000 includes both 50,000 and higher salaries. - WHERE salary < 50000 returns rows with salaries below 50,000; <= includes the 50,000 threshold.

Working with Different Data Types

• Strings – enclosed in single quotes, e.g., WHERE gender = 'female'.
• Numbers – used directly, e.g., WHERE age = 44.
• Dates – follow the YYYY-MM-DD format, e.g., WHERE birth_date > '1985-01-01'.

Logical Operators

Logical operators combine multiple conditions: - AND – all conditions must be true. - OR – at least one condition must be true. - NOT – negates a condition.

Combined Example

WHERE birth_date > '1985-01-01' AND gender = 'male'

Returns male employees born after 1985.

Using OR:

WHERE birth_date > '1985-01-01' OR gender <> 'male'

Returns anyone born after 1985 or anyone who is not male (i.e., female).

Operator Precedence (PEMDAS for Logic)

When mixing AND, OR, and NOT, SQL follows a precedence order similar to arithmetic: 1. NOT 2. AND 3. OR Parentheses can override this order to group conditions explicitly.

Complex Example with Parentheses

WHERE (first_name = 'Leslie' AND age = 44) OR age > 55

• The part inside parentheses must satisfy both name and age.
• Any row with age greater than 55 is also returned, regardless of name.

The LIKE Operator for Pattern Matching

LIKE allows flexible string searches using two wildcards: - % – matches any sequence of characters (including none). - _ – matches exactly one character.

Common Patterns - WHERE first_name LIKE 'Jer%' – names starting with "Jer" (e.g., Jerry). - WHERE first_name LIKE '%er%' – names containing "er" anywhere. - WHERE first_name LIKE 'A%' – names beginning with "A" (April, Andy). - WHERE first_name LIKE 'A__' – names starting with "A" followed by exactly two characters (e.g., Andy). - WHERE first_name LIKE 'A___%' – names starting with "A" and at least three characters after it (captures April).

LIKE works with non‑string columns when they are cast to text, such as dates:

WHERE birth_date LIKE '1989%'

Returns rows where the birth year is 1989.

Summary of Key Points

• Use WHERE to filter rows; combine conditions with AND, OR, NOT.
• Comparison operators (=, >, <, >=, <=, !=) work with numbers, strings, and dates.
• LIKE with % and _ enables pattern‑based searches, useful for partial matches.
• Parentheses control logical precedence and make complex queries readable.

Next Steps

The upcoming lesson will cover GROUP BY and ORDER BY clauses, which allow aggregation and sorting of query results.

The WHERE clause is the gateway to precise data retrieval in SQL—by mastering comparison operators, logical connectors, and pattern matching with LIKE, you can filter rows exactly the way you need without scanning the entire table.