MySQL - 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 −

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 −

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 −

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 −

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 −

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 −

Subquery Using a Client Program

We can also perform Subquery using the client program.

Syntax

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
$mysqli->query($sql);

sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
statement.executeQuery(sql);

sub_query = "SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS)"
cursorObj.execute(sql)

Example

Following are the programs −

$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();

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  

NodeJS program
var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
 con.query(sql, function(err, result){
    console.log("Subquery executed successfully...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});  

Subquery executed successfully...!
Table records:
[
  { NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00' },
  { NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00' },
  { NAME: 'Komal', AGE: 22, ADDRESS: 'Hyderabad', SALARY: '4500.00' },
  { NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SubQuery {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records: 
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Hyderabad, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Subquery to fetch the salaries of all customers whose ID is present in the same table
sub_query = f"""
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
"""
cursorObj.execute(sub_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(Decimal('2000.00'),)
(Decimal('1500.00'),)
(Decimal('2000.00'),)
(Decimal('6500.00'),)
(Decimal('8500.00'),)
(Decimal('4500.00'),)
(Decimal('10000.00'),)