sql join 语句的小总结

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(,'',);

sql join 语句的小总结 sql join 语句的小总结

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
上一篇:图解MYSQL JOIN ON,SQL JOIN 详解,数据库sql join语句


下一篇:画图解释 SQL join 语句