What is Nested Select Statement in MariaDB

Nested select statements, normally named subqueries, represent an advanced feature for MariaDB which enables more efficient and thorough querying of the data. Therefore, the nesting of a SELECT statement within another allows us to perform operations and filtering that could be hardly possible with only one query.

In this article, we’ll explore the syntax and multiple examples of nested select statements in MariaDB.

A nested select statement is a kind of select statement that is contained in other SQL statements like SELECT, INSERT, UPDATE or DELETE. The inner SELECT is executed first and then its result is used by the outer expression to do more operations.

Syntax:

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

Explanation:

• column1, column2, … – are the columns you want to retrieve from the outer query.
• table1: It is the table you’re querying in the outer query.
• table2: It is the table you’re querying in the inner query.
• WHERE condition – It is the condition used to filter data in the inner query.

Let’s create an example table and insert some data into it:

Create Table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

Insert Data:

INSERT INTO employees VALUES
(1, 'Minal', 'Pandey', 3, 50000.00),
(2, 'Abhilekh', 'Pandey', 2, 60000.00),
(3, 'Soni', 'Pandey', 1, 75000.00),
(4, 'Sudarshan', 'Pandey', 2, 65000.00),
(5, 'Mahi', 'Pandey', 1, 70000.00);

Output:

Explanation: Table got created successfully.

Example 1: Finding Employees with the Highest Salary

Syntax:

SELECT first_name, salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees 
);

Output:

Explanation:

• The outer query selects first_name, and salary from the employees table and uses the alias e for the table.
• The WHERE clause of the outer query filters rows where the salary column is equal to the result of the nested query.
• The nested subquery retrieves the maximum salary (MAX(salary)) for each department.

Example 2: Retrieving Employees with Salaries Higher Than the Average Salary

Syntax:

SELECT first_name, salary, department_id
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Output:

Explanation:

• The outer query selects first_name, salary, and department_id from the employees table.
• The WHERE clause of the outer query filters out rows where the salary column is bigger than the result of the nested query.
• Nested subquery calculates the average salary (AVG(salary)) of all employees in the employees table.
• The second query from outside then filters the employees based on whether their salary is greater than the average salary calculated by the subquery.

Example 3: Finding Employees with the Second Highest Salary

Syntax:

SELECT first_name,last_name, salary, department_id
FROM employees
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
);

Output:

Explanation:

• The outer query selects first_name, last_name, salary and department_id from the employees table.
• The WHERE clause of the outer query filters rows where the salary column meets the result of the inner subquery.
• The nested subquery picks out the second-highest salary from the employees table.
• DISTINCT salary allows for consideration of only unique salary values.
• ORDER BY salary DESC makes salaries to be sorted in the descending order thus, the second-highest salary shows up at the top.
• LIMIT 1 OFFSET 1 returns the second row (salary) from the sorted list, which is the second highest salary.
• The outer query then filters employees based on whether their salary matches the second-highest salary obtained from the subquery.

Nested select statements offer several benefits:

• Increased Flexibility: Nested queries provide an advanced filtering and data transformation ability that may be already beyond the capacity of one query.
• Improved Readability: Although such complex queries are usually complex to read and understand, they are often more legible and comprehensible when they are used correctly.
• Optimization: MariaDB query optimizer can do that in most cases. It can especially improve performance and speed of the whole query process if nested queries are used and it can perform nested queries not as a series of separate queries.

Nested select statements are an advanced tool in MariaDB, which should be used for doing complex queries and data manipulations. When you are familiar with their structure and the way they operate, then you can take advantage of them to get the data you want from your database in the way that you want in your database applications. Nevertheless, the importance of nested queries should be used wisely and its influence on the application’s performance should be taken into account.