SQL WHERE Clause - MSDN Documentation

SQL Querying: The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified condition. It filters records based on a condition specified using comparison operators, logical operators, and other SQL functions.

Syntax

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

Conditions

A condition can be a combination of one or more columns and can include comparison operators, logical operators, and other SQL functions.

Comparison Operators

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> or != Not equal to

Logical Operators

AND: Displays a record if ALL of the conditions separated by AND are TRUE.
OR: Displays a record if ANY of the conditions separated by OR is TRUE.
NOT: Reverses the meaning of the logical operator it precedes.

Other Useful Operators

BETWEEN: Selects values within a given range.
LIKE: Searches for a specified pattern in a column.
IN: Selects values from a list.
IS NULL: Selects records where the column is NULL.
IS NOT NULL: Selects records where the column is NOT NULL.

Examples

Example 1: Selecting records with a specific value

Retrieve all customers from the 'Customers' table who are located in 'London'.

SELECT CustomerName, ContactName
FROM Customers
WHERE City = 'London';

Example 2: Using the AND operator

Retrieve all customers from 'USA' who have a postal code greater than 50000.

SELECT CustomerName, Country, PostalCode
FROM Customers
WHERE Country = 'USA' AND PostalCode > '50000';

Example 3: Using the OR operator

Retrieve all customers from 'Germany' or 'France'.

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'France';

Example 4: Using the BETWEEN operator

Retrieve all products with a price between 10 and 50.

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 50;

Example 5: Using the LIKE operator

Retrieve all customers whose names start with 'A'.

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%';

Example 6: Using the IN operator

Retrieve all customers who are from 'Canada', 'Mexico', or 'USA'.

SELECT CustomerName, Country
FROM Customers
WHERE Country IN ('Canada', 'Mexico', 'USA');

Example 7: Checking for NULL values

Retrieve all customers who do not have a region specified (Region is NULL).

SELECT CustomerName, Region
FROM Customers
WHERE Region IS NULL;

Combining Operators

You can combine multiple operators to create more complex filtering criteria. The order of evaluation can be controlled using parentheses.

Example 8: Combining AND and OR

Retrieve customers from 'UK' who are either from 'London' or have a postal code greater than 5000.

SELECT CustomerName, Country, City, PostalCode
FROM Customers
WHERE Country = 'UK' AND (City = 'London' OR PostalCode > '5000');

Tables

The WHERE clause can reference columns from tables specified in the FROM and JOIN clauses.

Column	Description
`Customers`	Contains customer information including `CustomerID`, `CustomerName`, `ContactName`, `Address`, `City`, `PostalCode`, `Country`, `Region`.
`Products`	Contains product information including `ProductID`, `ProductName`, `Price`.

SQL Documentation