MySQL Join语法

JOIN、CROSS JOIN、INNER JOIN

在 MySQL 中等价的，都是构成笛卡尔积。

select * from a,b

id	name	id	aid	name
1	b1	    1	1	b1
2	a1	    1	1	b1
1	b1	    2	1	b2
2	a1	    2	1	b2
1	b1	    3	2	b3
2	a1	    3	2	b3
1	b1	    4	2	b4
2	a1	    4	2	b4

a [LEFT] JOIN b USING (c1, c2)

要求a表和b表中都必须存在USING中定义的c1, c2两列，且两列在两个表中的值相同，否则报错。

select * from a left join b using (id)

id	name	aid	name
1	b1	    1	b1
2	a1	    1	b2


上面的语句等价于
select coalesce(a.id, b.id) as id, a.name, b.aid, b.name from a left join b on a.id = b.id 

NATURAL [LEFT] JOIN

等价于使用限制所有同名列都在USING子句中的 [LEFT] JOIN

重复的列不会重复显示

select * from a natural join b 

id	name	aid
1	b1	    1

上面的语句等价于
select coalesce(a.id, b.id) as id, coalesce(a.name, b.name) as name, b.aid from a join b on a.id = b.id and a.name = b.name

STRAIGHT_JOIN

类似于 JOIN， 只是左表在右表之前读。

select * from a straight_join b;

<=>

select * from a join b;

JOIN 比 , 优先级更高

t1, t2 join t3 
等价于
t1, (t2 join t3)

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
该语句会报Unknown column 't1.i1' in 'on clause'的错
原因就是JOIN的优先级高于 , 的，语句等价于：
SELECT * FROM t1, (t2 JOIN t3 ON (t1.i1 = t3.i3));

案例数据表

CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `a` (`id`, `name`)
VALUES
	(1, 'b1'),
	(2, 'a1');


CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `aid` int(11) DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

INSERT INTO `b` (`id`, `aid`, `name`)
VALUES
	(1, 1, 'b1'),
	(2, 1, 'b2'),
	(3, 2, 'b3'),
	(4, 2, 'b4');