Subquery in SQL | Correlated Subquery + Complete SQL Subqueries Tutorial

Introduction

This tutorial covers SQL subqueries, providing a comprehensive understanding of their types and applications. By the end, you'll know how to write and utilize subqueries effectively in your SQL queries.

Step 1: Understand What a Subquery Is

• A subquery is a query nested within another SQL query.
• It allows you to perform operations that depend on the results of another query.
• Subqueries can return single or multiple results.

Step 2: Write a Simple Subquery

• Start with a basic structure:

  SELECT column_name
  FROM table_name
  WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);
  

• Example:

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

Step 3: Types of Subqueries

Single Row Subquery

• Returns a single row result.
• Use when you expect only one value.

Multiple Row Subquery

• Returns multiple rows.
• Use with operators like IN or ANY.

Single Column Subquery

• Returns a single column of results.
• Ideal for filtering based on one criterion.

Multiple Column Subquery

• Returns multiple columns.
• Can be used with JOIN conditions.

Correlated Subquery

• A subquery that references columns from the outer query.
• Evaluated once for each row processed by the outer query.

Step 4: Learn Where to Use Subqueries

• Subqueries can be used in various SQL clauses:
  • SELECT
  • FROM
  • WHERE
  • HAVING

Example of a Subquery in the WHERE Clause

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

Step 5: Using Subqueries with SQL Commands

• Subqueries can enhance commands like INSERT, UPDATE, and DELETE.

Example with INSERT

INSERT INTO new_employees (employee_id, employee_name)
VALUES ((SELECT MAX(employee_id) FROM employees) + 1, 'John Doe');

Example with UPDATE

UPDATE employees
SET salary = salary * 1.1
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2000);

Example with DELETE

DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM terminated_employees);

Conclusion

Subqueries are a powerful tool in SQL that allow you to write more dynamic and dependent queries. By understanding different types and their applications, you can create efficient and effective SQL commands. As you practice, consider exploring more complex queries and how subqueries can be nested to solve intricate problems.