mysql多种join_MySQL的几种Join

/* 左表t1*/

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (id INT NOT NULL,NAME VARCHAR(20));

INSERT INTO t1 VALUES (1,‘t1a‘);

INSERT INTO t1 VALUES (2,‘t1b‘);

INSERT INTO t1 VALUES (3,‘t1c‘);

INSERT INTO t1 VALUES (4,‘t1d‘);

INSERT INTO t1 VALUES (5,‘t1f‘);

/* 右表 t2*/

DROP TABLE IF EXISTS t1;

CREATE TABLE t2 (id INT NOT NULL,NAME VARCHAR(20));

INSERT INTO t2 VALUES (2,‘t2b‘);

INSERT INTO t2 VALUES (3,‘t2c‘);

INSERT INTO t2 VALUES (4,‘t2d‘);

INSERT INTO t2 VALUES (5,‘t2f‘);

INSERT INTO t2 VALUES (6,‘t2a‘);

#两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。

SELECT * FROM t1 JOIN t2

#两表关联，左表全部保留，右表关联不上用null表示。

SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.id

#右表全部保留，左表关联不上的用null表示。

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id =t2.`id`

#两表关联，保留两表中交集的记录。

SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.`id`

#两表关联，查询左表独有的数据。

SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.`id` WHERE t2.id IS NULL

#两表关联，查询右表独有的数据。

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id =t2.id WHERE t1.id IS NULL

#两表关联，查询它们的所有记录

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id =t2.`id`

UNION

SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.id

#两表关联，取并集然后去交集。SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.`id` WHERE t2.id IS NULLUNIONSELECT * FROM t1 RIGHT JOIN t2 ON t1.id =t2.id WHERE t1.id IS NULL