SQL WHERE

Basic WHERE Clause

SELECT * FROM customers WHERE city = 'London';

Filters records where the city is London.

Comparison Operators

SQL comparison operators are used in the WHERE clause to filter records based on specific conditions. These operators compare column values against a constant or another expression and return rows that satisfy the condition.

• =: Equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• <> or !=: Not equal to
• BETWEEN ... AND ...: Within a range (inclusive)
• IN (...): Matches any value in a list
• LIKE: Matches a pattern (used with wildcards)
• IS NULL: Checks for NULL values

Examples:

-- Products with price equal to 100
SELECT * FROM products WHERE price = 100;

-- Products with price not equal to 100
SELECT * FROM products WHERE price <> 100;

-- Products priced at 100 or more
SELECT * FROM products WHERE price >= 100;

-- Products with names starting with "A"
SELECT * FROM products WHERE name LIKE 'A%';

-- Products with prices between 100 and 500
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Products in specific categories
SELECT * FROM products WHERE category IN ('Electronics', 'Books');

-- Products with unknown (NULL) supplier
SELECT * FROM products WHERE supplier_id IS NULL;

These operators help you narrow down search results to match exactly what you need in real-world data queries. Use them to build dynamic and powerful filters.

Logical Operators

Logical operators are used to combine multiple conditions in the WHERE clause. These operators return either true or false based on the logical relationship between the conditions.

• AND: Returns true only if both conditions are true.
• OR: Returns true if at least one of the conditions is true.
• NOT: Reverses the result of the condition (true becomes false, false becomes true).

Examples:

-- Customers from either Berlin or Paris
SELECT * FROM customers 
WHERE city = 'Berlin' OR city = 'Paris';

-- Customers from the USA who are at least 21 years old
SELECT * FROM customers 
WHERE country = 'USA' AND age >= 21;

-- Customers not from Berlin
SELECT * FROM customers 
WHERE NOT city = 'Berlin';

Logical operators allow you to build more flexible and complex conditions by combining multiple comparisons. Use parentheses () to control evaluation order if needed.

BETWEEN and IN

The BETWEEN operator selects values within a given range, inclusive of the boundary values. The IN operator checks if a value matches any value in a list of specified values. These operators are useful for simplifying complex WHERE conditions.

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'IT');

LIKE and Wildcards

The LIKE operator is used for pattern matching in text. Use the percent sign % to match any sequence of characters and the underscore _ to match a single character.

SELECT * FROM customers WHERE name LIKE 'A%';  -- Starts with A
SELECT * FROM customers WHERE name LIKE '_r%'; -- Second letter is r

NULL Checking

In SQL, NULL represents a missing or unknown value. Use IS NULL to find records with no value in a column, and IS NOT NULL to find records where a value exists.

SELECT * FROM contacts WHERE email IS NULL;
SELECT * FROM contacts WHERE phone IS NOT NULL;

Operator Precedence

SQL evaluates AND before OR unless parentheses are used to change the order of evaluation. Parentheses help make complex conditions clearer and ensure the logic is applied as intended.

SELECT * FROM orders 
WHERE status = 'active' AND (total > 100 OR priority = 'high');

Best Practices

• Use parentheses for clarity with mixed conditions
• Use IS NULL/IS NOT NULL for null checks
• Avoid unfiltered SELECT queries in large tables

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic