PostgreSQL Subqueries
Introduction
A subquery is a SQL query nested inside a larger query.
- A subquery may occur in:
- In PostgreSQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery.
- A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
- You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, SOME, or ALL.
- A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
- The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.
Contents:
- Subquery Syntax
- PostgreSQL Subquery Example
- Subqueries : Guidelines and Types of Subqueries
- PostgreSQL Subquery as Scalar Operand
- PostgreSQL Subqueries: Using Comparisons
- PostgreSQL Subqueries with ALL, ANY, IN, or SOME
- PostgreSQL Subqueries with EXISTS
- PostgreSQL Row Subqueries
- PostgreSQL Correlated Subqueries
- PostgreSQL Subqueries in the FROM Clause
Subquery Syntax:
- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.
PostgreSQL Subquery Example:
Using a subquery, list the name of the employees, paid more than 'Alexander' from employees.
Code:
SELECT first_name,last_name, salary FROM employees
WHERE salary >
(SELECT max(salary) FROM employees
WHERE first_name='Alexander');
Sample Output:
first_name | last_name | salary -----------+-----------+---------- Steven | King | 24000.00 Neena | Kochhar | 17000.00 Lex | De Haan | 17000.00 Nancy | Greenberg | 12000.00 Den | Raphaely | 11000.00 John | Russell | 14000.00 Karen | Partners | 13500.00 Alberto | Errazuriz | 12000.00 Gerald | Cambrault | 11000.00 Eleni | Zlotkey | 10500.00 Peter | Tucker | 10000.00 David | Bernstein | 9500.00 Janette | King | 10000.00 ..............
Subqueries: Guidelines
There are some guidelines to consider when using subqueries :
- A subquery must be enclosed in parentheses.
- Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries.
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
Types of Subqueries
- The Subquery as Scalar Operand
- Comparisons using Subqueries
- Subqueries with ALL, ANY, IN, or SOME
- Row Subqueries
- Subqueries with EXISTS or NOT EXISTS
- Correlated Subqueries
- Subqueries in the FROM Clause
PostgreSQL Subquery as Scalar Operand
A scalar subquery is a subquery that returns exactly one column value from one row.
- The SELECT query is executed and the single returned value is used in the surrounding value expression.
- It is an error to use a query that returns more than one row or more than one column as a scalar subquery.
- During a particular execution, if the subquery returns no rows, that is not an error; the scalar result is taken to be null.
- The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.
Examplep: PostgreSQL Subquery as Scalar Operand
Code:
SELECT employee_id, last_name,
(CASE WHEN department_id=(
SELECT department_id from departments WHERE location_id=2500)
THEN 'Canada' ELSE 'USA' END)
FROM employees;
Sample Output:
employee_id | last_name | case -------------+-------------+-------- 100 | King | USA 101 | Kochhar | USA 102 | De Haan | USA 103 | Hunold | USA 104 | Ernst | USA 105 | Austin | USA 106 | Pataballa | USA 107 | Lorentz | USA 108 | Greenberg | USA 109 | Faviet | USA ...................... 107 rows in set (0.00 sec)
PostgreSQL Subqueries: Using Comparisons
A subquery can be used before or after any of the comparison operators. The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. SQL then compares the value that results from the subquery with the value on the other side of the comparison operator. You can use the following comparison operators :
Operator | Description |
---|---|
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
!= | Not equal to |
<> | Not equal to |
<=> | NULL-safe equal to operator |
For example, suppose you want to find the employee id, first_name, last_name, and salaries for employees whose average salary is higher than the average salary throughout the company.
Code:
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary >
(SELECT AVG(SALARY) FROM employees);
Sample Output:
employee_id | first_name | last_name | salary -------------+-------------+------------+---------- 100 | Steven | King | 24000.00 101 | Neena | Kochhar | 17000.00 102 | Lex | De Haan | 17000.00 103 | Alexander | Hunold | 9000.00 108 | Nancy | Greenberg | 12000.00 109 | Daniel | Faviet | 9000.00 110 | John | Chen | 8200.00 111 | Ismael | Sciarra | 7700.00 112 | Jose Manuel | Urman | 7800.00 113 | Luis | Popp | 6900.00 .................... ....................
PostgreSQL Subqueries with ALL operator
Syntax:
expression operator ALL (subquery)
The ALL operator compares value to every value returned by the subquery. The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result.
- The result of ALL is true if all rows yield true (including the case where the subquery returns no rows).
- The result is false if any false result is found.
- The result is NULL if the comparison does not return false for any row, and it returns NULL for at least one row.
Example: PostgreSQL Subquery, ALL operator
The following query selects the department with the highest average salary. The subquery finds the average salary for each department, and then the main query selects the department with the highest average salary.
Code:
SELECT department_id, AVG(SALARY)
FROM employees GROUP BY department_id
HAVING AVG(SALARY)>=ALL
(SELECT AVG(SALARY) FROM employees
GROUP BY department_id);
Sample Output:
department_id | avg ---------------+-------------------- 90 | 19333.333333333333 (1 row)
Note: Here we have used ALL keyword for this subquery as the department selected by the query must have an average salary greater than or equal to all the average salaries of the other departments.
PostgreSQL Subqueries with ANY/SOME operator
Syntax:
expression operator ANY (subquery) expression operator SOME (subquery)
The ANY operator compares the value to each value returned by the subquery. Therefore ANY keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.
SOME is a synonym for ANY. IN is equivalent to = ANY.
Example: PostgreSQL Subquery, ANY operator
The following query selects any employee who works in the location 1700. The subquery finds the department id in the 1700 location, and then the main query selects the employees who work in any of these departments.
departments table:
Code:
SELECT first_name, last_name,department_id
FROM employees
WHERE department_id= ANY
(SELECT DEPARTMENT_ID
FROM departments WHERE location_id=1700);
Sample Output:
first_name | last_name | department_id -------------+------------+--------------- Steven | King | 90 Neena | Kochhar | 90 Lex | De Haan | 90 Nancy | Greenberg | 100 Daniel | Faviet | 100 John | Chen | 100 Ismael | Sciarra | 100 Jose Manuel | Urman | 100 Luis | Popp | 100 Den | Raphaely | 30 Alexander | Khoo | 30 Shelli | Baida | 30 Sigal | Tobias | 30 Guy | Himuro | 30 Karen | Colmenares | 30 Jennifer | Whalen | 10 Shelley | Higgins | 110 William | Gietz | 110 (18 rows)
Note: We have used ANY keyword in this query, because it is likely that the subquery will find more than one departments in 1700 location. If you use the ALL keyword instead of the ANY keyword, no data is selected because no employee works in all departments of 1700 location
PostgreSQL Subqueries with IN operator
Syntax:
expression IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result.
- The result of IN is true if any equal subquery row is found.
- The result is “false” if no equal row is found (including the case where the subquery returns no rows).
- If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false.
Example: PostgreSQL Subquery, IN operator
The following query selects those employees who work in the location 1800. The subquery finds the department id in the 1800 location, and then the main query selects the employees who work in any of these departments.
Code:
SELECT first_name, last_name,department_id
FROM employees
WHERE department_id IN
(SELECT DEPARTMENT_ID FROM departments
WHERE location_id=1800);
Sample Output:
------------+-----------+--------------- Michael | Hartstein | 20 Pat | Fay | 20 (2 rows)
PostegreSQL Subqueries with NOT IN operator
Syntax:
expression NOT IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result.
- The result of NOT IN is true if any equal subquery row is found.
- The result is “false” if no equal row is found (including the case where the subquery returns no rows).
- If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false.
Example: PostgreSQL Subquery, NOT IN operator
The following query selects those employees who does not work in those department where the managers of ID between 100 and 200 works. The subquery finds the department id which is under the manager whose id is between 100 and 200, and then the main query selects the employees who do not work in any of these departments.
Code:
SELECT first_name, last_name,department_id
FROM employees
WHERE department_id NOT IN
(SELECT DEPARTMENT_ID FROM departments
WHERE manager_id
BETWEEN 100 AND 200);
Sample Output:
first_name | last_name | department_id ------------+-----------+--------------- Kimberely | Grant | 0 Michael | Hartstein | 20 Pat | Fay | 20 Susan | Mavris | 40 Hermann | Baer | 70 Shelley | Higgins | 110 William | Gietz | 110 (7 rows)
PostgreSQL Subqueries with EXISTS operator
Syntax:
EXISTS (subquery)
The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is true; if the subquery returns no rows, the result of EXISTS is false.
Example: PostgreSQL Subqueries with EXISTS
The following query finds employees (employee_id, first_name, last_name, job_id, department_id) from employees table who have at least one person reporting to them.
Code:
SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees E
WHERE EXISTS
(SELECT * FROM employees
WHERE manager_id = E.employee_id);
Sample Output:
employee_id | first_name | last_name | job_id | department_id -------------+------------+-----------+---------+--------------- 100 | Steven | King | AD_PRES | 90 101 | Neena | Kochhar | AD_VP | 90 102 | Lex | De Haan | AD_VP | 90 103 | Alexander | Hunold | IT_PROG | 60 108 | Nancy | Greenberg | FI_MGR | 100 114 | Den | Raphaely | PU_MAN | 30 120 | Matthew | Weiss | ST_MAN | 50 121 | Adam | Fripp | ST_MAN | 50 122 | Payam | Kaufling | ST_MAN | 50 123 | Shanta | Vollman | ST_MAN | 50 ............ 18 rows in set (0.02 sec)
PostgreSQL Row Subqueries
A row subquery is a subquery that returns a single row and more than one column value. You can use = , >, <, >=, <=, <>, !=, <=> comparison operators. See the following examples:
Syntax:
row_constructor operator (subquery)
Example: PostgreSQL Row Subqueries
In the following examples, queries shows different result according to above conditions :
Code:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 1800);
Sample Output:
first_name ------------ Pat (1 row)
Code:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 2800);
Sample Output:
first_name ------------ (0 rows)
Code:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 1700);
Sample Output:
ERROR: more than one row returned by a subquery used as an expression
PostgreSQL Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table (in the parent query) that also appears in the outer query. PostgreSQL evaluates from inside to outside.
Correlated subquery syntax:
Example - 1: PostgreSQL Correlated Subqueries
Following query find all employees who earn more than the average salary in their department.
Code:
SELECT last_name, salary, department_id
FROM employees outerr
WHERE salary>
(SELECT AVG(salary)
FROM employees
WHERE department_id = outerr.department_id);
Sample Output:
last_name | salary | department_id ----------+----------+--------------- King | 24000.00 | 90 Hunold | 9000.00 | 60 Ernst | 6000.00 | 60 Greenberg | 12000.00 | 100 Faviet | 9000.00 | 100 Raphaely | 11000.00 | 30 Weiss | 8000.00 | 50 Fripp | 8200.00 | 50 Kaufling | 7900.00 | 50 Vollman | 6500.00 | 50 Mourgos | 5800.00 | 50 ....................
Example - 2: PostgreSQL Correlated Subqueries
From the employees and job_history tables display details of those employees who have changed jobs at least once.
job_history table:
Code:
SELECT first_name, last_name, employee_id, job_id
FROM employees E
WHERE 1 <=
(SELECT COUNT(*) FROM Job_history
WHERE employee_id = E.employee_id);
Sample Output:
first_name | last_name | employee_id | job_id ------------+-----------+-------------+--------- Neena | Kochhar | 101 | AD_VP Lex | De Haan | 102 | AD_VP Den | Raphaely | 114 | PU_MAN Payam | Kaufling | 122 | ST_MAN Jonathon | Taylor | 176 | SA_REP Jennifer | Whalen | 200 | AD_ASST Michael | Hartstein | 201 | MK_MAN (7 rows)
PostgreSQL Subqueries in the FROM Clause
Subqueries works in a SELECT statement's FROM clause. The syntax is:
SELECT ... FROM (subquery) [AS] name ...
Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any columns in the subquery select list must have unique names.
Example: PostgreSQL Subqueries in the FROM Clause
We have the following table tb1.
Code:
CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
CREATE TABLE
Let insert some values into tb1.
Code:
INSERT INTO tb1 VALUES (1, '1', 1.0);
Sample Output:
INSERT 0 1
Code:
INSERT INTO tb1 VALUES (2, '2', 2.0);
Sample Output:
INSERT 0 1
Code:
INSERT INTO tb1 VALUES (3, '3', 3.0);
Sample Output:
INSERT 0 1
Code:
SELECT * FROM tb1;
Sample Output:
c1 | c2 | c3 ----+-------+---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 (3 rows)
Here is how to use a subquery in the FROM clause, using the example table (tb1) :
Code:
SELECT sc1, sc2, sc3
FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb
WHERE sc1>1;
Sample Output:
sc1 | sc2 | sc3 -----+-------+----- 2 | 2 | 6 3 | 3 | 9 (2 rows)
Previous: FULL OUTER JOIN
Next: VIEWS
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/postgresql-subqueries.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics