sql join 语句的小总结

CREATE TABLE Persons (
  id INT PRIMARY KEY,
  LastName CHAR(10) NOT NULL,
  FirstName VARCHAR (10),
  address VARCHAR (10),
  city VARCHAR (10) 
  
  )ENGINE INNODB;
  
 INSERT INTO Persons(id,LastName,FirstName, address, city)
   VALUES(1,'Adams','Joh','Oxford Street','London');
 INSERT INTO Persons(id,LastName,FirstName, address, city)
   VALUES(2,'Bush','George','Fifth Avenue','New York');
 INSERT INTO Persons(id,LastName,FirstName, address, city)
   VALUES(3,'Adams','Thomas','Changan Street','Beijing');

CREATE TABLE Orders(
    Id_O INT PRIMARY KEY,
    Order_no CHAR(20) NOT NULL,
    Id_P INT NOT NULL

);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(1,'77895',3);
INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(2,'44678',3);
INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(3,'22456',1);
INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(4,'24562',1);
INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(5,'34764',65);

 

1， inner join,  连接谓词共有的部分。
SELECT * FROM Persons p INNER JOIN Orders o 
ON p.`id` = o.`Id_P`; 

2， left join,  左表的特有部分加上两表的共有部分
SELECT * FROM Persons p LEFT JOIN Orders o
ON p.`id` = o.`Id_P`;

3， 左表特有的部分
SELECT * FROM Persons p LEFT JOIN Orders o
ON p.`id` = o.`Id_P` WHERE o.`Id_O` IS  NULL;

4， right join , 右表特有部分加上两表的共有部分。
SELECT * FROM Persons p RIGHT JOIN Orders o
ON p.`id` = o.`Id_p`;

5，右表特有部分。
SELECT * FROM Persons p RIGHT JOIN Orders o
ON p.`id` = o.`Id_p` WHERE p.`id` IS NULL;

6， full join 两表的所有结果集
SELECT * FROM Persons p LEFT JOIN Orders o
ON p.`id` = o.`Id_p`
UNION 
SELECT * FROM Persons p RIGHT JOIN Orders o
ON p.`id` = o.`Id_P`;

7，左表特定部分的结果集 加上 右表特定的结果集 。
SELECT * FROM Persons p LEFT JOIN Orders o
ON p.`id` = o.`Id_p` WHERE o.`Id_O` IS NULL
UNION 
SELECT * FROM Persons p RIGHT JOIN Orders o
ON p.`id` = o.`Id_P` WHERE p.`id` IS NULL