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.
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.
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:
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:
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.