PostgreSQL

Subqueries in PostgreSQL are a powerful feature of Data Query Language (DQL) that allow you to embed one query within another. This can be particularly useful when you need to perform complex queries that involve multiple steps or calculations. Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, or WHERE clauses, to filter or aggregate data in sophisticated ways.

Here’s a breakdown of how subqueries work in PostgreSQL:

1. Subquery Basics
   • A subquery is a query nested inside another query.
   • Subqueries can be used to return data for the outer query to process.
   • They are often enclosed in parentheses.
2. Types of Subqueries
   • Scalar Subqueries: Return a single value and are used where a single value is expected.
     • Example:

       SELECT first_name
       FROM customers
       WHERE customer_id = (SELECT customer_id FROM accounts WHERE account_balance > 1000);
             

   • Row Subqueries: Return a single row of values and are used when the outer query expects a single row.
     • Example:

       SELECT customer_id, first_name
       FROM customers
       WHERE (customer_id, first_name) = (SELECT customer_id, first_name FROM accounts WHERE account_balance > 1000 LIMIT 1);
             

   • Table Subqueries: Return a set of rows and columns and are used in the FROM clause.
     • Example:

       SELECT * FROM (SELECT * FROM transactions WHERE transaction_amount > 500) AS high_value_transactions;
             

3. Subquery in the WHERE Clause
   • Used to filter results based on the results of another query.
   • Example:

     SELECT account_id, account_balance
     FROM accounts
     WHERE account_id IN (SELECT account_id FROM transactions WHERE transaction_date = '2024-09-01');
     

4. Subquery in the FROM Clause
   • Used to create a temporary table to be used by the outer query.
   • Example:

     SELECT customer_id, total_transactions
     FROM (
       SELECT customer_id, COUNT(*) AS total_transactions
       FROM transactions
       GROUP BY customer_id
     ) AS transaction_summary
     WHERE total_transactions > 10;
     

5. Correlated Subqueries
   • These subqueries reference columns from the outer query and are executed once for each row processed by the outer query.
   • Example:

     SELECT customer_id, first_name
     FROM customers c
     WHERE EXISTS (
       SELECT 1
       FROM accounts a
       WHERE a.customer_id = c.customer_id AND a.account_balance > 1000
     );
     

By using subqueries effectively, you can perform more complex queries and achieve more sophisticated data analysis in PostgreSQL.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT employee_number, first_name, last_name, designation, salary
FROM org_employee
WHERE salary > (SELECT AVG(salary) FROM org_employee);

SQL SUB QUERY EXAMPLE

Raw data from employee table

Query

SELECT employee_number, first_name, last_name, designation, salary
FROM org_employee
WHERE salary >= (SELECT AVG(salary) FROM org_employee);

Step1, Execute sub query

First, run the subquery '(SELECT AVG(salary) FROM org_employee)' to obtain its result, which in this instance is the average salary.

Sub query output

Step 2, apply sub query output as filter criteria on main query

In this step, we filter for employees whose salary is greater than or equal to 250,000, which is the average salary determined from the subquery output.

FINAL, main query output