MySQL 8.0.22 | Select Subquery + Auto_increment

3 min readNov 30, 2020

Subquery — The query inside of the query.

Hey guys! Welcome to another Select session with me. Today we’re going to take a look at How to use Subquery.

[Subquery]

Subquery is the query inside of the query, and it has to include Select clause and a From clause just like the regular select statement. The way to use subquery is just simply put select query in the parentheses and the put in in the select query.

Like that, you can choose one of those numbers to put the subquery. It helps you to select more complicated combo of columns. I think it’d be better when you do some practice to understand it. So, Let’s get this started!

[Practice Questions]

//Copy the Queries down below and solve the questions.create database shop;
use shop;
create table member(
no int primary key auto_increment,
name varchar(5) not null,
addr varchar(2),
gender varchar(1),
grade int default 1,
point int default 100);insert into member values
(default,'Nina','NY','w',2,300),
(default,'Tom','NY','m',1,200),
(default,'Amy','LA','w',1,150),
(default,'Jake','DC','m',2,100),
(default,'Lisa','LA','w',2,300),
(default,'Nicky','NY','w',3,400),
(default,'Tim','LA','m',4,300);

Do you see I put Auto_increment on the no right after the primary key ? It means I’m gonna put ascending number starts from 1 in the no. And then I put default in each tuple. Then you will see each tuple has it’s own number when you check the query with the select * from member . You can use Auto_increment only once in each table.

Q. Select the highest point of each gender and the person’s name.

It might look like you don’t even need a subquery to solve it. I’ll show you what happens if you don’t use subquery. And I’m going to use group by , so if you don’t know how to use it, please checkout my last post!

MySQL 8.0.22 | Select Group By + Having, Count, As

Group By — Group tuples that have same value in certain columns. You can specify the group with having clause.

studentkim.medium.com

Wrong Answer :mysql> select name, max(point), gender
    -> from member
    -> group by gender;
+------+------------+--------+
| name | max(point) | gender |
+------+------------+--------+
| Nina |        400 | w      |
| Tom  |        300 | m      |
+------+------------+--------+
2 rows in set (0.00 sec)

It looks like it worked! Everything is right except the names. It should be “Nicky” and “Tim” who has the highest point of each gender, but it just returned the 2 names that I inserted first. That’s why we have to use subquery here. Let me take you to my problem solving process.

1.Divide the questions.
I’d prefer to divide the question into two sentences. It goes like
1. “select the name, point, gender.”,
2. “select the highest point of each gender.”
I will write the queries based on these two.

1.
select name, point, gender from member2.
select max(point), gender from member group by gender

2. Figure out which query to be the subquery.
Now you need to figure out which select statement should be the subquery. In the question they said “Get the name, gender and point”. So the first one should be the original query which includes all. So I’ll put no.2 select statement into no.1.
Then, all we have to do is making the point and the max(point) match. So I’ll put the subquery in by using where clause.

mysql> select name, point, gender
    -> from member
    -> where (point,gender) in (select max(point),gender from member group by gender);
+-------+-------+--------+
| name  | point | gender |
+-------+-------+--------+
| Nicky |   400 | w      |
| Tim   |   300 | m      |
+-------+-------+--------+
2 rows in set (0.01 sec)

Ta-da! I know it’s quite complicated. So next time I’ll bring some more practice questions that using subquery. After that, I’m sure you would get familiar with it.

Thank you for reading my post guys! see ya!