PostgreSQL Subqueries

A subquery in PostgreSQL is a query nested inside another query. Subqueries are used to perform intermediate calculations, filter results dynamically, or feed data into the main query. They can be placed in the SELECT, FROM, or WHERE clauses.

1. Basic Syntax

-- Basic subquery example
            SELECT column1, column2
            FROM table1
            WHERE column1 = (SELECT column1 FROM table2 WHERE condition);

2. Subquery in WHERE Clause

Subqueries in the WHERE clause help filter results based on values retrieved from another query.

-- Employees who earn more than the average salary
            SELECT name, salary
            FROM employees
            WHERE salary > (
                SELECT AVG(salary)
                FROM employees
            );

3. Subquery in SELECT Clause

Subqueries in the SELECT clause allow you to compute values for each row in the main query.

-- Employee name with their department name using subquery
            SELECT name,
                (SELECT department_name 
                    FROM departments 
                    WHERE departments.id = employees.department_id) AS department
            FROM employees;

4. Subquery in FROM Clause

Subqueries in the FROM clause act as temporary tables (derived tables) for further queries.

-- Total salary per department, then select departments with total > 200000
            SELECT department, total_salary
            FROM (
                SELECT department_id AS department, SUM(salary) AS total_salary
                FROM employees
                GROUP BY department_id
            ) AS dept_totals
            WHERE total_salary > 200000;

5. Correlated Subqueries

A correlated subquery refers to columns from the outer query. It executes once per row of the outer query.

-- Find employees who earn more than the average in their department
            SELECT name, department_id, salary
            FROM employees e1
            WHERE salary > (
                SELECT AVG(salary)
                FROM employees e2
                WHERE e1.department_id = e2.department_id
            );

6. Subqueries with EXISTS

The EXISTS operator checks whether a subquery returns any rows.

-- List departments that have at least one employee
            SELECT department_name
            FROM departments d
            WHERE EXISTS (
                SELECT 1
                FROM employees e
                WHERE e.department_id = d.id
            );

7. Subqueries with IN

-- Employees in departments with more than 5 employees
            SELECT name, department_id
            FROM employees
            WHERE department_id IN (
                SELECT department_id
                FROM employees
                GROUP BY department_id
                HAVING COUNT(*) > 5
            );

8. Best Practices

• Use subqueries to simplify complex queries and avoid multiple passes over data.
• Consider replacing correlated subqueries with JOIN for better performance on large datasets.
• Always alias subqueries in the FROM clause.
• Test subqueries individually to ensure correct logic before embedding them.

Conclusion

Subqueries in PostgreSQL are a powerful tool for writing flexible and dynamic queries. They allow you to perform calculations, filter data, and create temporary datasets efficiently. By mastering subqueries in SELECT, FROM, and WHERE clauses, as well as correlated subqueries and EXISTS, you can write complex PostgreSQL queries effectively. In the next tutorial, we will explore SQL Views, which provide a way to save and reuse query results.