sql的各种Join的定义与区别:
INNER JOIN:如果表中有至少一个匹配,则返回行
LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
FULL JOIN:只要其中一个表中存在匹配,则返回行
各种Join的图解
实例演示 tbla表
DROP TABLE IF EXISTS `tbla`;
CREATE TABLE tbla(
aid INT NOT NULL AUTO_INCREMENT,
aname VARCHAR(30),
money FLOAT,
PRIMARY KEY (`aid`)
)
INSERT INTO tbla VALUES(1,"ali",13321.13),(2,"tens",12888.13),(3,"Google",13888.13),(4,"baidu",8899.13)
tblB表
DROP TABLE IF EXISTS `tblB`;
CREATE TABLE tblB(
bid INT NOT NULL AUTO_INCREMENT,
bname VARCHAR(30),
money FLOAT,
PRIMARY KEY (`bid`)
)
INSERT INTO tblB VALUES(1,"Google",13321.13),(2,"MIT",12888.13),(3,"microsoft",13888.13),(4,"baidu",8899.13)
接下来就是各种Join实例测试
1、inner join的实例
SELECT * FROM tbla INNER JOIN tblb ON tbla.aname=tblb.bname
会得到的结果如下图
2、LEFT JOIN的实例
SELECT * FROM tbla LEFT JOIN tblb ON tbla.aname=tblb.bname
会得到的结果如下图
3、RIGHT JOIN的实例
SELECT * FROM tbla RIGHT JOIN tblb ON tbla.aname=tblb.bname
会得到的结果如下图