- 四種join
- 前期準(zhǔn)備工作
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多個(gè)表的笛卡爾積
SELECT t1.id, t2.id
FROM t1
CROSS JOIN t2;
結(jié)果如下
| 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;
結(jié)果如下
| id | id |
|---|---|
| 2 | A |
| 3 | B |
-
MySQL LEFT JOIN列出所有的左邊的列以及右邊符合要求的列，右邊的值可以為空
SELECT t1.id, t2.id
FROM t1 LEFT JOIN t2 ON t1.pattern = t2.pattern;
結(jié)果如下
| 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;
結(jié)果如下：
| id | id |
|---|---|
| 2 | A |
| 3 | B |
| NULL | C |
