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:

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:

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:

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:

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:

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:

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:

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:

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:

SELECT ALL player_name
FROM player
WHERE TRUE;

The results should resemble the following:

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

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:

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:

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:

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.