MySQL 8.0.22 | Select Inner Join + Basic Exercise Questions(Table Nickname)

Join — The clause which combine tuples from tow or more tables, based on related columns between them.

As you see, we’re going to learn the Join today. You might think “After all those hard works with foreign key, and now you want me to combine tables back? What the…….” I feel you.

But, we need join to read those data easily! And we can use join even on the tables without foreign key. There are two joins, Inner and Outer. Today I’ll be talking about the Inner Join.

[Inner Join]

Inner Join can combine tuples that have same value of certain columns.
And there are two ways to use it.

(1) Basic Inner Join

The basic query of the inner join is
select table1.column1, table2.column2
from table1
inner join table2
on table1.column=table2.column;
On the first line, there are column1 from table1 and column2 from table2 that you want to select, and it can be one or more. The columns after on are the columns that related to each other. But I prefer the next one because the basic one is bit complicated.

(2) Second Query

So the second way to use inner join is just using where instead of join clause.
select table1.column1, table2.column2
from table1, table2
where table1.column=table2.column;
Shorter and more familiar.
And now I’ll show you some examples of using inner join.

[Exercise Questions]

Let’s say there’s a shop and they saved all the members and each member’s car information. You can copy and paste the same query that I wrote down below.

create table member(
id int primary key,
name varchar(8),
addr varchar(7),
point int
);
create table car(
id int,
carNum varchar(4),
foreign key(id) references member(id)
);
insert into member values
(1,'Nina','seoul',100),
(2,'Tom','seoul',200),
(3,'Amy','busan',400),
(4,'Jake','naju',800),
(5,'Lisa','incheon',500);
insert into car values
(1,'1111'),
(2,'2222'),
(3,'3333'),
(1,'4444'),
(3,'5555');

Q1. Select the id, name and the car number of the member who have a car. (Try to solve it by yourself before looking at the answer.)

Answer : 
mysql> select m.id, m.name, c.carNum
    -> from member m, car c
    -> where m.id=c.id;
+----+------+--------+
| id | name | carNum |
+----+------+--------+
|  1 | Nina | 1111   |
|  1 | Nina | 4444   |
|  2 | Tom  | 2222   |
|  3 | Amy  | 3333   |
|  3 | Amy  | 5555   |
+----+------+--------+
5 rows in set (0.00 sec)

On my answer, you would see the mysterious m and c . They are the nicknames that I set in this query. You see there are
from member m, car c . So I set the nickname ‘m’ for member and ‘c’ for car table. Of course you can type the whole tables name but it’s gonna be so long and annoying. These nicknames only works in this one query.

Q2. Select the id, name, address, car number of the person who’s living in ‘busan’.

Answer :
mysql> select m.id, m.addr, m.name, c.carNum
    -> from member m, car c
    -> where m.id=c.id and m.addr='busan';
+----+-------+------+--------+
| id | addr  | name | carNum |
+----+-------+------+--------+
|  3 | busan | Amy  | 3333   |
|  3 | busan | Amy  | 5555   |
+----+-------+------+--------+
2 rows in set (0.00 sec)

Q3. Select the owner’s name and the car number of the car number ‘3333’.

mysql> select m.name, c.carNum
    -> from member m, car c
    -> where m.id=c.id and c.carNum='3333';
+------+--------+
| name | carNum |
+------+--------+
| Amy  | 3333   |
+------+--------+
1 row in set (0.00 sec)

How was it? Could you do it by yourself? It’s fine if you couldn’t. Cause I am going to bring these kinds of questions a lot from today. So you can practice and practice every day! How fun it is! yay!

Anyway well done for all of you guys. I’ll post ‘Outer Join’ tomorrow, So don’t miss it! Thanks!