Nested Select Statement in MySQL

The relational databases, the ability to retrieve and manipulate data with precision is a cornerstone of effective database management. MySQL, a popular relational database management system, provides a powerful tool called the Nested SELECT statement that empowers developers to perform complex and versatile data queries.

So, In this article we will learn about the concept of Nested SELECT statements, offering an in-depth exploration of their purpose, syntax, and the scenarios in which they shine. By understanding the intricacies of nesting SELECT statements, developers can harness a potent tool for crafting sophisticated and dynamic queries tailored to the demands of diverse data scenarios.

Nested SELECT Statements

A Nested SELECT statement, also known as a subquery, involves embedding one SELECT statement within another. This nesting allows for the retrieval of data in a hierarchical manner, with the inner SELECT statement executed first, and its results used as a condition or value in the outer SELECT statement.

Syntax of Nested SELECT Statements:

SELECT column1, column2, …FROM table_nameWHERE column_name OPERATOR (SELECT column_name FROM another_table WHERE condition);

Why Use Nested SELECT Statements?

So here are mentioned some major points where the Nested SELECT Statements are used:

1. Complex Filtering: Nested SELECT statements help with complex filtering circumstances. The ability to nest one SELECT statement within another allows developers to apply criteria based on the results of a subquery, resulting in very detailed data extraction.
2. Subquery as a Value: When a subquery returns a single value, it can be regarded as such. This is particularly handy when comparing a column to the maximum or minimum value obtained from another table.
3. Correlated Subqueries: Correlated Subqueries: Nested SELECT statements can reference a column from the outer query. This enables dynamic and context-aware subqueries, increasing the flexibility of data retrieval.
4. Aggregation and Comparison: Nested SELECT statements are valuable when performing aggregate functions on subquery results or when comparing aggregated values to other columns within the same or different tables.

Example of Nested select statement in MySQL

Example 1: Complex Filtering Using Nested Select Statement

So, in the Example we create the DB as GFG where you have a ‘products‘ table and a ‘orders‘ table. You want to retrieve the names of products that have been ordered more than a certain quantity. The quantity threshold is dynamic and is determined by the highest quantity ordered for any product.

-- SQL Code 

-- Create the database
CREATE DATABASE IF NOT EXISTS GFG;
USE GFG;

-- Create sample tables
CREATE TABLE IF NOT EXISTS products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  quantity INT
);

-- Insert sample data
INSERT INTO products VALUES
  (1, 'Laptop'),
  (2, 'Desk Chair'),
  (3, 'Notebook'),
  (4, 'Headphones'),
  (5, 'Monitor');

INSERT INTO orders VALUES
  (1, 1, 10),
  (2, 2, 5),
  (3, 1, 15),
  (4, 3, 8),
  (5, 4, 12),
  (6, 5, 6);

Query using a Nested SELECT Statement for complex filtering:

-- Query using a Nested SELECT Statement for complex filtering 
SELECT p.product_id, p.product_name, o.quantity 
FROM products p JOIN orders o 
ON  p.product_id = o.product_id 
WHERE o.quantity > (SELECT MAX(quantity) FROM orders);
SELECT MAX(quantity) FROM orders;

Output:

output

Explanation:

The output of the provided SQL queries is a maximum order quantity of 15, as obtained from the second query (SELECT MAX(quantity) FROM orders). This value is used as a condition in the first query, resulting in the selection of products associated with order quantities greater than 15 from the “products” and “orders” tables. Therefore, the output from the first query includes product details for orders with quantities exceeding the maximum order quantity of 15.

Query Retrieving Product and Order Information:

SELECT p.product_id, p.product_name, o.quantity 
FROM products p JOIN orders o 
ON p.product_id = o.product_id;

Output:

output

Explanation:

The output of the provided SQL query is a combination of product and order information. It retrieves the product_id, product_name, and quantity columns by joining the “products” and “orders” tables on the common column product_id. The result includes each product’s details alongside the respective order quantities, showing the association between products and their corresponding orders.

Example 2: Used Nested select Statement

So, here we are created the DB as Practice and we have the table ‘guests’ and some parameter with their values as you can see in the below code-

-- SQL Code 

-- Create the database
CREATE DATABASE IF NOT EXISTS Practice;
USE Practice;

-- Create a simplified 'guests' table
CREATE TABLE IF NOT EXISTS guests (
  guest_id INT PRIMARY KEY, 
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  total_visits INT
);

-- Insert sample data
INSERT INTO guests (guest_id, first_name, last_name, total_visits)
VALUES
  (1, 'Judy', 'Hopps', 168),
  (2, 'Nick', 'Wilde', 1),
  (3, 'Duke', 'Weaselton', 4),
  (4, 'Tommy', 'Yax', 70),
  (5, 'Lizzie', 'Yax', 80);

Query to calculate the average visits:

SELECT AVG(total_visits) AS average_visits FROM guests;

Output:

output

Explanation:

This output indicates that, on average, guests in the “guests” table have approximately 46.6000 total visits. The actual result may vary based on the specific data in your “guests” table.

Query using nested statements to calculate the average visits and retrieve guests with visits above the average:

SELECT first_name, last_name, total_visits  FROM guests 
WHERE total_visits > ( SELECT AVG(total_visits)  FROM guests);

Output:

Output

Explanation:

The expected output would include Judy Hopps, Tommy Yax, and Lizzie Yax because their total visits (168, 70, 80) are greater than the calculated average.

Conclusion

So, Overall the evolving environment of database management, nested SELECT statements are a versatile and important tool. These statements enable developers to embed one SELECT statement within another, providing a level of flexibility and precision in data retrieval that is critical for dealing with complex scenarios. As we walk through practical examples and use scenarios, developers will obtain a better knowledge of how to successfully use Nested SELECT queries. With this understanding, the way to creating sophisticated and powerful queries tailored to specific data requirements becomes obvious, ultimately improving the efficiency and efficacy of database interactions in MySQL.