20180925-MySQL-Joins

• 四种join

1. Cross join
2. Inner join
3. Left join
4. Right join

• 前期准备工作

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    pattern VARCHAR(50) NOT NULL
);

CREATE TABLE t2(
    id VARCHAR(50) PRIMARY KEY,
    pattern VARCHAR(50) NOT NULL
);

INSERT INTO t1(id, pattern) 
VALUES(1, 'Divot'),(2, 'Brick'),(3, 'Grid');

INSERT INTO t2(id, pattern)
VALUES('A', 'Brick'),('B', 'Grid'),('C', 'Diamond');

• MySQL CROSS JOIN多个表的笛卡尔积

SELECT t1.id, t2.id
FROM t1
CROSS JOIN t2;

结果如下

id	id
1	A
2	A
3	A
1	B
2	B
3	B
1	C
2	C
3	C

• MySQL INNER JOIN前提是有匹配的列值，非空值
  join-predicate：t1.pattern = t2.pattern

SELECT t1.id, t2.id
FROM t1 INNER JOIN t2
ON t1.pattern = t2.pattern;

结果如下

id	id
2	A
3	B

• MySQL LEFT JOIN列出所有的左边的列以及右边符合要求的列，右边的值可以为空

SELECT t1.id, t2.id
FROM t1 LEFT JOIN t2 ON t1.pattern = t2.pattern;

结果如下

id	id
2	A
3	B
1	NULL

• MySQL RIGHT JOIN，类似情况，右边的列不能为空，需要全部列出，左边的列可以为NULL

SELECT t1.id, t2.id
FROM t1 RIGHT JOIN t2 ON t1.pattern = t2.pattern;

结果如下：

id	id
2	A
3	B
NULL	C