Khóa học miễn phí MySQL – SubQuery nhận dự án làm có lương

MySQL – SubQuery

Table of content


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

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

To fetch the data using subqueries through a JavaScript program, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows −

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

To fetch the data using subqueries through a Java program, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows −

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

To fetch the data using subqueries through a Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows −

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

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  
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);
    });
});  

Output

The output obtained is as shown below −

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

Output

The output obtained is as shown below −

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

Output

The output obtained is as shown below −

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc