Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL Subqueries

Subqueries in PostgreSQL are nested queries used within a main query to perform complex filtering or computations. They allow you to isolate parts of SQL queries and use intermediate results to formulate the final query output.

Usage

Subqueries are typically used in SELECT, INSERT, UPDATE, or DELETE statements to break down complex SQL operations into simpler, more manageable components. They are enclosed in parentheses and can return a single value or a set of values to be used by the main query.

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column FROM table WHERE condition);

In this syntax, the subquery is used within a WHERE clause to filter results based on the output of the nested query.

Examples

1. Basic Subquery in WHERE Clause

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

This example retrieves customers who have placed orders after January 1, 2023, using a subquery within the WHERE clause.

2. Subquery in SELECT Statement

SELECT product_id,
       (SELECT AVG(price) FROM products) AS average_price
FROM products;

Here, a subquery calculates the average price of all products and presents it alongside each product's ID.

3. Using Subquery with EXISTS

SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT * FROM products WHERE suppliers.supplier_id = products.supplier_id AND products.stock > 0);

This example checks for suppliers with products in stock using an EXISTS condition to filter results.

4. Subquery in INSERT Statement

INSERT INTO sales_summary (product_id, total_sales)
SELECT product_id, SUM(sales_amount)
FROM sales
WHERE sale_date > '2023-01-01'
GROUP BY product_id;

This example uses a subquery to aggregate sales data and insert the results into a summary table.

5. Subquery in UPDATE Statement

UPDATE products
SET price = price * 1.1
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');

Here, the subquery finds the category ID for 'Electronics', and the main query updates the prices of those products.

6. Subquery in DELETE Statement

DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_items WHERE quantity = 0);

This example deletes orders that have no items, as determined by a subquery.

7. Correlated Subquery Example

SELECT employee_id, employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.employee_id AND s.amount > 1000);

A correlated subquery references columns from the outer query, here checking for employees with sales over $1000.

Tips and Best Practices

  • Optimize performance. Use subqueries wisely; consider indexing relevant columns or restructuring queries to minimize execution time.
  • Use subqueries for modularity. Break down complex queries into subqueries for easier management and readability.
  • Leverage correlated subqueries carefully. Correlated subqueries are powerful but can impact performance as they're executed once per row in the outer query.
  • Consider using EXISTS for existence checks. It's often more efficient than comparing subquery results with operators like IN.