mysql 7种jion全实现 干货

mysql 7种jion全实现 干货

注意：mysql 没有full outer join

create database jiontest;
use jiontest;

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

INSERT INTO t1(id, person)
VALUES(1,'小明'),
      (2,'小红'),
      (3,'小强');
 
INSERT INTO t2(id, person)
VALUES('A','小明'),
      ('B','小红'),
      ('C','小刚');
	  

#笛卡尔	  
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 |
 
 #交集
 SELECT 
    t1.id, t2.id
FROM
    t1
        INNER JOIN
    t2 ON t1.person = t2.person;
	
id|id|
--|--|
 1|A |
 2|B |
 
 #左连接 
 SELECT 
    t1.id, t2.id
FROM
    t1
        LEFT JOIN
    t2 ON t1.person = t2.person;
	
id|id|
--|--|
 1|A |
 2|B |
 3|  |
 #右连接 
 SELECT 
    t1.id, t2.id
FROM
    t1
        RIGHT JOIN
    t2 on t1.person = t2.person;
	
id|id|
--|--|
 1|A |
 2|B |
  |C |
  
 #左独有  
  SELECT 
    t1.id, t2.id
FROM
    t1
        LEFT JOIN
    t2 on t1.person = t2.person
	WHERE t2.person is null;
id|id|
--|--|
 3|  |
 
 #右独有   
   SELECT 
    t1.id, t2.id
FROM
    t1
        RIGHT JOIN
    t2 on t1.person = t2.person
	WHERE t1.person is null;
id|id|
--|--|
  |C |
  
   #交集
   SELECT 
    t1.id, t2.id
FROM
    t1
        INNER JOIN
    t2 on t1.person = t2.person;
	
id|id|
--|--|
 1|A |
 2|B |
 
  #全集
 SELECT t1.id, t2.id from t1 right join t2 ON t1.person = t2.person
union
SELECT t1.id, t2.id from t1 left join t2 ON t1.person = t2.person;
id|id|
--|--|
 1|A |
 2|B |
  |C |
 3|  |
 
 #左右独有
SELECT t1.id, t2.id from t1 right join t2 ON t1.person = t2.person where t1.id is null
union
SELECT t1.id, t2.id from t1 left join t2 ON t1.person = t2.person where t2.id is null;
id|id|
--|--|
  |C |
 3|  |