Filtering Data with SSMS - SQL Server Management Studio

Filtering Data with the WHERE Clause

The WHERE clause is fundamental to retrieving specific subsets of data from your tables. It allows you to specify conditions that rows must meet to be included in the result set.

Basic Filtering with Operators

You can use various comparison operators to define your filter conditions:

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

Example: Selecting customers from a specific city


SELECT CustomerID, CompanyName, ContactName, City
FROM Customers
WHERE City = 'London';

Example: Finding products with a price greater than 50


SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50.00;

Using Logical Operators

Combine multiple conditions using logical operators:

AND: Both conditions must be true.
OR: At least one of the conditions must be true.
NOT: Reverses the result of a condition.

Example: Finding customers in 'USA' who are also from 'Washington'


SELECT CustomerID, CompanyName, Country, Region
FROM Customers
WHERE Country = 'USA' AND Region = 'WA';

Example: Finding orders placed before 1997 OR on or after 2000


SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate < '1997-01-01' OR OrderDate >= '2000-01-01';

Specialized Filtering Operators

SQL provides other useful operators for filtering:

BETWEEN ... AND ...: Selects values within a range (inclusive).
IN (value1, value2, ...): Selects values that match any value in a list.
LIKE: Searches for a specified pattern in a column. Wildcards are used with LIKE:
- %: Represents zero, one, or multiple characters.
- _: Represents a single character.
IS NULL: Checks if a column has a NULL value.
IS NOT NULL: Checks if a column does not have a NULL value.

Example: Finding orders placed between two dates


SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31';

Example: Finding customers from 'Germany', 'France', or 'Spain'


SELECT CustomerID, CompanyName, Country
FROM Customers
WHERE Country IN ('Germany', 'France', 'Spain');

Example: Finding products whose names start with 'Ch'


SELECT ProductID, ProductName
FROM Products
WHERE ProductName LIKE 'Ch%';

Example: Finding customers with a contact name like 'A%'


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

Example: Finding customers with a NULL region


SELECT CustomerID, CompanyName, Region
FROM Customers
WHERE Region IS NULL;

Tip:

When using LIKE with patterns, be mindful of performance. Wildcards at the beginning of the pattern (e.g., %word) can often lead to table scans, which are slower than patterns that start with literal characters (e.g., word%).

Using Parentheses for Complex Logic

You can use parentheses to group conditions and control the order of operations, especially when mixing AND and OR.

Example: Finding customers from 'USA' or 'Canada' who also have a region of 'WA' or 'BC'


SELECT CustomerID, CompanyName, Country, Region
FROM Customers
WHERE (Country = 'USA' OR Country = 'Canada')
  AND (Region = 'WA' OR Region = 'BC');

Mastering the WHERE clause is crucial for efficient data retrieval and analysis within SQL Server Management Studio.