mysql join

mysql 的 join

 

------

基本格式：

 

select ... from A

join B1 on ...

join B2 on ...

...

where ...

 

------

join 类型

 

join 内连接

left join 左连接，左表所有数据查出，右表跟左表有关联的数据才查出

right join 右连接，右表所有数据查出，左表跟右表有关联的数据才查出

inner join 内连接，同 join，

全连接 mysql 不支持全连接，可以通过 union 2个 left join 或 right join 实现全连接，

 

= 如果不用 join 语句，直接用 = 号关联，则相当于 内连接，

 

------

例子：

 

 

* 建表：

	-- create table boy & gril
	create table boy(
		id int(10) primary key auto_increment,
		name varchar(50) not null,
		girl_id int null,
		cdate timestamp default current_timestamp
	)
	engine=innodb default charset=utf8;

	create table girl(
		id int(10) primary key auto_increment,
		name varchar(50) not null,
		boy_id int null,
		cdate timestamp default current_timestamp
	)
	engine=innodb default charset=utf8;

 

 

* 插入测试数据：

	
	-- clear table
	truncate table boy;
	truncate table girl;
	-- init data
	insert into boy(name,girl_id) values
	('boy_01',2),
	('boy_02',5),
	('boy_03',1),
	('boy_04',4),
	('boy_05',3),
	('boy_06',100);

	insert into girl(name,boy_id) values
	('girl_1',3),
	('girl_2',1),
	('girl_3',5),
	('girl_4',4),
	('girl_5',2),
	('girl_6',100);

 

 

* 各种 join 的 sql 语句：

	-- 内连接
	select b.id as boy_id, g.id as girl_id from boy b join girl g on b.girl_id = g.id order by b.id asc;

	-- 用 = 关联，相当于 内连接
	select b.id as boy_id, g.id as girl_id from boy b, girl g where b.girl_id = g.id order by b.id asc;

	-- 左连接
	select b.id as boy_id, g.id as girl_id from boy b left join girl g on b.girl_id = g.id order by b.id asc;

	-- 右连接
	select b.id as boy_id, g.id as girl_id from boy b right join girl g on b.girl_id = g.id order by b.id asc;

	-- 2个 左连接，通过 union 合并，相当于 全连接
	select b.id as boy_id, g.id as girl_id from boy b left join girl g on b.girl_id = g.id
	union
	select b.id as boy_id, g.id as girl_id from girl g left join boy b on g.boy_id = b.id

 

------