Filtering Query Results Using the WHERE Clause

Once you know how to retrieve data with a SELECT statement, the next step is learning how to filter the results. Often you don’t need every row from a table—you only want the rows that match specific criteria.

The WHERE clause allows you to define conditions that rows must meet in order to be included in the results.

Basic WHERE syntax:

SELECT column1, 
       column2
FROM schema_name.table_name
WHERE condition;

SELECT column1, 
       column2
FROM schema_name.table_name
WHERE condition;

Filtering by a Single Condition

For example, let’s return only the paintings that were created in Japan:

Table: Gallery.Paintings

Query:

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Japan';

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Japan';

Result:

Using Comparison Operators

The WHERE clause supports a variety of operators for filtering:

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

Example: Find paintings created after 1900:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE YearCreated > 1900;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE YearCreated > 1900;

Result:

Combining Conditions

You can filter on multiple conditions using logical operators:

• AND → all conditions must be true
• OR → at least one condition must be true
• NOT → excludes rows that match a condition
  • often combined with other keywords, for example NOT IN, NOT NULL, NOT EXISTS

Example: Find paintings created in the Netherlands and before 1700:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Netherlands'
	AND YearCreated < 1700;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Netherlands'
	AND YearCreated < 1700;

Result:

Using IN for Multiple Matches

When searching for multiple conditions in the same column you can use IN, making the statement cleaner and easier to read.

For example, this OR condition:

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Italy'
	OR CountryOfOrigin = 'Spain';

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Italy'
	OR CountryOfOrigin = 'Spain';

Returns the same result as this IN condition:

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy', 'Spain');

SELECT Title,
	   Artist,
	   CountryOfOrigin
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy', 'Spain');

Result:

Similar to equal = and not equal <>, you can use IN (values to include) and NOT IN (values to exclude).

💡Tip: There is no limit to the number of values you can use with IN and NOT IN. However, if you’re listing a large number of values there are better options such as joining to a temporary table, table variable, or using a subquery.

Using Wildcards with LIKE

When filtering text values, sometimes you don’t know the exact string. The LIKE operator allows you to search with wildcards:

• % → matches zero or more characters
• _ → matches a single character

Example: Find paintings where the title starts with “The”:

SELECT Title,
	   Artist
FROM Gallery.Paintings
WHERE Title LIKE 'The%';

SELECT Title,
	   Artist
FROM Gallery.Paintings
WHERE Title LIKE 'The%';

Result:

Filtering with NULL Values

Columns sometimes contain NULL, which represents missing or unknown data.

📌Important Note: NULL is not a value, it’s a placeholder for an unknown value. Therefore, we can’t use = to find records with a value equal to NULL or <> to filter out NULL records.

To filter for these cases, use IS NULL or IS NOT NULL.

Example: Find statues that don’t have an artist recorded:

Table: Gallery.Statues

SELECT Title,
	   CountryOfOrigin
FROM Gallery.Statues
WHERE Artist IS NULL;

SELECT Title,
	   CountryOfOrigin
FROM Gallery.Statues
WHERE Artist IS NULL;

Result:

Key Takeaways

• The WHERE clause filters rows based on conditions you specify.
• Use comparison operators (=, <, >, <>) to evaluate numeric or text values.
• Combine multiple conditions with AND, OR, NOT, and IN.
• Use LIKE with % and _ for pattern matching in text.
• Handle missing data with IS NULL and IS NOT NULL.

Learning how to filter data is essential to working with SQL. With WHERE, you can narrow your results to exactly what you need before moving on to sorting and grouping.