mysql left join right join inner join例子

来源：互联网发布：公司客户数据保密制度编辑：程序博客网时间：2024/04/30 14:43

建表语句如下：

#--------------create table_a-----------create table table_a(  id int(11) not null auto_increment,  name varchar(20) not null,  primary key(id))engine=InnoDB default charset=utf8;#----------------create table_b----------create table table_b(  id int(11) not null,  name varchar(20) not null,  primary key(id))engine=InnoDB default charset=utf8;

插入几条数据：

#-------------insert data into table a------------insert into table_a values(1,'a20121313'),(2,'a2131314'),(3,'a43239284392'),(4,'a33727427'),(5,'a32382592'),(6,'a42424224'),(7,'a14312141');#-------insert data into table_b----------------insert into table_b values(1,'b2141414'),(2,'b15315151'),(3,'b562842842'),(4,'b32185115');

1.left join

sql如下：

select * from table_a a left join table_b b on a.id = b.id;

结果如下：

+----+--------------+------+------------+| id | name         | id   | name       |+----+--------------+------+------------+|  1 | a20121313    |    1 | b2141414   ||  2 | a2131314     |    2 | b15315151  ||  3 | a43239284392 |    3 | b562842842 ||  4 | a33727427    |    4 | b32185115  ||  5 | a32382592    | NULL | NULL       ||  6 | a382331      | NULL | NULL       ||  7 | a64376371631 | NULL | NULL       |+----+--------------+------+------------+7 rows in set (0.00 sec)

通过观察结果可以得出以下结论，left join以左表为基础表，以右表为从表，左表不存在的数据设置为NULL。

2.right join

sql如下：

 select * from table_a a right join table_b b on a.id = b.id;

结果如下:

+------+--------------+----+------------+| id   | name         | id | name       |+------+--------------+----+------------+|    1 | a20121313    |  1 | b2141414   ||    2 | a2131314     |  2 | b15315151  ||    3 | a43239284392 |  3 | b562842842 ||    4 | a33727427    |  4 | b32185115  |+------+--------------+----+------------+4 rows in set (0.00 sec)

right join以右表为基础表，左表为从表，左表不存在的项设置为NULL

3.inner join

inner join无基础表，只是显示符合条件的结果。