Writing Subqueries

Introduction

Subqueries in SQL are powerful tools that allow users to write queries within queries. They can simplify complex queries and enhance the database querying capabilities. This lesson will explore subqueries, their types, and how to effectively use them in database development.

Definition

A subquery is a query nested inside another SQL query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements and can return a single value, a list of values, or a table of values.

Types of Subqueries

• Single-row subquery: Returns a single row.
• Multiple-row subquery: Returns multiple rows.
• Correlated subquery: Refers to columns from the outer query.
• Non-correlated subquery: Does not refer to the outer query.

Examples

1. Single-row Subquery

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

2. Multiple-row Subquery

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

3. Correlated Subquery

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

4. Non-correlated Subquery

SELECT name 
FROM employees 
WHERE department_id = (SELECT department_id FROM departments WHERE name = 'Sales');

Best Practices

1. Use subqueries for clarity and simplicity when dealing with complex queries.
2. Prefer JOINs over subqueries when performance is a concern.
3. Avoid using subqueries in the SELECT clause when possible, as it can lead to performance issues.
4. Test subqueries independently to ensure they return the expected results.

FAQ