JOIN 按功能大致分三类:
- INNER JOIN(内连接,或等值连接):获取两表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 获取右表所有记录,即使左表没有对应匹配的记录。
#创建人物表
CREATE TABLE tb_name (
id INT(11),
iname VARCHAR(10),
age INT(3),
PRIMARY KEY(id)
);
INSERT INTO tb_name VALUES
(1001, '张三', 17),
(1002, '小美', 16),
(1003, '啊Q', 21),
(1004, '张三', 22),
(1005, '小美', 17),
(1006, '张三', 16);
#创建家庭表
CREATE TABLE tb_family (
id INT(11),
iname VARCHAR(10),
family INT(3),
PRIMARY KEY(id)
);
INSERT INTO tb_family VALUES
(1001, '张三', 5),
(1002, '勤奋', 4),
(1003, '啊Q', 3),
(1005, '海阔', 8);
#内连(获取两表都有的记录)
SELECT a.id, a.iname, a.age, b.family FROM tb_name a
INNER JOIN tb_family b ON a.id = b.id AND a.iname = b.iname;
#上例可以等价于
SELECT a.id, a.iname, a.age, b.family FROM tb_name a, tb_family b
WHERE a.id = b.id AND a.iname = b.iname;
#左联(获取左表所有记录,右表没有对应的数据就显示Null)
SELECT a.id, a.iname, a.age, b.family FROM tb_name a
LEFT JOIN tb_family b ON a.id = b.id AND a.iname = b.iname;
#右连(获取右表所有记录,左表没有对应的数据就显示Null)
SELECT a.id, a.iname, a.age, b.family FROM tb_name a
RIGHT JOIN tb_family b ON a.id = b.id AND a.iname = b.iname;
内连结果:
id iname age family
1001 张三 17 5
1003 啊Q 21 3
左连结果:
id iname age family
1001 张三 17 5
1002 小美 16
1003 啊Q 21 3
1004 张三 22
1005 小美 17
1006 张三 16
右连结果:
id iname age family
1001 张三 17 5
4
1003 啊Q 21 3
8
总结:
1,对于inner join,满足on后的条件,表的数据才能查出,可以起到过滤作用。当然也可以把条件放到where后面。
2, 对于left join,不管on后跟什么条件,左表的数据全部查出来,因此要想过滤需把条件放到where后面。
3,对于right join,不管on后跟什么条件,右表的数据全部查出来,因此要想过滤需把条件放到where后面。
right join举例(把上述的 AND 改成 WHERE):
SELECT a.id, a.iname, a.age, b.family FROM tb_name a
RIGHT JOIN tb_family b ON a.id = b.id WHERE a.iname = b.iname;
结果:
id iname age family
1001 张三 17 5
1003 啊Q 21 3