PostgreSQL - SPLessons

PostgreSQL Sub Queries

Chapter 16

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Sub Queries

PostgreSQL Sub Queries

shape 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.

shape 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.

shape Examples

By viewing the below example the concept of PostgreSQL Subqueries can be easily understands.

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)

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.

Summary

shape Key Points

  • PostgreSQL Subqueries – A query within another query is said to be subquery.