日期:2014-05-16  浏览次数:20798 次

MySQL left join right join inner join 区别

请看下面的语句

注意比较06 和07的显示结果,自己测试的 语法没有错误

-- left join , right join ,inner join 实验


CREATE DATABASE if NOT EXISTS join_test;
use join_test;
DROP TABLE If EXISTS tbl_a;
create table tbl_a (
id int(11) auto_increment primary key,
name varchar(255),
email varchar(255)
);

DROP TABLE If EXISTS `tbl_b`;
create table `tbl_b`(
`id` int(11) auto_increment primary key,
`name` varchar(255),
`email` varchar(255)
);
use join_test;
-- inset into tbl_a
insert into tbl_a values(1,'a_name_001','a_email_001');
insert into tbl_a values(2,'a_name_002','a_email_002');
insert into tbl_a values(3,'a_name_003','a_email_003');
insert into tbl_a values(4,'a_name_004','a_email_004');
insert into tbl_a values(5,'a_name_005','a_email_005');
insert into tbl_a values(7,'a_name_007','a_email_007');

-- insert into tbl_b
insert into tbl_b values(1,'b_name_001','b_email_001');
insert into tbl_b values(2,'b_name_002','b_email_002');
insert into tbl_b values(3,'b_name_003','b_email_003');
insert into tbl_b values(4,'b_name_004','b_email_004');
insert into tbl_b values(5,'b_name_005','b_email_005');
insert into tbl_b values(6,'b_name_006','b_email_006');

-- left join
select * from tbl_a left join tbl_b on tbl_a.id=tbl_b.id;

查询结果:

id name email id name email 1 1 2 2 3 3 4 4 5 5 7
a_name_001 a_email_001b_name_001 b_email_001
a_name_002 a_email_002b_name_002 b_email_002
a_name_003 a_email_003b_name_003 b_email_003
a_name_004 a_email_004b_name_004 b_email_004
a_name_005 a_email_005b_name_005 b_email_005
a_name_007 a_email_007




-- right join
select * from tbl_a right join tbl_b on tbl_a.id=tbl_b.id;
查询结果:

id name email id name email 1 1 2 2 3 3 4 4 5 5 6
a_name_001 a_email_001b_name_001 b_email_001
a_name_002 a_email_002b_name_002 b_email_002
a_name_003 a_email_003b_name_003 b_email_003
a_name_004 a_email_004b_name_004 b_email_004
a_name_005 a_email_005b_name_005 b_email_005
b_name_006 b_email_006




-- inner join
select * from tbl_a inner join tbl_b on tbl_a.id=tbl_b.id
查询结果:

id
友情链接: 爱易网 云虚拟主机技术 云服务器技术 程序设计技术 开发网站 APP开发教程
Copyright © 2013-2024 爱易网页 当前在线:286人  网站在32秒内访问总人数:286人 当前 30.18%  粤ICP备18100884号-2