在sql中有4中方式
INNER JOIN(JOIN)
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN(mysql中不支持)
首先建表
这些东西还是自己搞一遍比较好
下面是建表语句
create table names(
id int primary key auto_increment,
name varchar(20) not null
);
create table addresses(
id int primary key auto_increment,
address varchar(20) not null
);
insert into names values(1,'Google');
insert into names values(2,'Taobao');
insert into names values(3,'Weibo');
insert into names values(4,'Facebook');
insert into addresses values(1,'USA');
insert into addresses values(5,'CHINA');
insert into addresses values(3,'CHINA');
insert into addressesnames values(6,'USA');
INNER JOIN
内连接是最常见的一种连接,只连接匹配的行
inner join与join是相同的
SELECT * FROM names INNER JOIN addresses ON names.id = addresses.id;
SELECT * FROM names JOIN addresses ON names.id = addresses.id;
LEFT JOIN
是返回左表的全部行和右表满足ON关系,如果左表的行在右表中没有匹配,那么这一行右表中对应数据就用NULL代替
SELECT * FROM names LEFT JOIN addresses ON names.id = addresses.id;
RIGHT JOIN
返回右表的全部行和左表满足ON条件的行,如果右表的行在左表中没有匹配,那么这一行左表中对应数据用NULL代替。
SELECT * FROM names RIGHT JOIN addresses ON names.id = addresses.id;
当然还有全连接
FULL OUTER JOIN
会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行,那么对面的数据用NULL代替
当然了,mysql中并不支持full outer join
只能用union来替代
SELECT * FROM names LEFT JOIN addresses ON names.id = addresses.id
UNION
SELECT * FROM names RIGHT JOIN addresses ON names.id = addresses.id
因为上课不认真听讲,总是感觉学的不够全面,所以专门抽空补课了mysql的书,最近在看mysql 必知必会
以上内容学习来自以下博客
https://www.cnblogs.com/tigerhsu/p/5113938.html
https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html
亲力而为(力好像错了)