Post
Ask Question

Explain Subqueries in PostgreSQL

What is Subquery in PostgreSQL?

Subqueries in PostgreSQL allow the use of the result of one query as a part of another query. Subqueries can be used in various SQL clauses like SELECT, FROM, WHERE HAVING, and even in JOIN conditions.

There are different types of subqueries, including scalar subqueries, row subqueries, and table subqueries.

1. Basic Subquery in the WHERE Clause

A common use of subqueries is in the WHERE clause. This allows you to filter the main query based on the result of another query.

Suppose you have two tables, employees, and departments.

Employees table
 

id name department_id salary
1 Vipul 1 5000
2 Atul 2 6000
3 Sweta 1 7000


Departments table
 

id department_name
1 IT
2 Sales

Now,

Q. Find all employees who work in the "IT" department.

Here, You can achieve this using a subquery in the WHERE clause.

SELECT name
FROM employees
WHERE department_id = (
    SELECT id
    FROM departments
    WHERE department_name = 'IT'
);

2. Subquery in the FROM Clause

Subqueries can also be used in the FROM clause, essentially treating the result of the subquery as a temporary table (often called a derived table).

Suppose you want to calculate the average salary by department and then find which departments have an average salary greater than 60,000.

SELECT department_name, avg_salary
FROM (
    SELECT d.department_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.department_name
) AS avg_salary_table
WHERE avg_salary > 60000;

3. Subquery in the SELECT Clause

Subqueries can also be placed directly within the SELECT clause. This is useful when you want to calculate a value or retrieve additional information for each row of the main query.

Suppose you want to list each employee's name and their department's name.

SELECT name,
       (SELECT department_name
        FROM departments
        WHERE id = e.department_id) AS department_name
FROM employees e;

4. Correlated Subquery

A correlated subquery is a subquery that references columns from the outer query. Unlike a regular subquery, which runs independently of the outer query, a correlated subquery is executed once for each row processed by the outer query.

Suppose you want to find all employees whose salaries are above the average in their department.

SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

5. Subquery in the JOIN Clause

Subqueries can also be used within a JOIN clause, effectively allowing you to join with a result set produced by a subquery.

Suppose you want to find the department name for each employee but only for departments that have more than one employee.

SELECT e.name, d.department_name
FROM employees e
JOIN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 1
) AS subq ON e.department_id = subq.department_id
JOIN departments d ON e.department_id = d.id;

Conclusion

Subqueries in PostgreSQL are a powerful feature that allows you to build complex queries by nesting one query inside another. You can use subqueries in the WHERE, FROM, SELECT, and JOIN clauses, depending on the specific requirements of your query. Understanding how and when to use subqueries can greatly enhance the flexibility and expressiveness of your SQL queries.


Similar Articles