Using the WHERE Clause Effectively: Common SQL Operators and Their Usage

The WHERE clause is a fundamental part of SQL queries that allows us to filter data based on specific conditions. Understanding how to use various operators within the WHERE clause can significantly enhance our ability to retrieve precisely the data we need.

This article explores some of the operators that are most commonly used with the WHERE clause.

Sample Data

Run the following SQL script to create the tables and populate them with enough data to demonstrate the examples on this page:

-- Create artists table
CREATE TABLE artists (
    artist_id INT PRIMARY KEY,
    name VARCHAR(100),
    genre VARCHAR(50),
    formed_year INT
);

-- Create albums table
CREATE TABLE albums (
    album_id INT PRIMARY KEY,
    artist_id INT,
    title VARCHAR(100),
    release_year INT,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Create songs table
CREATE TABLE songs (
    song_id INT PRIMARY KEY,
    album_id INT,
    title VARCHAR(100),
    duration INT,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Insert sample data
INSERT INTO artists (artist_id, name, genre, formed_year) VALUES
(1, 'The Triffids', 'Alternative Rock', 1978),
(2, 'Died Pretty', 'Alternative Rock', 1983),
(3, 'The Go-Betweens', 'Indie Rock', 1977),
(4, 'The Scientists', 'Post-Punk', 1978),
(5, 'The Moles', 'Indie Pop', 1989);

INSERT INTO albums (album_id, artist_id, title, release_year) VALUES
(1, 1, 'Born Sandy Devotional', 1986),
(2, 2, 'Doughboy Hollow', 1991),
(3, 3, '16 Lovers Lane', 1988),
(4, 4, 'Blood Red River', 1983),
(5, 5, 'Untune the Sky', 1991);

INSERT INTO songs (song_id, album_id, title, duration) VALUES
(1, 1, 'Wide Open Road', 288),
(2, 1, 'Stolen Property', 315),
(3, 2, 'D.C.', 234),
(4, 2, 'Sweetheart', 249),
(5, 3, 'Streets of Your Town', null),
(6, 3, 'Was There Anything I Could Do?', 221),
(7, 4, 'Set It On Fire', 183),
(8, 4, 'Swampland', null),
(9, 5, 'What''s the New Mary Jane', 248),
(10, 5, 'Bury Me Happy', 202);

Now that we’ve got data, let’s run some SELECT statements that use different operators in the WHERE clause.

Equality Operator (`=`)

Let’s start with the basic equality operator. This operator is used to match exact values.

For example, to find all songs by The Triffids:

SELECT s.title
FROM songs s
JOIN albums a ON s.album_id = a.album_id
JOIN artists ar ON a.artist_id = ar.artist_id
WHERE ar.name = 'The Triffids';

Result:

title          
---------------
Wide Open Road 
Stolen Property

But as mentioned, it has to be an exact match. Therefore, the following just won’t cut it:

SELECT s.title
FROM songs s
JOIN albums a ON s.album_id = a.album_id
JOIN artists ar ON a.artist_id = ar.artist_id
WHERE ar.name = 'Triffids';

Result:

0 row(s) returned

In this case I didn’t provide the full name. I only provided part of the name, which meant that there was no match. If we wanted to provide only part of the name (for example, if we don’t know the full name), then we could use the LIKE operator (we’ll cover this later).

Inequality Operator (`<>`)

The inequality operator selects all records that do not match the specified value.

So for example, to find all artists not formed in the 1978:

SELECT name
FROM artists
WHERE formed_year <> 1978;

Result:

name           
---------------
Died Pretty    
The Go-Betweens
The Moles

Greater Than (`>`) and Less Than (`<`) Operators

These operators are used for numerical comparisons.

The Greater Than (>) operator returns TRUE if the left operand has a value higher than the right operand; otherwise, it returns FALSE.
It’s the opposite with the Less Than Operator (<) – it returns TRUE if the left operand has a value lower than the right operand; otherwise, the result is FALSE.

To find albums released after 1986:

SELECT title, release_year
FROM albums
WHERE release_year > 1986;

Result:

title            release_year
---------------  ------------
Doughboy Hollow  1991        
16 Lovers Lane   1988        
Untune the Sky   1991

Conversely, we can switch it over to the Less Than Operator to return albums released before 1986:

SELECT title, release_year
FROM albums
WHERE release_year < 1986;

Result:

title            release_year
---------------  ------------
Blood Red River  1983

It might be tempting to assume that between these two examples, we returned all albums. But that’s not necessarily the case. The first example retrieved all albums after a certain year, and the second example got all albums before that year, but none of them retrieved any albums from the year itself – 1986. As it happens, our database has an album that was released in 1986. None of our queries returned that album.

If this is an issue, then we can use one of the following operators.

Greater Than or Equal To (`>=`) and Less Than or Equal To (`<=`) Operators

These operators are similar to the previous ones, except that they include the specified value in the results.

The Greater Than or Equal To (>=) operator returns TRUE if the left operand has a value greater than or equal to the right operand; otherwise, it returns FALSE.
The Less Than or Equal To (<=) operator returns TRUE if the left operand has a value less than or equal to the right operand; otherwise, it returns FALSE.

Let’s rewrite the previous two examples to use these operators:

SELECT title, release_year
FROM albums
WHERE release_year >= 1986;

Result:

title                  release_year
---------------------  ------------
Born Sandy Devotional  1986        
Doughboy Hollow        1991        
16 Lovers Lane         1988        
Untune the Sky         1991

And to the <= operator:

SELECT title, release_year
FROM albums
WHERE release_year <= 1986;

Result:

title                  release_year
---------------------  ------------
Born Sandy Devotional  1986        
Blood Red River        1983

This time, both examples returned albums from 1986 in their result set.

`BETWEEN` Operator

The BETWEEN operator selects values within a given range.

To find albums released between 1985 and 1990:

SELECT title, release_year
FROM albums
WHERE release_year BETWEEN 1985 AND 1990;

Result:

title                  release_year
---------------------  ------------
Born Sandy Devotional  1986        
16 Lovers Lane         1988

`IN` Operator

The IN operator allows us to specify multiple values in a WHERE clause.

To find artists in multiple specific genres:

SELECT name, genre
FROM artists
WHERE genre IN ('Alternative Rock', 'Indie Rock');

Result:

name             genre           
---------------  ----------------
The Triffids     Alternative Rock
Died Pretty      Alternative Rock
The Go-Betweens  Indie Rock

It’s another way of doing the following:

SELECT name, genre
FROM artists
WHERE genre = 'Alternative Rock' OR genre = 'Indie Rock';

The IN operator can be particularly useful for when we have a large list of values. It saves us from having to do many OR operations. We can simply provide a list of values inside parentheses, preceded by the IN operator.

`LIKE` Operator

The LIKE operator is used for pattern matching with wildcards.

For example, to find all artists whose names start with The:

SELECT name
FROM artists
WHERE name LIKE 'The%';

Result:

name           
---------------
The Triffids   
The Go-Betweens
The Scientists 
The Moles

`IS NULL` and `IS NOT NULL` Operators

These operators check for NULL values (or the absence of NULL values).

To find all songs without a duration (or more accurately, have a NULL value in their duration column):

SELECT title
FROM songs
WHERE duration IS NULL;

Result:

title               
--------------------
Streets of Your Town
Swampland

Conversely, we can use IS NOT NULL to find songs that do have a duration:

SELECT title
FROM songs
WHERE duration IS NOT NULL;

Result:

title                         
------------------------------
Wide Open Road                
Stolen Property               
D.C.                          
Sweetheart                    
Was There Anything I Could Do?
Set It On Fire                
What's the New Mary Jane      
Bury Me Happy

`AND` Operator

The AND operator combines multiple conditions.

To find all songs longer than 4 minutes from albums released after 1985:

SELECT s.title, s.duration, a.release_year
FROM songs s
JOIN albums a ON s.album_id = a.album_id
WHERE s.duration > 240 AND a.release_year > 1985;

Result:

title                     duration  release_year
------------------------  --------  ------------
Wide Open Road            288       1986        
Stolen Property           315       1986        
Sweetheart                249       1991        
What's the New Mary Jane  248       1991

`OR` Operator

The OR operator selects records that meet at least one of the specified conditions.

To find artists formed in either 1983 or 1989:

SELECT name, formed_year
FROM artists
WHERE formed_year = 1983 OR formed_year = 1989;

Result:

name         formed_year
-----------  -----------
Died Pretty  1983       
The Moles    1989

More Operators

Here’s a full list of operators that we can use in our SQL queries.

Sample Data

Equality Operator (=)

Inequality Operator (<>)

Greater Than (>) and Less Than (<) Operators

Greater Than or Equal To (>=) and Less Than or Equal To (<=) Operators

BETWEEN Operator

IN Operator

LIKE Operator

IS NULL and IS NOT NULL Operators

AND Operator

OR Operator

More Operators

Equality Operator (`=`)

Inequality Operator (`<>`)

Greater Than (`>`) and Less Than (`<`) Operators

Greater Than or Equal To (`>=`) and Less Than or Equal To (`<=`) Operators

`BETWEEN` Operator

`IN` Operator

`LIKE` Operator

`IS NULL` and `IS NOT NULL` Operators

`AND` Operator

`OR` Operator