CREATE TABLE Persons (

  id INT PRIMARY KEY,

  LastName CHAR() NOT NULL,

  FirstName VARCHAR (),

  address VARCHAR (),

  city VARCHAR () 

  )ENGINE INNODB;

 INSERT INTO Persons(id,LastName,FirstName, address, city)

   VALUES(,'Adams','Joh','Oxford Street','London');

 INSERT INTO Persons(id,LastName,FirstName, address, city)

   VALUES(,'Bush','George','Fifth Avenue','New York');

 INSERT INTO Persons(id,LastName,FirstName, address, city)

   VALUES(,'Adams','Thomas','Changan Street','Beijing');

CREATE TABLE Orders(

    Id_O INT PRIMARY KEY,

    Order_no CHAR() NOT NULL,

    Id_P INT NOT NULL

);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(,'',);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(,'',);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(,'',);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(,'',);

INSERT INTO Orders(Id_O,Order_no,Id_P) VALUES(,'',);

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