PostgreSQL ALL Operator
Introduction
This tutorial will explain how to use the PostgreSQL ALL operator and provide examples to help obtain a working understanding of the function. The PostgreSQL ALL operator function allows for the selection of all the records of a SELECT STATEMENT and will compare the value to every value within the query result set. This is a very useful function to find specific values within a table.
Prerequisites for using the PostgreSQL ALL Operator
- The PostgreSQL server must be properly installed, configured and running. Download PostgreSQL for Linux and Windows systems here
How to use the PostgreSQL ALL Operator Function
It is important to note that the ALL operator command must be preceded by a comparison operator. Following is the basic syntax of the PostgreSQL ALL operator commands:
1 2 3 | SELECT [target_column_name... | first_expression ] FROM [target_table_name] WHERE second_expression desired_comparison_operator {ALL | ANY | SOME} ( subquery ) |
Following is a breakdown of the commands:
First, the column name within the table must be specified. This is followed by an expression that consists of a SQL query to compare values within the table and/or perform some basic calculations.
Next, the name of the table, where the operation will be performed, must be specified. A WHERE expression is then used to compare a table column to every value in the ALL operator sub-query. The ALL operator will evaluate to TRUE if the entire row matches the expression.
Third, the desired_comparison_operator will compare the second_expression to the sub-query. Note that this is a must-use standard comparison operator.
How to Create a Sample Data Set
This example will use a gaming application. Create two separate tables, a player and a player_stats table, that will be used to keep track of the player’s details in a database.
First, create the player table by executing the following commands:
1 2 3 4 5 6 7 | CREATE TABLE player ( player_id SERIAL PRIMARY KEY, player_name TEXT, playing_hours INTEGER, rating INTEGER, rank INTEGER ); |
Now add the following player information to the table:
1 2 3 4 5 6 7 8 9 | INSERT INTO player (player_name, playing_hours, rating, rank) VALUES ('teamsolo', 200, 5, 1), ('armada', 230, 5, 1), ('lonewolf', 230, 4, 1), ('knownsecret', 230, 4, 2), ('winterwolf', 230, 4, 2), ('moltenice', 230, 4, 2), ('silentkiller', 240, 3, 3), ('casper', 240, 3, 3); |
The results should resemble the following:
1 2 3 4 5 6 7 8 9 10 11 | player_id | player_name | playing_hours | rating | rank -----------+--------------+---------------+--------+------ 1 | teamsolo | 200 | 5 | 1 2 | armada | 230 | 5 | 1 3 | lonewolf | 200 | 4 | 1 4 | knownsecret | 230 | 4 | 2 5 | winterwolf | 210 | 4 | 2 6 | moltenice | 210 | 4 | 2 7 | silentkiller | 230 | 3 | 1 8 | casper | 230 | 3 | 2 (8 ROWS) |
Now create the player_stats table with the following commands:
1 2 3 4 5 6 | CREATE TABLE player_stats ( stats_id SERIAL PRIMARY KEY, player_id INTEGER, no_of_kills INTEGER, no_of_deaths INTEGER ); |
Now add the following information to the stats table:
1 2 3 4 5 6 7 8 9 | INSERT INTO player_stats (player_id, no_of_kills, no_of_deaths) VALUES (1, 25000, 370), (2, 24500, 375), (3, 23000, 376), (4, 23000, 400), (5, 23000, 400), (6, 23000, 400), (7, 22000, 450), (8, 22000, 450); |
The results should resemble the following:
1 2 3 4 5 6 7 8 9 10 11 | stats_id | player_id | no_of_kills | no_of_deaths ----------+-----------+-------------+-------------- 1 | 1 | 25000 | 370 2 | 2 | 24500 | 375 3 | 3 | 23000 | 376 4 | 4 | 23000 | 400 5 | 5 | 23000 | 400 6 | 6 | 23000 | 400 7 | 7 | 22000 | 450 8 | 8 | 22000 | 450 (8 ROWS) |
How to use the PostgreSQL ALL and SELECT Statements
This section will provide examples of how to use the PostgreSQL ALL operator function with SELECT statement. The base commands are as follows:
1 2 3 | SELECT ALL column_name FROM the_table_name WHERE the_condition(s); |
Now, using the above base commands, locate the name of all players within the table player as follows:
1 2 3 | SELECT ALL player_name FROM player WHERE TRUE; |
The results should resemble the following:
1 2 3 4 5 6 7 8 9 10 11 | player_name -------------- teamsolo armada lonewolf knownsecret winterwolf moltenice silentkiller casper (8 ROWS) |
How to use the PostgreSQL ALL with WHERE Statement
This section will cover how to use the PostgreSQL ALL with WHERE statement. The following commands will find the player’s name if the data in the playerstats table has a value of _either 25000no_of_kills or 370no_of_deaths`.
1 2 3 4 5 | SELECT player_name FROM player WHERE player_id = ALL (SELECT player_id FROM player_stats WHERE no_of_kills = 25000 OR no_of_deaths = 370); |
The results should resemble the following:
1 2 3 4 | player_name ------------- teamsolo (1 ROW) |
The following commands use the ALL and less-than (<) operator to locate all of the players having playing hours that are less than the average playing hours:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT player_id, player_name, playing_hours FROM player WHERE playing_hours < ALL ( SELECT ROUND(AVG (playing_hours),2) FROM player GROUP BY rating ) ORDER BY rank; |
The results should resemble the following:
1 2 3 | player_id | player_name | playing_hours -----------+-------------+--------------- 1 | teamsolo | 200 |
In the result set returned by the sub-query, shown above, the statement retrieved all the players having playing hours that are less than the largest value within the average playing hours.
Conclusion
This tutorial covered how to use the PostgreSQL ALL operator, provided examples and showed different ways of using the statements to obtain various results. The article specifically covered how to create a sample data set, how to use the PostgreSQL ALL and SELECT statement and the PostgreSQL ALL with WHERE statement. Remember that the ALL operator command must be preceded by a comparison operator.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started