PostgreSQL Sub Query

A Subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

Types of Subqueries:

Single Row Sub Query:

Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.

Example:

pearl=# select name from siva where age = (select age from siva where age=21);

Multiple row sub query:

Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

Usage of Multiple Row operators

[> ALL] More than the highest value returned by the subquery

[< ALL] Less than the lowest value returned by the subquery

[< ANY] Less than the highest value returned by the subquery

[> ANY] More than the lowest value returned by the subquery

[= ANY] Equal to any value returned by the subquery (same as IN)

Examples:

pearl=# select name from siva where age >all (select age from siva where age>=26);

pearl=# select name from siva where age =26);

pearl=# select name from siva where age =26);

pearl=# select name from siva where age >any (select age from siva where age>=26);

pearl=# select name from siva where age in (select age from siva where age>=26);

pearl=# select name from siva where age =any (select age from siva where age>=26);

Correlated Sub Query: (Use Multiple Tables)

Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.

Example:
pearl=# select a.id,a.name from siva a where id in (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id = (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id in (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id >all (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id <all (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id >any (select b.id from muthu b where place ='theni');

pearl=# select a.id,a.name,a.salary from siva a where id <any (select b.id from muthu b where place ='theni');

subquery with EXISTS:

pearl=# select a.id,a.name from siva a where exists (select from muthu b where a.id=b.id);

Subqueries with the INSERT Statement:

pearl=# insert into mms select id from siva where id in(select id from siva);

Subqueries with the UPDATE Statement:

pearl=# update siva set salary = salary * 2 where age in (select age from siva where age >=26);

Subqueries with the DELETE Statement:

pearl=# delete from siva where age in (select age from siva where age >26);