MySQL 8.0.22 | Select Outer Join (Left, Right)

[Outer Join]

(1) Left Outer Join

(2) Right Outer Join

[Exercise Questions]

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 each member’s id, name, car number, and also show the members who don’t have car.

Answer :mysql> select m.id, m.name, c.carNum
-> from member m
-> left outer join car c
-> on m.id=c.id;
+----+------+--------+
| id | name | carNum |
+----+------+--------+
| 1 | Nina | 1111 |
| 1 | Nina | 4444 |
| 2 | Tom | 2222 |
| 3 | Amy | 3333 |
| 3 | Amy | 5555 |
| 4 | Jake | NULL |
| 5 | Lisa | NULL |
+----+------+--------+
7 rows in set (0.00 sec)

Q2. Select id, name and the carNum column as well of member who doesn’t have car only.

mysql> select m.id, m.name, c.carNum
-> from member m
-> left outer join car c
-> on m.id=c.id
-> where c.id is null;
+----+------+--------+
| id | name | carNum |
+----+------+--------+
| 4 | Jake | NULL |
| 5 | Lisa | NULL |
+----+------+--------+
2 rows in set (0.00 sec)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store