WHERE

Overview

The WHERE clause is used to filter records based on specified conditions. It allows you to extract only those records that fulfill a particular criterion.

WHERE is one of the most commonly used SQL clauses and is essential for retrieving specific data from large tables.

Basic Syntax

The WHERE clause follows the SELECT and FROM statements:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison Operators

WHERE clause uses comparison operators to specify conditions:

Examples

Example 1: Equality Condition

SELECT name, email
FROM Customers
WHERE city = 'New York';

Example 2: Comparison Condition

SELECT product_name, price
FROM Products
WHERE price > 50;

Example 3: Multiple Conditions with AND

SELECT *
FROM Orders
WHERE order_date >= '2024-01-01'
  AND total_amount > 100;

Example 4: Using OR

SELECT name, city
FROM Customers
WHERE city = 'New York' OR city = 'Los Angeles';

Text Values

When comparing text values, use single quotes around the string:

SELECT *
FROM Customers
WHERE name = 'John Doe';

-- Case-sensitive in most databases
SELECT *
FROM Products
WHERE category = 'Electronics';

Numeric Values

Numeric values do not need quotes:

SELECT *
FROM Products
WHERE price > 100
  AND stock_quantity < 50;

Date Values

Date values are typically enclosed in quotes and follow a standard format:

SELECT *
FROM Orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2024-02-01';

-- Format varies by database system
-- SQL Server: '2024-01-01'
-- Oracle: DATE '2024-01-01'
-- MySQL: '2024-01-01'

WHERE with Other Clauses

WHERE with ORDER BY

SELECT name, price
FROM Products
WHERE category = 'Electronics'
ORDER BY price DESC;

WHERE with JOIN

SELECT c.name, o.order_date, o.total_amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';

Common WHERE Patterns

Checking for NULL Values

Use IS NULL or IS NOT NULL to check for NULL values (see NULL Values tutorial):

-- Find records with NULL values
SELECT *
FROM Customers
WHERE email IS NULL;

-- Find records without NULL values
SELECT *
FROM Customers
WHERE email IS NOT NULL;

Pattern Matching with LIKE

Use LIKE for pattern matching (covered in LIKE tutorial):

SELECT *
FROM Customers
WHERE name LIKE 'John%';

Value Lists with IN

Use IN to match against a list of values (covered in IN tutorial):

SELECT *
FROM Products
WHERE category IN ('Electronics', 'Computers', 'Phones');

Range Matching with BETWEEN

Use BETWEEN to match values within a range (covered in BETWEEN tutorial):

SELECT *
FROM Products
WHERE price BETWEEN 50 AND 100;

Best Practices

• Use indexes: WHERE conditions benefit greatly from indexes on filtered columns
• Be specific: Use precise conditions to reduce the number of rows processed
• Use parameters: For dynamic queries, use parameterized queries to prevent SQL injection
• Test conditions: Verify your WHERE conditions return expected results
• Consider performance: Simple conditions are faster than complex ones

Common Use Cases

• Filtering customer records by location
• Finding products in a specific price range
• Retrieving orders from a date range
• Searching for records matching specific criteria
• Combining multiple conditions to narrow results

Next Steps

Learn more about combining conditions with AND, OR, NOT, pattern matching with LIKE, or sorting results with ORDER BY.