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.

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 :

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