Updated May 11, 2023
Introduction to MySQL Subquery
In MySQL, a Subquery has defined as a SELECT SQL Statement used inside another SQL statement to calculate the results of outer queries. A Subquery, in SQL, is an inner query that is placed within an outer SQL query using different SQL clauses like WHERE, FROM, and HAVING, along with statement keywords such as SELECT, INSERT, FROM, UPDATE, DELETE, SET, or DO, and accompanied with expressional operators or logical operators.
Following are the types of subquery with respective result details:
- Single Row Subquery: It either returns zero or a single row
- Multiple Row Subquery: It returns one or multiple rows of a table
- Multiple Column Subquery: It returns one or multiple columns
- Correlated Subqueries refer to one or more columns in the outer SQL query.
Syntax of MySQL Subquery
We can say that a Subquery or Inner Query is a SQL query that can be used as a condition for the main SQL query outside that helps to restrict the data rows to be fetched from the tables embedded within the WHERE clause. This query could not be bounded in a set function.
For example:
SELECT * FROM tablename1 WHERE column1 = ANY (SELECT column1 FROM tablename2 WHERE tablename2……)So, MySQL looks outside the subquery and finds tablename1 in the outer query.
Thus, like JOINS, a Subquery combines table data from different tables into a single result set. But JOINS returns rows, and the subquery returns either a single value as a result of a row set. Hence, the primary use of subquery can be to compute an instant value for another query to be executed.
Working on MySQL Subquery
Let us explain how to use the MySQL Subquery to write any compound queries and learn the correlated Subquery concept.
In MySQL, we can use subqueries in any SQL query expression, but we need to enclose them within parentheses.
Suppose we have the following SQL query, which provides us with the persons who work in the IT sector located in Delhi.
SELECT PersonName FROM PersonDb WHERE PersonID IN (SELECT PersonID FROM PersonIT WHERE City='Delhi');In the above SQL Statement, the subquery first returns all the PersonIDs of PersonIT located in the city of Delhi. Then the outer query selects all the names from the PersonDb table that work in PersonIT whose PersonID is matched from the result set of the inner query. When we run this query, we can say that the query inside the outer executes first, and the result it returns is used as input for the outer query.
Types of MySQL Subquery
Following are the types of subquery with syntaxes and examples:
1. MySQL subquery on WHERE Clause
Let us prepare a demo data database table named Customer, having information about customers and payment amounts.
The table has CustomerID, Name, Payment_purpose, CustomerNum, and Amount fields.
CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, Name varchar(255), CustomerNum int, Payment_purpose varchar(255), Amount int );And also inserted some data samples as follows:
INSERT INTO Customers (CustomerID, Name, CustomerNum, Payment_purpose , Amount) VALUES ('1', 'Nikhil', '21', 'Rent', '4000');
We have used the following query to fetch the details required using a Subquery with comparison operators to return a value and with the expression in the WHERE clause:
SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount = (SELECT MAX(amount) FROM Customers);Output:
This subquery with the WHERE clause returns the customer details whose payment amount is maximum in the table Customer.
In addition to this, you can also execute a subquery using the equality operator and another comparison like greater than (>) or smaller than (<).
For example, using a subquery, let’s use a subquery to find the customer details from the table where the payment amount is greater than average.
SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount > (SELECT AVG(amount) FROM Customers);Here, we have used the AVG aggregate function in SQL inner query statement, which produces the following results through an outer query.
Output:
2. MySQL Subquery with operators IN and NOT IN
When a subquery provides more than a single value, you can add other operators with WHERE clauses, such as IN and NOT IN.
We have two tables suppose ‘Customers’ (CustomerID, Name, Payment_purpose, CustomerNum, Amount) and ‘Orders’ (OrderID, OrderNum, Status, CustomerNum).
CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, CustomerNum int, status varchar (255), OrderNum int, Budget int );
We will first create the table, then insert some demo data.
Now let us find the customers who have not placed any orders using the subquery with the NOT IN operator.
SELECT Name, CustomerNum FROM Customers WHERE CustomerNum NOT IN (SELECT DISTINCT CustomerNum FROM Orders);Output:
3. MySQL subquery with FROM clause
When using a subquery with the FROM clause, the database engine treats the resulting set as a temporary table where each row represents a record. We also refer to this temporary table as a derived or materialized subquery.
Using the subquery, we can also find the Minimum, Maximum, and Average number of items from the Budget table.
SELECT I.CustomerID, I.NAME, I.Payment_purpose, I.Amount FROM (select AVG(Budget) as averageBudget from Orders) as Budget, Customers as I WHERE I.Amount > Budget.averageBudget;Output:
4. MySQL Correlated Subquery
As mentioned above, a correlated subquery is an independent query. It means that you can use it as a standalone query. The subquery evaluates the data from the outer query’s result set for each row.
For example,
SELECT OrderNum , COUNT(OrderNum) AS items FROM Orders GROUP BY OrderNum;Output:
5. MySQL subquery with EXISTS & NOT EXISTS
The EXISTS and NOT EXISTS operators in SQL subquery return a Boolean value as a result, which can be either TRUE or FALSE. The following is the syntax to use EXISTS in a subquery:
SELECT * (column1, …) FROM Tablename WHERE EXISTS (subquery);For example,
SELECT Name FROM Customer C WHERE EXISTS (SELECT * FROM Orders WHERE CustomerNum = C.CustomerNum);Output:
EXISTS and NOT EXISTS check whether the rows exist or not in the result set and return TRUE or FALSE values, respectively.
Conclusion
In MySQL, you can nest a subquery inside the WHERE clause of an outer SQL SELECT statement.. The subquery can be added to further filter the result set using comparison operators like <,>, =, IN or BETWEEN, etc.
The database management system executes the Subquery first and then uses its result to generate the result set for the outer query. Thus, the statement is true. The SQL Subquery can return a scalar, column, row, and table subqueries in the result set rows, providing a condition for outer query and giving the final required result from the table in MySQL.
Recommended Articles
We hope that this EDUCBA information on “MySQL Subquery” benefited you. You can view EDUCBA’s recommended articles for more information.
