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');