Skip to content

Sub-query

  • A subquery is a query nested inside another query such as SELECT, INSERT, DELETE and UPDATE.

ANY

  • ANY is equivalent to IN operator.
select * from product where id = ANY(Select id from product where id>2);

ALL

  • The PostgreSQL ALL operator allows you to query data by comparing a value with a list of values returned by a subquery.

  • Syntax:

comparison_operator ALL (subquery)

  • The ALL operator must be preceded by a comparison operator such as equal (=), not equal (!=), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=).

  • The ALL operator must be followed by a subquery which also must be surrounded by the parentheses.

select * from product
where
id > ALL(Select id from product where id <= 3);

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  4 | CG                                                 |   500 | NEPAL
  5 | Tesla                                              | 11500 | USA
  6 | Jio                                                |  1600 | INDIA
(3 rows)

Exists / Not Exists

  • The EXISTS operator is a boolean operator that tests for existence of rows in a subquery.

  • If the subquery returns at least one row, the result of EXISTS is true.

  • In case the subquery returns no row, the result is of EXISTS is false.

select * from product where exists (select id from product where id>=3);

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  1 | Iphone                                             | 10000 | USA
  2 | Realme                                             |  1000 | INDIA
  3 | Vivo                                               |  2000 | CHINA
  4 | CG                                                 |   500 | NEPAL
  5 | Tesla                                              | 11500 | USA
  6 | Jio                                                |  1600 | INDIA
(6 rows)
  • When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.
SELECT * from EMP WHERE ‘RIYA’ IN (SELECT Name from DEPT WHERE EMP.EMPID=DEPT.EMPID);