SQL Subquery Tutorial

Introduction

A subquery, also known as an inner query or nested query, is a query within another SQL query. Subqueries are used to perform operations that require multiple steps, such as filtering, calculations, or data retrieval from multiple tables.

Basic Syntax

The basic syntax of a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);

Types of Subqueries

1. Single-row subquery: Returns only one row.
2. Multi-row subquery: Returns more than one row.
3. Correlated subquery: A subquery that depends on the outer query.

Examples

Example 1: Single-row Subquery

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Example 2: Multi-row Subquery

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

Example 3: Correlated Subquery

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

Best Practices

• Use subqueries when you need to perform complex operations that cannot be done with a single query.
• Ensure that subqueries are optimized for performance, especially in large datasets.
• Use EXISTS or NOT EXISTS for checking the existence of rows in a subquery.

Conclusion

Subqueries are a powerful tool in SQL that allow you to perform complex data retrieval and manipulation. By understanding and using subqueries effectively, you can enhance your SQL queries and improve your database management skills.