Updated May 25, 2023
Introduction to PostgreSQL ALL Operator
Deleted: The PostgreSQL ALL operator compares a particular value with the result set returned by a sub-query. It is mandatory to define the sub-query after the ALL operator, which is surrounded by the parenthesis. The PostgreSQL ALL operator always returns the comparison result is true if the result of the subquery returns no rows. We can use the PostgreSQL ALL operator with comparison_operator such as =, !=, >, >=, <, and <= etc.
Syntax:
comparison_operator ALL (subquery)Explanation:
Precede the operator with the following:
- equal (=)
- not equal (!=)
- greater than(>)
- greater than or equal to (>=)
- less than (<)
- less than or equal to (<=)
Place the subquery surrounded by parentheses after the PostgreSQL ALL operator.
How does it work in PostgreSQL?
Let’s consider the operator’s working as per the result returned by the sub-query.
1. The result of the sub-query returns some rows
- If a value to be compared is greater than the largest value returned by the sub-query result, then the expression column > ALL (subquery) evaluates to true.
- If a value to be compared is greater than or equal to the largest value returned by the sub-query result, then the expression column >= ALL (subquery) evaluates to true.
- The expression column ALL (subquery) evaluates to true if the result of the subquery provides the smallest value that is less than the compared value.
- If a value to be compared is less than or equal to the smallest value returned by the sub-query result, then the expression column <= ALL (subquery) evaluates to true.
- When a value being compared matches any value returned by the result of the subquery, the expression column = ALL (subquery) evaluates to true.
- The expression column != ALL (subquery) evaluates to true when a value to be compared does not match any value returned by the sub-query result.
2. The result of the subquery returns no rows
The PostgreSQL ALL operator always returns the comparison result as true.
Examples of PostgreSQL ALL Operator
Consider the two tables named COUNTRIES and CITIES to see examples of the PostgreSQL operator.
Code:
CREATE table COUNTRIES
(
country_id serial PRIMARY KEY,
country_name VARCHAR (256) NOT null,
last_updated DATE NULL
);
CREATE table CITIES
(
CITY_id serial PRIMARY KEY,
country_id INT NOT NULL,
city_name VARCHAR (256) NOT NULL,
last_updated DATE null,
FOREIGN KEY (country_id) REFERENCES COUNTRIES (country_id)
);Now insert some data in both tables.
Code:
INSERT INTO COUNTRIES (country_name,last_updated)
VALUES
('India','06-01-2020'),
('US','07-01-2020'),
('CHINA','08-01-2020');
INSERT INTO CITIES (country_id, city_name,last_updated)
VALUES
(1,'Pune','06-02-2020'),
(1,'Mumbai', '07-02-2020'),
(2,'New York', '08-02-2020'),
(2,'Los Angeles', '09-02-2020'),
(3,'Beijing ', '10-02-2020'),
(3,'Shanghai', '11-02-2020');Illustrate both tables and data inserted with the following tables and snapshots.
Code:
select * from COUNTRIES;Output:
Code:
select * from CITIES;Output:
Example #1
Let’s consider the following example: we will get records from the cities table where the country’s id is ‘3’.
Code:
SELECT city_id, country_id, city_name
FROM cities
WHERE
country_id = ALL (
SELECT country_id
FROM cities
WHERE country_id ='3');Output:
Example #2
Let’s consider the following example where we will get records from the cities table where the city’s name is not Pune, Mumbai, and New York.
Code:
SELECT city_id, country_id, city_name
FROM cities
WHERE
city_id != ALL (
SELECT city_id
FROM cities
WHERE city_name ='Pune'or city_name ='Mumbai'or city_name ='New York');Output:
Example #3
Let’s consider another example where we will get records from the cities table where the city id is greater than the city id of Pune city.
Code:
SELECT city_id, country_id, city_name
FROM cities
where city_id > ALL (
SELECT city_id
FROM cities
WHERE city_name ='Pune');Output:
Example #4
Similar to the above example, we can get records from the cities table where the city id is less than the city id of New York City.
Code:
SELECT city_id, country_id, city_name
FROM cities
where city_id < ALL (
SELECT city_id
FROM cities
WHERE city_name ='New York');Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL ALL Operator” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
