How to Work with MySQL Subqueries on Alibaba Cloud

In this tutorial, we will show you how to work with subqueries on your MySQL server hosted either on an Alibaba Cloud ECS or ApsaraDB RDS for MySQL instance.

By Francis Ndungu, Alibaba Cloud Community Blog author.

In MySQL, a subquery is an SQL statement nested within another query. A Subquery can also be referred to as an inner query while the main query in this case can be referred as the outer query. The former is executed first during a database operation to provide results to the parent query.

You may use a subquery in SELECT, FROM and WHERE clauses. Similarly, you may use the results of a subquery when inserting, deleting or updating records in a database. We will demonstrate these later in the article.

Using MySQL subqueries is inevitable in a production environment since they enhance code readability and run statements more efficiently.

They also aid in the isolation of specific pieces of SQL statements in a database operation. Also, a subquery may be used as an alternative to complex unions and joins that would otherwise require a lot of programming skills.

In this tutorial, we will show you how to work with subqueries on your MySQL server hosted either on an Alibaba Cloud ApsaraDB RDS for MySQL or Elastic Compute Service (ECS) instance.

Prerequisites

To follow along with this tutorial, you will require the following:

An Alibaba Cloud account. You can sign up now to enjoy up to $1200 worth of free credit to test over 40 Alibaba Cloud products.
A MySQL database server hosted either on Alibaba Cloud ECS instance or ApsaraDB for RDS
Root access to your MySQL server.

Procedure

Step 1: Creating Sample Data

For better illustration, we will create sample data to run subqueries. First, log in to your MySQL server:

$ mysql -uroot -p

Key in your password when prompted and hit Enter to continue. Then, create a sample database and call it 'sample_db':

mysql> create database sample_db;

Switch to the database:

mysql>use sample_db;

Next create a 'categories' table:

mysql>create table categories(
category_id BIGINT,
category_name VARCHAR(45),
PRIMARY KEY (category_id)
);

Add some values to the 'categories' table:

mysql>insert into categories (category_id, category_name) values('1001', 'SPEAKERS');
mysql>insert into categories (category_id,  category_name) values('2002', 'DISCS');
mysql>insert into categories (category_id, category_name) values('3003', 'ACCESSORIES');
mysql>insert into categories (category_id, category_name) values('4004', 'CLOTHING');
mysql>insert into categories (category_id, category_name) values('5005', 'COMPUTERS');

Confirm the data by running the code below:

mysql> select * from categories;

The output is as follows:

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|        1001 | SPEAKERS      |
|        2002 | DISCS         |
|        3003 | ACCESSORIES   |
|        4004 | CLOTHING      |
|        5005 | COMPUTERS     |
+-------------+---------------+

Then, create 'products' table:

mysql>create table products(
product_id BIGINT,
category_id BIGINT,
product_name VARCHAR(45),
price DOUBLE,
PRIMARY KEY (product_id)
);

Add some sample data to the 'products' table;

mysql>insert into products (product_id, category_id, product_name, price) values('1', '1001', 'BLUETOOTH SPEAKER','18.60');
mysql>insert into products (product_id, category_id,  product_name, price) values('2', '2002', 'SOLID STATE DRIVE','52.30');
mysql>insert into products (product_id, category_id, product_name, price) values('3', '3003', 'WIRELESS MOUSE','29.90');

Check the presence of data by running the code below:

mysql> select * from products;

The output is as follows:

+------------+-------------+-------------------+-------+
| product_id | category_id | product_name      | price |
+------------+-------------+-------------------+-------+
|          1 |        1001 | BLUETOOTH SPEAKER |  18.6 |
|          2 |        2002 | SOLID STATE DRIVE |  52.3 |
|          3 |        3003 | WIRELESS MOUSE    |  29.9 |
+------------+-------------+-------------------+-------+

Step 2: Creating Subquery from SELECT Clause

With the sample data in place, we can start running a few examples to test subqueries. We are going to start off with a subquery within a SELECT statement as shown below:

mysql>SELECT
Column1, 
column2,
([subquery_statement]) as [column_name]
FROM [table_name]

To put this in to a better perspective, let's list all products in our database together with the associated categories without using the UNION clause, to do this, we can run the below query:

mysql>SELECT
product_name,
price,
(select category_name from categories where category_id=products.category_id) as category_name
FROM products;

In the above query, we are retrieving the category name based on the category_id associated with each product. Once you run the above statement, you should get an output similar to the one shown below.

+-------------------+-------+---------------+
| product_name      | price | category_name |
+-------------------+-------+---------------+
| BLUETOOTH SPEAKER |  18.6 | SPEAKERS      |
| SOLID STATE DRIVE |  52.3 | DISCS         |
| WIRELESS MOUSE    |  29.9 | ACCESSORIES   |
+-------------------+-------+---------------+

Step 3: MySQL Subquery in a FROM Clause

Next, we are going to execute a subquery nested in the FROM clause. We can use the below syntax:

mysql> Select 
column1,
column2,
column..n,
from ([subquery_statement]);

This kind of subquery is very powerful and can be used for example to retrieve products with a higher price tag than the average price of all products.

Consider the following example below:

mysql>Select *
from  products where price>(select AVG(price) from products);

The output is as follows:

+------------+-------------+-------------------+-------+
| product_id | category_id | product_name      | price |
+------------+-------------+-------------------+-------+
|          2 |        1002 | SOLID STATE DRIVE |  52.3 |
+------------+-------------+-------------------+-------+

Step 4: MySQL Subquery in a WHERE Clause

We can also run a subquery in a WHERE clause using the below syntax:

mysql> select 
Column1,
Column2,
Column..n
From [table_name]
Where ([subquery_statement]);

For instance, we may use a subquery to retrieve all category names that have a matching product from the 'products' table

mysql>SELECT
category_name
FROM categories
WHERE category_id IN (select category_id from products);

The output is as follows:

+---------------+
| category_name |
+---------------+
| SPEAKERS      |
| DISCS         |
| ACCESSORIES   |
+---------------+

Step 5: MySQL Subquery in an INSERT Statement

Another great way you can use a subquery is during an INSERT operation.

mysql>INSERT into [table_name] 
column1, 
column2, 
column..n)
values
(
'1',
'[(subquery_statement)]',
'column..n value',
);

For instance, we can insert a new product and associate it with the next 'product_id'. To do this, our subquery will just select the highest 'product_id' from the products table and add one value to the equation as shown below.

However, we must alias the table name in the subquery to avoid getting an error:

mysql>INSERT into products 
(
product_id,
category_id, 
product_name, 
price
) 
Values
(
(select max(x.product_id)+1 from products as x), 
'1001',
 'COMPUTER HEADSET',
'69.40'
);

You may check whether the request was successful by running the query below:

+------------+-------------+-------------------+-------+
| product_id | category_id | product_name      | price |
+------------+-------------+-------------------+-------+
|          1 |        1001 | BLUETOOTH SPEAKER |  18.6 |
|          2 |        2002 | SOLID STATE DRIVE |  52.3 |
|          3 |        3003 | WIRELESS MOUSE    |  29.9 |
|          4 |        1001 | COMPUTER HEADSET  |  69.4 |
+------------+-------------+-------------------+-------+

As you can see above, a new record was successfully added with the next 'product_id' in the series.

Step 6: MySQL Subquery in an UPDATE Statement

Just like we have done in an MySQL INSERT statement, a subquery works pretty well in an UPDATE statement as shown below:

mysql> Update [table_name] set
column1='value1',
column2='value2',
column3=[(subquery_statement)],
column..n='value..n';

The example below, updates the products price by 20%.

mysql>update products
set 
price=(select (price*1.20) from (select product_id from products) as x where x.product_id = products.product_id);

Step 7: MySQL Subquery in a DELETE Statement

MySQL subqueries are usually used in a DELETE statement to preserve the referential integrity of a database. This ensures that the relationships of records on the tables are consistent. For instance, if you accidentally delete a category that has some associated products, this will mess up the database.

To overcome this problem, you can use a subquery in a DELETE statement. This forces the database server to check the presence of related records before the DELETE operation is run.

The following is an example:

mysql>DELETE from categories
Where category_id NOT IN (select category_id from products);

The output is as follows:

Query OK, 2 rows affected (0.01 sec)

The output above confirms that only two categories were deleted because they didn't have a related products table.

Some Considerations about MySQL Subqueries

Always enclose subquery statements with parenthesis.
The inner query is run first during the MySQL transaction in order to avail the subquery results to the main query.
You may use comparison operators (such as >,<, =, <=, >=) and multiple row operators (such as IN) with subqueries.
You cannot modify a table and select from the same table while using a subquery in an INSERT, UPDATE and DELETE statement. In such a case, you must use an alias of the table.
If a subquery returns more than one record in a statement that expects a single row, an error will occur.

Conclusion

In this tutorial, we have shown you how to use MySQL subqueries on databases hosted on Alibaba Cloud ECS instances or ApsaraDB for RDS. You can use the samples in this guide to extend the functionality of your applications.

Remember, when it comes to MySQL subqueries, there is definitely more you can do than meets the eye. The best thing is to practice with the sample data that we have provided on this guide. If you are new to Alibaba Cloud, you can sign up now and get up to $1200 of free trial credit to test the tutorial.

Community

How to Work with MySQL Subqueries on Alibaba Cloud

Prerequisites

Procedure

Step 1: Creating Sample Data

Step 2: Creating Subquery from SELECT Clause

Step 3: MySQL Subquery in a FROM Clause

Step 4: MySQL Subquery in a WHERE Clause

Step 5: MySQL Subquery in an INSERT Statement

Step 6: MySQL Subquery in an UPDATE Statement

Step 7: MySQL Subquery in a DELETE Statement

Some Considerations about MySQL Subqueries

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

AnalyticDB for MySQL

PolarDB for MySQL

ApsaraDB RDS for MySQL

ApsaraDB for MyBase