Introduction to PostgreSQL ALL
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:
The operator should be preceded by the following:
- equal (=)
- not equal (!=)
- greater than(>)
- greater than or equal to (>=)
- less than (<)
- less than or equal to (<=)
The subquery surrounded by parentheses should be placed after the PostgreSQL ALL operator.
How does ALL Operator work in PostgreSQL?
Let’s consider the working of the operator 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 result of the sub-query 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 result of the sub-query then the expression column >= ALL (subquery) evaluates to true.
- If a value to be compared is less than the smallest value returned by the result of the sub-query then the expression column < ALL (subquery) evaluates to true.
- If a value to be compared is less than or equal to the smallest value returned by the result of the sub-query then the expression column <= ALL (subquery) evaluates to true.
- If a value to be compared is equal to any value returned by the result of the sub-query then the expression column = ALL (subquery) evaluates to true.
- . If a value to be compared is not equal to any value returned by the result of the sub-query then the expression column != ALL (subquery) evaluates to true.
2. The result of the subquery returns no rows
The PostgreSQL ALL operator always returns the comparison result as true.
Examples of PostgreSQL ALL
Consider the two tables named COUNTRIES and CITIES in order to see the 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 where we will get records from the cities table where the id of the country 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 name of the city 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
This is a guide to PostgreSQL ALL. Here we discuss the introduction, how does ALL operator work in PostgreSQL? and examples respectively. You may also have a look at the following articles to learn more –
