DATA ANALYSIS  

Filtering Tables with WHERE

In most applications we rarely want to retrieve all rows from a table. Instead, we usually need only records that satisfy specific conditions. In SQL, filtering rows is done using the WHERE clause together with the SELECT statement.

In This Article You Will Learn:

  • filter text using comparison operators, LIKE and %
  • compare dates and timestamps
  • filter numeric or date ranges using BETWEEN
  • detect missing values using IS NULL
  • handle different data types using casting
  • combine multiple conditions using AND and OR

Basic syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Note that all examples presented below are designed to work with the web shop database that you can query in the Practice tab. For practice purposes, you can copy the following queries, paste them into the Practice tab, and view the results. Do not hesitate to try your own modifications of the provided queries as well.

Example using the web shop database:

SELECT firstname, lastname, email FROM customer WHERE id < 105;

This query retrieves only customers whose id is less than 105.

Filtering Text Values

Text columns can be filtered using standard comparison operators or pattern matching.

Common comparison operators include:

  • = equal
  • <> not equal
  • > greater than
  • < less than
  • >= greater than or equal
  • <= less than or equal

Exact Text Match

SELECT firstname, lastname FROM customer WHERE firstname = 'John';

This query returns customers whose first name is exactly John.

Pattern Matching with LIKE and %

The LIKE operator allows filtering text using wildcards. The most common wildcard is %, which represents any sequence of characters.

Starts with

SELECT firstname, lastname FROM customer WHERE firstname LIKE 'A%';

Returns customers whose first name starts with A.

Ends with

SELECT firstname, lastname FROM customer WHERE email LIKE '%gmail.com';

Returns customers using Gmail addresses.

Contains text

SELECT name FROM product WHERE name LIKE '%Bag%';

Returns products whose name contains the word Bag anywhere in the text.

Filtering Dates and Timestamps

Many databases store temporal data such as dates or timestamps. In the webshop schema, the customer table contains dateofbirth, and several tables contain created timestamps.

Example filtering customers born after a specific date:

SELECT firstname, lastname, dateofbirth FROM customer WHERE dateofbirth > '1990-01-01';

Example filtering recently created customers:

SELECT id, firstname, created FROM customer WHERE created >= '2024-01-01';

PostgreSQL also allows explicit casting when necessary:

SELECT id, created FROM customer WHERE created::date = '2024-05-01';

Here the timestamp is converted to a date before comparison.

Filtering with BETWEEN

The BETWEEN operator filters values inside a specific range.

Syntax:

column BETWEEN value1 AND value2

Example filtering customer birth dates:

SELECT firstname, lastname, dateofbirth FROM customer WHERE dateofbirth BETWEEN '1980-01-01' AND '1990-12-31';

This returns customers born in the 1980s.

Numeric Example

SELECT id, discountinpercent FROM article WHERE discountinpercent BETWEEN 10 AND 30;

Returns articles with discounts between 10% and 30%.

Filtering NULL Values

In SQL, missing values are represented by NULL. They cannot be compared using =.

Example:

SELECT firstname, lastname FROM customer WHERE email IS NULL;

Returns customers whose email address is not stored.

Handling Different Data Types

Sometimes comparisons involve values with different data types, such as comparing numbers stored as integers with decimal values or text values.

Integer vs Float

Example: filtering articles whose tax rate is greater than 0.2.

SELECT id, taxrate FROM article WHERE taxrate > 0.2;

PostgreSQL automatically converts compatible numeric types when possible.

Explicit Type Casting (PostgreSQL)

If the types are incompatible, explicit casting may be required. PostgreSQL supports casting using ::type.

Example:

SELECT id FROM article WHERE discountinpercent::numeric > 10.5;

Here an integer value is cast to numeric so it can be compared with a decimal value.

Comparing Text with Numbers

Sometimes numeric values are stored as text. In such cases, conversion is required.

Example:

SELECT id FROM article WHERE originalprice::numeric > 100;

This converts the money or text-like value into a numeric value before comparison.

Applying Multiple Conditions (AND / OR)

Queries often require more than one filtering condition.

Using AND

AND requires that all conditions must be true.

Example:

SELECT firstname, lastname FROM customer WHERE gender = 'female' AND dateofbirth > '1995-01-01';

This returns female customers born after 1995.

Using OR

OR requires that at least one condition must be true.

Example:

SELECT name FROM product WHERE category = 'Apparel' OR category = 'Traditional';

This query retrieves products belonging to either category.

Precedence of AND and OR in WHERE Clauses

When multiple logical operators are used in a WHERE clause, SQL evaluates them according to a defined order of precedence. Understanding this order is important because it determines how conditions are interpreted and which rows are returned.

In SQL, the logical operators follow this precedence:

  • NOT
  • AND
  • OR

This means that AND conditions are evaluated before OR conditions unless parentheses explicitly change the order.

Example Without Parentheses

Consider the following query:

SELECT firstname, lastname FROM customer WHERE gender = 'female' AND dateofbirth > '1995-01-01' OR firstname LIKE 'A%';

Because AND has higher precedence than OR, SQL interprets this query as if it were written like this:

SELECT firstname, lastname FROM customer WHERE (gender = 'female' AND dateofbirth > '1995-01-01') OR firstname LIKE 'A%';

This means the query will return:

  • female customers born after 1995
  • OR any customer whose name starts with A

Even if that person is not female or does not meet the date condition.

Example With Parentheses

If we want a different logical grouping, we must use parentheses.

Example:

SELECT firstname, lastname FROM customer WHERE gender = 'female' AND (dateofbirth > '1995-01-01' OR firstname LIKE 'A%');

Now SQL evaluates the expression inside parentheses first. This query returns only:

  • customers who are female and who either: were born after 1995 or have a name starting with A

The parentheses therefore change the meaning of the query.

Why Parentheses Are Recommended

Although SQL has defined operator precedence, relying on it can make queries harder to read and easier to misunderstand. For example, the following query technically works:

WHERE condition1 AND condition2 OR condition3

But many developers prefer to write:

WHERE (condition1 AND condition2) OR condition3

because the logic becomes immediately clear. Using parentheses:

  • improves readability
  • prevents logical errors
  • makes queries easier to maintain

Contact details:

+48 790-430-860

analysislessons@gmail.com