PostgreSQL subquery (also known as inner queries or nested queries) is a tool for performing operations in multiple steps. For example, if you wanted to take the sums of several columns, then average all of those values, you’d need to do each aggregation in a distinct step.
Table of Contents
PostgreSQL subquery Example:
Subqueries can be used in several places within a query, but it’s easiest to start with the FROM statement. Here’s an example of a basic subquery:
SELECT column1 = (SELECT column-name FROM table-name WHERE condition),
column-names
FROM table-name
WHERE condition
Why subqueries:
Suppose you want to write a query to find out who earns a salary greater than Susan’s salary.
To solve this problem, you need two queries: one query to find what Susan earns and a second query to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query.
The inner query or the subquery returns a value that is used by the outer query or the main query.
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the
search value in the second query.
Subqueries
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when
you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses:
• WHERE clause
• HAVING clause
• FROM clause
Syntax:
the operator includes a comparison operator such as >, =, or IN
Note: Comparison operators fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row
operators (IN, ANY, ALL).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The
subquery generally
Guidelines for Using Subqueries
• A subquery must be enclosed in parentheses.
• A subquery must appear on the right side of the comparison operator.
• Subqueries cannot contain an ORDER BY clause. You can have only one ORDER BY clause for a
SELECT statement, and if specified it must be the last clause in the main SELECT statement.
• Two classes of comparison operators are used in subqueries: single-row operators and
multiple-row operators.
Types of Subqueries
• Single-row subqueries: Queries that return only one row from the inner SELECT statement
• Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement
• Multiple-column subqueries: Queries that return more than one column from the inner SELECT
statement
Single-Row Subqueries:
A single-row subquery is one that returns one row from the inner SELECT statement. This type of subquery uses a single-row operator. The slide gives a list of single-row operators.
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select * from emp where sal > (select sal from emp where empno=7698); empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----------+------+---------------------------+---------+--------+-------- 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 (4 rows) testdb=> |
Using Group Functions in a Subquery :
You can display data from the main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison operator.
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select deptno, min(sal) testdb-> from emp testdb-> group by deptno testdb-> having min(sal) > testdb-> (select min(sal) from emp where deptno=20); deptno | min --------+--------- 30 | 950.00 10 | 1300.00 (2 rows) |
Multiple-Row Subqueries :
Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row
operator, instead of a single-row operator, with a multiple-row subquery. The multiple-row operator expects
one or more values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
testdb=> select * testdb-> FROM emp testdb-> WHERE sal IN (SELECT MIN(sal) testdb(> FROM emp testdb(> GROUP BY deptno); empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-----------+-------+------+----------------------------------+---------+-------+-------- 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00+05:30 | 950.00 | 11.00 | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20 (3 rows) testdb=> |
Here subquery returned more than one record
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
