MySQL SubQuery

Introduction

In this chapter, we will learn about subqueries in MySQL. A subquery is a query nested inside another query. Subqueries can be used to perform operations that require multiple steps, such as filtering, aggregating, or joining data from different tables. We will cover the syntax for writing subqueries, examples of their usage, and important considerations for using subqueries in MySQL.

What is a Subquery?

A subquery is a query that is embedded within another query. The outer query is called the main query, and the inner query is called the subquery. Subqueries can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Types of Subqueries

Subqueries can be classified into several types based on their usage and placement in the main query:

1. Single-row subqueries: Return a single row of results.
2. Multi-row subqueries: Return multiple rows of results.
3. Scalar subqueries: Return a single value.
4. Column subqueries: Return a single column of results.
5. Correlated subqueries: Refer to columns from the outer query.

Syntax

The basic syntax for a subquery is:

SELECT column1, column2, ...
FROM table1
WHERE column IN (SELECT column FROM table2 WHERE condition);

Example

Here is an example of how to use a subquery in the WHERE clause:

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

In this example, the subquery retrieves the department_id of the ‘Sales’ department, and the main query retrieves the names of employees who work in that department.

Full Example

Let’s go through a complete example where we create a database and tables, and use subqueries to demonstrate their usage.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Departments and Employees Tables:

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

4. Insert Data into the Departments and Employees Tables:

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('HR'),
('IT'),
('Finance');

INSERT INTO employees (first_name, last_name, department_id) VALUES
('Rahul', 'Sharma', 1),
('Priya', 'Singh', 2),
('Amit', 'Kumar', 3),
('Neha', 'Verma', 1),
('Sahil', 'Mehta', 4);

5. Query to Find Employees in the Sales Department:

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

Output

In this example, the subquery retrieves the department_id of the ‘Sales’ department, and the main query retrieves the names of employees who work in that department.

Correlated Subqueries

A correlated subquery refers to columns from the outer query. It is executed once for each row processed by the outer query.

Example: Correlated Subquery

SELECT e.first_name, e.last_name, e.department_id
FROM employees e
WHERE e.department_id = (SELECT d.department_id FROM departments d WHERE d.department_name = 'Sales' AND d.department_id = e.department_id);

Output

In this example, the correlated subquery retrieves the department_id for each employee in the ‘Sales’ department, ensuring it matches the department_id of the outer query.

Using Subqueries in the SELECT Clause

You can use subqueries in the SELECT clause to calculate values for each row.

Example: Subquery in SELECT Clause

SELECT first_name, last_name,
       (SELECT department_name FROM departments WHERE department_id = e.department_id) AS department
FROM employees e;

Output

In this example, the subquery retrieves the department_name for each employee based on their department_id.

Important Considerations

• Performance: Subqueries can impact performance, especially correlated subqueries, as they are executed for each row of the outer query. Consider using joins or indexing for optimization.
• Readability: While subqueries can simplify complex queries, they can also make SQL statements harder to read. Ensure your queries remain clear and maintainable.
• Nested Subqueries: You can nest subqueries within other subqueries, but this can further impact performance and readability.

Conclusion

Subqueries in MySQL are powerful tools for performing complex operations within a single query. This chapter covered the syntax for writing subqueries, provided examples of their usage, and discussed important considerations.