mysql left, right, inner join

Left and right connections

Full multiplication method (very inefficient)

mysql> select * from test10;
+------+-------+
| id | sname |
+------+-------+
| 1 | 云彩 | 
| 2 | 月亮 | 
| 3 | 星星 | 
+------+-------+

3 rows in set (0.00 sec)

mysql> select * from test11;
+--------+-------+
| cat_id | cname |
+--------+-------+
| 95 | 猴子 | 
| 96 | 老虎 | 
+--------+-------+

2 rows in set (0.00 sec)

The effect of implementing two tables* in the database

mysql> select * from test10,test11;
+------+-------+--------+-------+
| id | sname | cat_id | cname |
+------+-------+--------+-------+
| 1 | 云彩 | 95 | 猴子 | 
| 1 | 云彩 | 96 | 老虎 | 
| 2 | 月亮 | 95 | 猴子 | 
| 2 | 月亮 | 96 | 老虎 | 
| 3 | 星星 | 95 | 猴子 | 
| 3 | 星星 | 96 | 老虎 | 
+------+-------+--------+-------+

6 rows in set (0.03 sec)

Analysis:
test10 Treated as a set with three elements
test11 Treated as a set with two elements

test10*test11The new set has six elements
Multiply the number of rows in two tables
Two columns Add the number of columns in each table (can be repeated)

When querying multiple tables with duplicate column names, you need to clearly indicate which table is obtained

mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category limit 3;
+----------+--------+--------------------+--------+----------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+--------------------+--------+----------+
| 1 | 4 | KD876 | 1 | 手机类型 | 
| 4 | 8 | htcN85原装充电器 | 1 | 手机类型 | 
| 3 | 8 | 诺基亚原装5800耳机 | 1 | 手机类型 | 
+----------+--------+--------------------+--------+----------+

Technique: Create a same structure Table create table [new table name] like [old table name]
create table minigoods like goods;

Copy part of the table contents

mysql> insert into minigoods
-> select * from goods limit 3;

Get meaningful two The corresponding minigoods.cat_id=category.cat_id

mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category where minigoods.cat_id=category.cat_id ;
+----------+--------+--------------------+--------+----------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+--------------------+--------+----------+
| 1 | 4 | KD876 | 4 | 3G手机 | 
| 4 | 8 | htcN85原装充电器 | 8 | 耳机 | 
| 3 | 8 | 诺基亚原装5800耳机 | 8 | 耳机 | 
+----------+--------+--------------------+--------+----------+

3 rows in set (0.00 sec)

Left join syntax
a table is on the left, not moving
B table is on the right, move
A table and B table pass a relationship (set by yourself) to filter the rows of b required by a

a left join b on [condition] --- -If the condition is true, take out the row of b

a left join b on [Condition] The result set can also be regarded as a table (assumed to be table c), and you can query it again

mysql> select goods_id,goods_name,cat_name 
-> from
-> (minigoods left join category on minigoods.cat_id=category.cat_id); 
+----------+--------------------+----------+
| goods_id | goods_name | cat_name |
+----------+--------------------+----------+
| 1 | KD876 | 3G手机 | 
| 4 | htcN85原装充电器 | 耳机 | 
| 3 | 诺基亚原装5800耳机 | 耳机 | 
+----------+--------------------+----------+

[minigoods left join category on minigoods.cat_id=category.cat_id regarded as c table]
Verification: You can still use where and other filtering conditions later

mysql> select goods_id,goods_name,cat_name from 
(minigoods left join category on minigoods.cat_id=category.cat_id )
where 1 order by goods_id desc limit 2; 
+----------+--------------------+----------+
| goods_id | goods_name | cat_name |
+----------+--------------------+----------+
| 4 | htcN85原装充电器 | 耳机 | 
| 3 | 诺基亚原装5800耳机 | 耳机 | 
+----------+--------------------+----------+

2 rows in set (0.00 sec)

You can join multiple tables left, that is, treat the result as a table
Treat it as one table

{{a left join b on [条件]} left join c on [条件]} 
mysql> select goods.goods_id,goods.goods_name,goods.cat_id,cat_name
-> from
-> minigoods left join category on minigoods.cat_id=category.cat_id
-> left join goods on minigoods.cat_id=4 limit 4;
+----------+--------------------+--------+----------+
| goods_id | goods_name | cat_id | cat_name |
+----------+--------------------+--------+----------+
| 1 | KD876 | 4 | 3G手机 | 
| 4 | htcN85原装充电器 | 8 | 3G手机 | 
| 3 | 诺基亚原装5800耳机 | 8 | 3G手机 | 
| 5 | 索爱原装M2卡读卡器 | 11 | 3G手机 | 
+----------+--------------------+--------+----------+

The difference between left and right joins ================================================== =================================================

a left join b on means query based on a when querying
a right join b on means query based on b when querying

a left join b on is equivalent For b right join a (all queries are based on a)

Tips: In terms of porting compatibility and understanding, it is best to always use left joins

create table boy(
bname varchar(20),
other char(1)
)engine myisam charset utf8;
insert into boy
values
('屌丝','A'),
('李四','B'),
('王五','C'),
('高富帅','D'),
('郑七','E');

create table girl(
gname varchar(20),
other char(1)
)engine myisam charset utf8;
insert into girl
values
('空姐','B'),
('大S','C'),
('阿娇','D'),
('张柏芝','D'),
('林黛玉','E'),
('宝钗','F');

Get the spouses of all boys (left connection)

select boy.*,girl.* from
boy left join girl on
boy.other=girl.other;
+--------+-------+--------+-------+
| bname | other | gname | other |
+--------+-------+--------+-------+
| 屌丝 | A | NULL | NULL | 
| 李四 | B | 空姐 | B | 
| 王五 | C | 大S | C | 
| 高富帅 | D | 阿娇 | D | 
| 高富帅 | D | 张柏芝 | D | 
| 郑七 | E | 林黛玉 | E | 
+--------+-------+--------+-------+

Get the spouses of all girls (left connection)

mysql> select girl.*,boy.* from
-> girl left join boy on 
-> boy.other=girl.other;
+--------+-------+--------+-------+
| gname | other | bname | other |
+--------+-------+--------+-------+
| 空姐 | B | 李四 | B | 
| 大S | C | 王五 | C | 
| 阿娇 | D | 高富帅 | D | 
| 张柏芝 | D | 高富帅 | D | 
| 林黛玉 | E | 郑七 | E | 
| 宝钗 | F | NULL | NULL | 
+--------+-------+--------+-------+

Get the spouses of all girls (right connection, and above Left join is consistent)

mysql> select girl.* ,boy.* from
-> boy right join girl on
-> boy.other=girl.other;
+--------+-------+--------+-------+
| gname | other | bname | other |
+--------+-------+--------+-------+
| 空姐 | B | 李四 | B | 
| 大S | C | 王五 | C | 
| 阿娇 | D | 高富帅 | D | 
| 张柏芝 | D | 高富帅 | D | 
| 林黛玉 | E | 郑七 | E | 
| 宝钗 | F | NULL | NULL | 
+--------+-------+--------+-------+

Inner join==================================== ================================================== ===============

Take out the spouse

select girl.*,boy.* from
boy inner join girl on 
boy.other=girl.other;
+--------+-------+--------+-------+
| gname | other | bname | other |
+--------+-------+--------+-------+
| 空姐 | B | 李四 | B | 
| 大S | C | 王五 | C | 
| 阿娇 | D | 高富帅 | D | 
| 张柏芝 | D | 高富帅 | D | 
| 林黛玉 | E | 郑七 | E | 
+--------+-------+--------+-------+

The inner join is the intersection of the left and right joins
(outer The connection is the union of left and right connections, which is not supported by mysql) You can use union to implement

Left join application========================== ================================================== =========================

create table match_t(
match_id int primary key auto_increment,
host_team_id int,
guest_team_id int,
match_result varchar(20),
match_time date
)engine myisam charset utf8;
insert into match_t
values
(1,1,2,'2:0','2006-05-21'),
(2,2,3,'1:2','2006-06-21'),
(3,3,1,'2:5','2006-07-21'),
(4,1,1,'3:2','2006-08-21');
create table team_t(
team_id int primary key auto_increment,
team_name varchar(20)
)engine myisam charset utf8;
insert into team_t
values
(1,'恒大'),
(2,'国安'),
(3,'申花'),
(4,'大连');
mysql> select * from match_t;
+----------+--------------+---------------+--------------+------------+
| match_id | host_team_id | guest_team_id | match_result | match_time |
+----------+--------------+---------------+--------------+------------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 | 
| 2 | 2 | 3 | 1:2 | 2006-06-21 | 
| 3 | 3 | 1 | 2:5 | 2006-07-21 | 
| 4 | 4 | 1 | 3:2 | 2006-08-21 | 
+----------+--------------+---------------+--------------+------------+
mysql> select * from team_t;
+---------+-----------+
| team_id | team_name |
+---------+-----------+
| 1 | 恒大 | 
| 2 | 国安 | 
| 3 | 申花 | 
| 4 | 大连 | 
+---------+-----------+

Skills: Kindness and giving show its alias

2006 - After the date of 06-21, change the battle in the table to the team name

select host_t.team_name,guest_t.team_name,match_result,match_time from
match_t left join (team_t as host_t) on match_t.host_team_id=host_t.team_id
left join (team_t as guest_t) on match_t.guest_team_id=guest_t.team_id
where match_time>='2006-06-21';
+--------------+-----------+---------------+-----------+--------------+------------+
| host_team_id | team_name | guest_team_id | team_name | match_result | match_time |
+--------------+-----------+---------------+-----------+--------------+------------+
| 1 | 恒大 | 2 | 国安 | 2:0 | 2006-05-21 | 
| 2 | 国安 | 3 | 申花 | 1:2 | 2006-06-21 | 
| 3 | 申花 | 1 | 恒大 | 2:5 | 2006-07-21 | 
| 4 | 大连 | 1 | 恒大 | 3:2 | 2006-08-21 | 
+--------------+-----------+---------------+-----------+--------------+------------+

The above is the content of mysql left, right, and inner connections. For more related content, please pay attention to the PHP Chinese website (www.php. cn)!