Subqueries in PostgreSQL

1. Introduction

Subqueries, or nested queries, are queries within queries. They allow you to perform complex data retrieval operations in PostgreSQL by using the result of one query to filter or manipulate another query.

2. Types of Subqueries

• Single-row subqueries
• Multiple-row subqueries
• Correlated subqueries

3. Syntax and Examples

3.1 Single-row Subquery

This type of subquery returns a single value. Here's an example:

SELECT name 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

3.2 Multiple-row Subquery

Multiple-row subqueries return multiple values. You can use the IN clause for such subqueries:

SELECT name 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location_id = 1000);

3.3 Correlated Subquery

Correlated subqueries reference columns from the outer query. Here's a simple example:

SELECT e1.name 
FROM employees e1 
WHERE e1.salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

4. Best Practices

• Use subqueries when they simplify the logic of your queries.
• Avoid unnecessary correlated subqueries for performance reasons.
• Consider using JOINs instead of subqueries when dealing with large datasets.
• Use EXISTS instead of IN for correlated subqueries when appropriate.

5. FAQ