Nested Select Statements in MySQL for Enhanced Query
-
Use the Inner-Outer Query Method to Implement the Nested
SELECTStatement in MySQL -
Implement the Nested
SELECTStatement as an Alternative toJOINSin MySQL
The SELECT statement is the primary function for retrieving data from a database using SQL. However, there are use-cases where the results of an executed SELECT operation need to be manipulated or retrieved.
The nested SELECT statement approach offers a convenient solution for such advanced queries.
Similar to general programming languages, SQL offers multiple ways of achieving the same results. Nested SELECT statements can serve as an alternative to JOINS.
However, there are cases where using nested SELECT statements is the optimal choice. This tutorial explores these concepts and use-cases in a few practical examples.
Use the Inner-Outer Query Method to Implement the Nested SELECT Statement in MySQL
The inner-outer query method in MySQL is similar in operation to a nested expression or function in most general programming languages. The inner query executes first then its result is passed to a wrapping outer query, typically via a WHERE clause.
For example, let’s create a sample table Employee with an id, name, title, and salary column.
-- Initializing and selecting a database
CREATE DATABASE test_company;
USE test_company;
-- creating a sample employees table
CREATE TABLE employees(
id INT AUTO_INCREMENT,
name VARCHAR (255),
title VARCHAR(255),
salary INT,
PRIMARY KEY (id)
);
-- populating the employees' table with information
INSERT INTO employees (name, title, salary) Values
('James Maddison','Computer Engineer',80000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000),
('Carlos Rodriguez','Data Engineer',100000);
-- previewing the employees' table
SELECT * FROM employees;
Output:
id name title salary
1 James Maddison Computer Engineer 80000
2 Matthew Defoe Software Architect 150000
3 Daniel Jameson Software Engineer II 95000
4 Jules Reddington Senior Software Engineer 120000
5 Carlos Rodriguez Data Engineer 100000
-----------------------------------------------------------------------------------------
1 row(s) affected
0 row(s) affected
0 row(s) affected
5 row(s) affected Records: 5 Duplicates: 0 Warnings: 0
5 row(s) returned
To find the details of Employees that currently earn more than the average salary in the company.
We first calculate the average salary in the company using the AVG() function, then we filter the Employees table based on the returned average value.
Typically, the more computational query is the inner query for improved efficiency and logic. This approach ensures the outer sub-query only filters a significantly reduced table of values.
More details on choosing an appropriate inner and outer sub-query are available via this reference from w3resource.
/* Inner query
SELECT AVG(salary) FROM employees;
Outer query
SELECT * FROM employees
WHERE salary > (Inner query)
*/
-- Full Query
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC; -- starting from the highest-paid
Output:
id name title salary
2 Matthew Defoe Software Architect 150000
4 Jules Reddington Senior Software Engineer 120000
-----------------------------------------------------------------------------------------
0.032 sec / 0.000 sec
2 row(s) returned
The query returns two employees who currently earn above the average salary, i.e., $109,000.
Implement the Nested SELECT Statement as an Alternative to JOINS in MySQL
As a replacement for implementing JOINS when retrieving data from more than one table, nested SELECT can be a good alternative.
Add another table named Projects related to the Employees table via a foreign key. Also, let’s add the extra required column and foreign key constraint to the Employees table.
-- Adding a new projects table
CREATE TABLE projects(
project_id INT,
project_name VARCHAR(255) DEFAULT NULL,
programming_language VARCHAR(255) DEFAULT 'N/A',
PRIMARY KEY (project_id)
);
-- This modifies the employees' table and adds a foreign key
ALTER TABLE employees
ADD project_id INT,
ADD FOREIGN KEY (project_id) REFERENCES projects(project_id);
-- Populating the projects table and updating the employees' table with project_ids
INSERT INTO projects(project_id, project_name, programming_language) VALUES
(100, 'Movie Recommendation System', 'Python, Javascript, R'),
(105, 'Deep Learning Data Pipeline 10', 'Python, R, SQL'),
(107, 'Web-Based Diagnostic Support AI System', 'Python, Javascript, Html, CSS');
INSERT INTO projects(project_id, project_name) VALUES
(311, 'Computer Hardware Revamp'),
(109, 'Implementing an Advanced Conversational Agent for Effex.inc');
SELECT * FROM projects;
Output:
project_id project_name programming_language
100 Movie Recommendation System Python, Javascript, R
105 Deep Learning Data Pipeline 10 Python, R, SQL
107 Web-Based Diagnostic Support AI System Python, Javascript, Html, CSS
109 Implementing an Advanced Conversational N/A
Agent for Effex.inc
311 Computer Hardware Revamp N/A
-----------------------------------------------------------------------------------------
0 row(s) affected
5 row(s) affected Records: 5 Duplicates: 0 Warnings: 0
5 row(s) affected Records: 5 Duplicates: 0 Warnings: 0
5 row(s) returned
Now, update the project_id column in the employees table.
-- NOW relating employees to projects
UPDATE employees
SET project_id = 311
WHERE id = 1;
UPDATE employees
SET project_id = 109
WHERE id = 2;
UPDATE employees
SET project_id = 100
WHERE id = 3;
UPDATE employees
SET project_id = 107
WHERE id = 4;
UPDATE employees
SET project_id = 105
WHERE id = 5;
SELECT * FROM employees;
Output:
id name title salary project_id
1 James Maddison Computer Engineer 80000 311
2 Matthew Defoe Software Architect 150000 109
3 Daniel Jameson Software Engineer II 95000 100
4 Jules Reddington Senior Software Engineer 120000 107
5 Carlos Rodriguez Data Engineer 100000 105
-----------------------------------------------------------------------------------------
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
5 row(s) returned
We can then select values from both tables using a nested SELECT statement. Let us find the name, title, salary, project name, and programming languages of employees that use JavaScript for their job.
First, the inner query gets values from the projects table. Then, the outer query fetches relevant data from the Result-Set for generating the needed view.
-- Selecting details of employees that use Javascript (Inner Query)
SELECT project_id, project_name FROM projects WHERE programming_language LIKE '%Javascript%';
Output:
project_id project_name
100 Movie Recommendation System
107 Web-Based Diagnostic Support AI System
-----------------------------------------------------------------------------------------
2 row(s) returned
Now, the full query.
/*
-- Inner query
SELECT project_id, project_name, programming_language FROM projects WHERE programming_language LIKE '%Javascript%';
-- Outer query
SELECT E.name AS 'Employee Name', E.title AS 'Job Title', E.Salary AS 'Salary',
P.project_name AS 'Current Project', P.programming_language AS 'Programming Language'
FROM employees AS E,
(Inner query) AS P
WHERE E.project_id = P.project_id;
*/
-- Full query
SELECT E.name AS 'Employee Name', E.title AS 'Job Title', E.Salary AS 'Salary',
P.project_name AS 'Current Project'
FROM employees AS E,
(SELECT project_id, project_name FROM projects WHERE programming_language LIKE '%Javascript%') AS P
WHERE E.project_id = P.project_id;
Output:
Employee Name Job Title Salary Current Project
Daniel Jameson Software Engineer II 95000 Movie Recommendation System
Jules Reddington Senior Software Engineer 120000 Web-Based Diagnostic Support AI System
-----------------------------------------------------------------------------------------
2 row(s) returned
This result is also achievable with a well-written JOIN statement. However, there is a choice of a trade-off between efficiency and convenience.
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub