Filtering Data with the WHERE Clause
The WHERE clause in SQL is used to extract only those records that fulfill a specified condition. It filters records that are returned by a SELECT statement, an UPDATE statement, or a DELETE statement.
Basic Filtering
The most common way to filter data is by using comparison operators.
Comparison Operators
=: Equal to>: Greater than<: Less than>=: Greater than or equal to<=: Less than or equal to<>or!=: Not equal to
Select all customers from 'USA':
SELECT * FROM Customers
WHERE Country = 'USA';Select products with a price greater than 50:
SELECT ProductName, Price
FROM Products
WHERE Price > 50;Logical Operators
You can combine conditions using logical operators like AND, OR, and NOT.
AND: Displays a record if ALL conditions are trueOR: Displays a record if ANY of the conditions is trueNOT: Reverses the effect of the logical operator
Select customers from 'USA' who live in 'New York':
SELECT * FROM Customers
WHERE Country = 'USA' AND City = 'New York';Select customers from 'USA' OR 'Canada':
SELECT * FROM Customers
WHERE Country = 'USA' OR Country = 'Canada';Select customers NOT from 'Germany':
SELECT * FROM Customers
WHERE NOT Country = 'Germany';BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive.
Select products with a price between 10 and 50:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
%: Represents zero, one, or multiple characters_: Represents a single character
Select customers whose name starts with 'A':
SELECT * FROM Customers
WHERE CustomerName LIKE 'A%';Select customers whose name ends with 's':
SELECT * FROM Customers
WHERE CustomerName LIKE '%s';Select customers whose name contains 'or':
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';Select customers whose name starts with 'A' and is at least 3 characters long:
SELECT * FROM Customers
WHERE CustomerName LIKE 'A__%';IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.
Select customers who are from 'Germany', 'France', or 'UK':
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');IS NULL and IS NOT NULL
Used to test for empty or non-empty values in a column.
Select customers who have no email address:
SELECT * FROM Customers
WHERE Email IS NULL;Select customers who have an email address:
SELECT * FROM Customers
WHERE Email IS NOT NULL;