Description
A PostgreSQL Sub Queries is a nested query where the result of one query can be used in another query via relational operator or aggregation functions. And a PostgreSQL Sub Queries must be enclosed with in parentheses and contain only one column in the select clause if used in where clause.
An orderby clause is not allowed in a subquery. And this can be nested within other PostgreSQL Sub Queries. PostgreSQL Sub Queries are used in where,having,from and in select clauses.
Syntax
The syntax for PostgreSQL SubQueries is as follows:
Select <column_name1>,<column_name2> from <table_name> where <column_name2> <condition> (select <column_name2> from <table_name> where condition);
Table name => Any accurate table in the database.
Column name =>The column names that are inserted in the table.
Condition => Is a logic.
Examples
By viewing the below example the concept of PostgreSQL Subqueries can be easily understands.
[c]
SQLDB=# select * from employee21;
emp_id | ename | sal | deptno | job
-------+-------+-------+--------+-----------
1001 | mike | 12000 | 10 | manager
1002 | rambo | 13000 | 20 | scalesman
1003 | kate | 14000 | 10 | manager
1003 | jeo | 14000 | 20 | manager
1003 | finn | 14000 | 30 | manager
(5 rows)
SQLDB=# select emp_id,ename,sal from employee21 where sal >(select sal from employee21 where ename='rambo');
emp_id | ename | sal
-------+-------+-------
1003 | kate | 14000
1003 | jeo | 14000
1003 | finn | 14000
(3 rows)
[/c]
Here in the above example a query with in another query is used,such as empid, ename and salary are selected from employee table and another select statement is performed on salary where ename equal to rambo with some condition. So it executes employee salary greater than rambo salary.
Key Points
- PostgreSQL Subqueries - A query within another query is said to be subquery.