LuisLlamas Logo
filtrado-datos-where-sql

Data Filtering with WHERE in SQL

  • 4 min
  • Beginner

In the previous article, we learned how to select columns with SELECT. However, if we run a SELECT without anything else, the database returns all the rows from the table.

If your table has ten records, it’s fine. But if you’re working with Amazon’s sales table, trying to fetch all the rows will block your application and leave the server crying for a while💥.

To avoid this, we need to filter which rows we want to retrieve. This is where the WHERE clause comes in.

The WHERE Clause

The WHERE clause specifies a condition that each row must meet to be included in the result.

It works like a nightclub bouncer: it analyzes each row, checks if it meets the rule, and if so, lets it pass. If not, it discards it.

The basic syntax is:

SELECT Column1, Column2
FROM Table
WHERE Condition;
Copied!

For example, if we want to get only the customers who live in “Madrid”:

SELECT Name, LastName, City
FROM Customers
WHERE City = 'Madrid';
Copied!

The Execution Order

Although we write SELECT first and then WHERE, SQL Server does not execute it in that order. The order is:

FROM: Locates the table.

WHERE: Filters the rows.

SELECT: Processes the columns and displays the results.

Why is this important? Because it means you cannot use in the WHERE an alias you defined in the SELECT.

Common error example:

-- THIS WILL ERROR
SELECT 
    Name, 
    Salary * 1.10 AS SalaryWithBonus
FROM Employees
WHERE SalaryWithBonus > 2000; 
-- Error: 'SalaryWithBonus' does not exist yet when WHERE is executed.
Copied!

For it to work, you must repeat the operation in the filter:

-- THIS WORKS
SELECT 
    Name, 
    Salary * 1.10 AS SalaryWithBonus
FROM Employees
WHERE (Salary * 1.10) > 2000;
Copied!

Filtering by Different Data Types

Depending on the type of information we store, the way we write the condition changes slightly.

This is the simplest. We do not use quotes.

SELECT * FROM Products
WHERE Price = 100;
Copied!

As we saw in the basic syntax, in SQL, text literals must be enclosed in single quotes '.

SELECT * FROM Employees
WHERE LastName = 'Llamas';
Copied!

Remember that SQL Server can be Case Sensitive or not, depending on the configured Collation.

In most default installations, WHERE LastName = 'llamas' would work the same, but in strict environments it would not.

Dates also go between single quotes. The safest format to avoid confusion (is 01/02 the 1st of February or the 2nd of January?) is the standard ISO format: 'YYYYMMDD' or 'YYYY-MM-DD'.

SELECT * FROM Orders
WHERE OrderDate = '20230915'; -- September 15, 2023
Copied!

The Problem with NULLs (IS NULL)

In SQL, NULL means “absence of value” or “unknown”. NULL is not equal to anything, not even to another NULL.

If you try to do this, it will not work:

SELECT * FROM Customers
WHERE Phone = NULL; -- This always returns 0 rows
Copied!

Since we cannot know if an unknown value is equal to another, the logical comparison fails (returns UNKNOWN).

To filter nulls, we must use the special operators IS NULL and IS NOT NULL.

-- Correct way: Customers without a phone
SELECT * FROM Customers
WHERE Phone IS NULL;

-- Correct way: Customers who DO have a phone
SELECT * FROM Customers
WHERE Phone IS NOT NULL;
Copied!

Performance: Full Scan vs Index Seek

WHERE is not only useful for the application to receive less data; combined with indexes, it is the main tool for performance optimization.

  • Without WHERE: The engine has to read the entire table (Full Table Scan). If the table has 100 million rows, it will read all 100 million.
  • With WHERE: If we have an Index on the column we are filtering by (for example, ID), SQL Server can go directly to the row it’s looking for without reading the rest (Index Seek).

It’s the difference between reading an entire book to find a sentence or going directly to the index at the back and jumping to the correct page.

On this page