Examples of Nested Select Statements

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, 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.