MySQL Subqueries explained with examples
This article about MySQL SubQueries has a brief introduction with advantages and examples. MySQL subqueries are also known as Inline MySQL Queries or Nested MySQL Queries or Inner Queries. This article includes the following:
- Introduction to MySQL subqueries in Hindi
- Advantages of MySQL subqueries in Hindi
- Defining MySQL subqueries in Hindi
Introduction to MySQL Subqueries
A subquery is a query that is inside another query. These are also called inner queries and also nested Queries. The queries that contain subquery are called outer queries.
A subquery can be in a SELECT, INSERT, UPDATE and DELETE statements. Most subqueries are written after the WHERE and FROM clause or to fetch certain data as a column.
Subqueries bring the concept of dynamic execution to SQL. The outer query uses the result from subquery at execution time, in its execution. The result of the subquery execution is dynamically passed to the outer query.
If you do not use a subquery, then firstly you have to execute the inner query separately. After that, the main/outer query will be executed using its results. This will be quite a lengthy task and for this, you will have to fire query more than once. In such situations, subqueries make your work easier.
In one subquery, you can also define another subquery. That is why they are also named nested Queries. As the name says, they are queries inside queries. Subqueries are always written in brackets (). As per my experience, Subqueries are mostly used in select Statements. Though they are also used in other statements too.
Let us now explore the advantages of using MySQL subqueries.
Advantages of Subqueries
Below are some advantages of subqueries.
- Other queries such as joins and unions are very complex which most people do not understand. Whereas, subqueries are in a structured format.
- Subqueries divide a large query into different parts.
- Subqueries are more readable than other queries and are easily understood.
- You do not need to store the results of subqueries in memory like views.
- You can also use comparison operators in subqueries.
- We can use Subqueries either in fetching values or in where clause.
Types of Subqueries
Subqueries are divided into 3 categories according to their results.
When a subquery returns a single value, it is called scalar subquery.
When a subquery returns a row, it is called a row subquery.
A subquery that returns a column is called a column subquery.
A table subquery is a subquery that returns a table.
How to define MySQL Subqueries
Below is the general syntax to define subqueries in SELECT statements of MySQL.
Select column1, column2, .. (SELECT sub_col1 FROM sub_table WHERE a_sub_col = a_main_col) AS sub_result FROM main_table;
As I told you earlier, subqueries can be defined in SELECT, INSERT, UPDATE and DELETE statements.
After defining these statements and other clauses, you name the table and then place a FROM or WHERE clause. After this, you define the subquery in brackets. This is explained through an example below.
Subquery Example with WHERE Clause
Before Going towards the demonstration Please import the database below to get the results that I will use in the following codes.
Suppose you have created the Employees table like below.
-- Table structure for table `employees`
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(100) NOT NULL,
`salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table `employees`
INSERT INTO `employees` (`id`, `name`, `salary`) VALUES
(10025, 'Aabha', 25000),
(10026, 'Daisy', 21000),
(10027, 'Dayana', 26000),
(10028, 'Jackson', 27000),
(10029, 'Josiah', 28000),
(10030, 'Justin ', 26500),
(10031, 'Jeffrey', 27500),
(10032, 'Aadhila', 28500),
(10033, 'Aadi', 38500),
(10034, 'Aamber', 28500);
Now you want to show the names of the employees whose salary is less than the average. To complete this task, you have to take 2 steps.
Firstly, you will calculate the average of salary column. You can do this in this way.
SELECT AVG(salary) FROM `employees`;
So here we get the average salary is 27650. After this, you will use this result to retrieve the names of those employees whose salary is less than 25000. This is how it can be done.
SELECT name FROM `employees` WHERE salary < 27650;
When you execute the above query, you will get the name of an employee whose salary is less than the average. I have used two queries to get this result.
Now, let’s see how this task can be completed easily in just a single statement with the help of subqueries.
SELECT name FROM `employees` WHERE salary < (SELECT AVG(salary ) FROM `employees`);
In the query above, the first subquery defined inside brackets will be executed by MySQL. This is a scalar subquery. The subquery will then be replaced by its result. When this happens, a single query will be saved which will execute and show MySQL result.
As you can see the result will be the same in both the methods but using subquery makes your work easy and automated.
Subquery Example with FROM Clause
So far you have seen how you can use subquery with WHERE clause. Let us now see how you can use subquery with FROM clause.
Suppose you want to show the maximum salary from the Employees table shown above. You can do this in this way with the help of subquery.
SELECT MAX(salary) AS max_salary FROM (SELECT salary FROM employees ) AS base_table;
In the query given above, the inner query will return the Salary column of the Employees table. This is a column subquery. This query will result in that maximum salary with an alias of ‘max_salary’ FROM the salaries fetched from ‘employees’ table. When we use such queries, we have to use an alias for the subquery too, that is in this case base_table.
After this, the outer query will return as the maximum salary result from this column.