Conditional Logic

Last modified: January 10, 2020 • Reading Time: 4 minutes

Postgres allows you to use conditional logic in your SQL queries. When the goal is simply to filter down results, using a WHERE clause is usually the best method.

(For an overview of WHERE clauses please reference this article: https://dataschool.com/learn-sql/where/ )

Conditional logic in SQL helps you to perform many different tasks:

To perform grouping (as shown in the examples below).
To deploy different mathematical operations depending on the value(s)
To perform boolean operations against your data.
To designate results based on specified text criteria.
To achieve a similar outcome as using WHERE, but with more clear and/or concise code.
To use conditional logic without the filtering effect of WHERE, thus retaining all the records.

Here are three different methods which can be used to execute conditional logic on your data.

CASE statement conditional logic:

Let’s say that we had the following data and wanted to group the results into regions based on their location. We could utilize a CASE statement to achieve this.

name	numfriends	location
Matt	300	San Francisco
Lisa	500	Los Angeles
Jeff	600	Chicago
Sarah	400	New York

SELECT name,
	numfriends,
	CASE
		WHEN location = 'San Francisco' THEN 'West'
		WHEN location = 'Los Angeles' THEN 'West'
		WHEN location = 'New York' THEN 'East'
		ELSE 'Other'
	END AS Region
FROM facebook;

The result would look like this and provide us the Regional grouping we desired.

name	numfriends	location	region
Matt	300	San Francisco	West
Lisa	500	Los Angeles	West
Jeff	600	Chicago	Other
Sarah	400	New York	East

IIF Conditional Logic:

Let’s now say that we wanted to group again but this time based on the number of friends. We could use IIF to group based on the quantities.

SELECT name,
	numfriends,
	location,
	IIF (numfriends >=500, 'Larger', 'Smaller') AS Size
FROM facebook;

Here are the results:

name	numfriends	location	size
Matt	300	San Francisco	Smaller
Lisa	500	Los Angeles	Larger
Jeff	600	Chicago	Larger
Sarah	400	New York	Smaller

Notice that in both of these past 2 examples, we are not merely filtering down the data, but rather returning all the data with new conditional information implemented on it.

UNION and WHERE Conditional Logic:

Finally, there may be situations in which it may be expedient to use more than 1 SELECT statement, each with WHERE clauses, and then UNION the results together. If we wanted to return all records with a location of either San Francisco or Los Angeles, it could be done like this:

SELECT name,
	numfriends,
	location,
FROM facebook
WHERE location = 'San Francisco'
UNION
	SELECT name,
		numfriends,
		location,
	FROM facebook
	WHERE location = 'Los Angeles';

The result would be:

name	numfriends	location
Matt	300	San Francisco
Lisa	500	Los Angeles

Given this simplicity of the sample data, in this case, perhaps it would make more sense to have just used a single SELECT statement with a single WHERE clause containing an OR condition. However, the UNION approach is good to know and to keep in mind. There may be times when addressing more complex situations that it may be applicable as a better approach.

Written by: Josiah Faas
Reviewed by: Matt David

CASE statement conditional logic:

IIF Conditional Logic:

UNION and WHERE Conditional Logic:

Created by