Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

SubQuery

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

SubQuery
The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.
Subquery with the SELECT Statement
A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery.
Syntax
Following is the basic syntax of a subquery within a SELECT statement 
SELECT column1, column2, ...
FROM table1
WHERE columnN operator 
(SELECT column_name FROM table2 WHERE condition);
Example
First, let us create a table with the name CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);
Now, let us insert values into the above-created table using the INSERT statement as shown below 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The CUSTOMERS table displayed is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID's match with the ID's in the same table 
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
Output
The output for the query above is produced as given below 
SALARY
2000.00
1500.00
2000.00
6500.00
8500.00
4500.00
10000.00
Subquery with the INSERT Statement
We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table.
Syntax
Following is the basic syntax of a subquery within an INSERT statement 
INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;
Example
Before performing the subqueries with INSERT statement, let us create a table named "CUSTOMERS_BKP" with a similar structure as CUSTOMERS table 
CREATE TABLE CUSTOMERS_BKP(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25) NOT NULL, 
   SALARY DECIMAL(18, 2), 
   PRIMARY KEY(ID)
);
Now, let us insert all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query 
INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);
Output
The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table 
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
Verification
Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement 
SELECT * FROM CUSTOMERS_BKP;
As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Subquery with Comparison Operator
The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators.
Syntax
Following is the basic syntax of a subquery with comparison operators 
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE] .....)
Example
The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs.
SELECT * FROM CUSTOMERS_BKP
WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP
WHERE AGE > 23);
Output
The output for the query above is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
6500.00
7
Muffy
24
Indore
10000.00
Subquery with IN or NOT-IN Operator
The MySQL subqueries with IN/NOT-IN operators are used to filter data based on whether values from one query match or do not match values from another query 
IN matches any value from the list
NOT-IN excludes any value from the list.
Example
The following query retrieves all the records from the CUSTOMERS table where the ADDRESS is not "Hyderabad" by comparing it to addresses in the CUSTOMERS_BKP table 
SELECT * FROM CUSTOMERS
WHERE ADDRESS NOT IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");
Output
Following is the output of the above query 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
Example
Now, the following query retrieves all the rows from the CUSTOMERS table where the ADDRESS is "Hyderabad" by using a subquery to fetch all addresses that match "Hyderabad" from the CUSTOMERS_BKP table 
SELECT * FROM CUSTOMERS
WHERE ADDRESS IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");
Output
On executing the given query, the output is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
6
Komal
22
Hyderabad
4500.00
Subquery Using a Client Program
We can also perform Subquery using the client program.
Syntax
PHPNodeJSJavaPython
To fetch the data using subqueries through a PHP program, we need to execute the "SELECT" statement using the mysqli function query() as follows 
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
printf("Table records: \n");
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f",
        $row['ID'],
        $row['NAME'],
        $row['AGE'],
        $row['ADDRESS'],
        $row['SALARY']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Table records:
Id: 4, NAME: Chaitali, AGE: 25, ADDRESS: Mumbai, SALARY: 6500.000000
Id: 5, NAME: Hardik, AGE: 27, ADDRESS: Bhopal, SALARY: 8500.000000
Id: 6, NAME: Komal, AGE: 22, ADDRESS: Hyderabad, SALARY: 4500.000000
Id: 7, NAME: Muffy, AGE: 24, ADDRESS: Indore, SALARY: 10000.000000  
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

SubQuery

Subquery with the SELECT Statement

Syntax

Example

Output

Subquery with the INSERT Statement

Syntax

Example

Output

Verification

Subquery with Comparison Operator

Syntax

Example

Output

Subquery with IN or NOT-IN Operator

Example

Output

Example

Output

Subquery Using a Client Program

Syntax

Example

Output