- 四種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
色鸳,類(lèi)似情況干旁,右邊的列不能為空,需要全部列出,左邊的列可以為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 |