PostgreSQL Subquery Expressions

We will describes the SQL-compliant subquery expressions available in PostgreSQL

Below are the list

  • EXISTS
  • IN
  • NOT IN
  • ANY/SOME
  • ALL

EXISTS

EXISTS clause always use with 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”

i.e EXISTS subquery returns a boolean (True/False)

postgres=# select empno,deptno,empname from emp where exists (select 1 from dept);
 empno | deptno | empname  
-------+--------+----------
    11 |      5 | Solomon
    10 |      5 | Bill
    12 |      5 | Susan
    13 |      1 | Wendy
    14 |      1 | Benjamin
    15 |      1 | Tom
    16 |      2 | Henry
    17 |      2 | Robert
    18 |      2 | Paul
(9 rows)

postgres=# select empno,deptno,empname from emp where exists (select 1 from dept where deptno = 10);
 empno | deptno | empname 
-------+--------+---------
(0 rows)

postgres=# select empno,deptno,empname from emp e where exists (select 1 from dept d where d.deptno = e.deptno);
 empno | deptno | empname  
-------+--------+----------
    11 |      5 | Solomon
    10 |      5 | Bill
    12 |      5 | Susan
    13 |      1 | Wendy
    14 |      1 | Benjamin
    15 |      1 | Tom
    16 |      2 | Henry
    17 |      2 | Robert
    18 |      2 | Paul
(9 rows)

IN

expression IN (subquery)

IN clause evaluates subquery into results and these results are passed on to main query. The right-hand side is a parenthesised subquery, which must return exactly one column

postgres=# select empno,empname from emp where deptno in (1,2);
 empno | empname  
-------+----------
    13 | Wendy
    14 | Benjamin
    15 | Tom
    16 | Henry
    17 | Robert
    18 | Paul
(6 rows)

postgres=# select empno,empname from emp where deptno in (select deptno from dept where location != 'Dubai'  );
 empno | empname 
-------+---------
    11 | Solomon
    10 | Bill
    12 | Susan
    16 | Henry
    17 | Robert
    18 | Paul
(6 rows)

NOT IN

expression NOT IN (subquery)

NOT IN clause evaluates subquery into results and these results are passed on to main query.

The right-hand side is a parenthesised subquery, which must return exactly one column.

postgres=# select empno,empname from emp where deptno not in (1,2);
 empno | empname 
-------+---------
    11 | Solomon
    10 | Bill
    12 | Susan
(3 rows)

postgres=# select empno,empname from emp where deptno not in (select deptno from dept where location = 'Dubai' );
 empno | empname 
-------+---------
    11 | Solomon
    10 | Bill
    12 | Susan
    16 | Henry
    17 | Robert
    18 | Paul
(6 rows)

ANY/SOME

expression operator ANY (subquery)
expression operator SOME (subquery)

SOME is a synonym for ANY.

IN is equivalent to = ANY

The right-hand side is a parenthesised 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.

postgres=# select empno,empname from emp where deptno = any (select deptno from dept where location = 'Dubai'  );
 empno | empname  
-------+----------
    13 | Wendy
    14 | Benjamin
    15 | Tom
(3 rows)

postgres=# select empno,empname from emp where deptno = some (select deptno from dept where location = 'Dubai'  );
 empno | empname  
-------+----------
    13 | Wendy
    14 | Benjamin
    15 | Tom
(3 rows)

ALL

expression operator ALL (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.

postgres=# select empno,empname from emp where deptno = ALL (select deptno from dept where location = 'Dubai');
 empno | empname  
-------+----------
    13 | Wendy
    14 | Benjamin
    15 | Tom
(3 rows)