数据库学习之四--Join, Left Join, Right Join, Full Join对比

一、Join, Left Join, Right Join, Full Join区别：

　　

 二、查询对比

　　1. 创建表Persons和Orders,并插入数据:

CREATE TABLE `persons` (
  `ID_P` int(11) NOT NULL AUTO_INCREMENT,
  `FistName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `LastName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,
  `City` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_P`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of persons
-- ----------------------------
INSERT INTO `persons` VALUES ('1', 'Adams', 'John', '21', 'London');
INSERT INTO `persons` VALUES ('2', 'Bush', 'George', '23', 'New York');
INSERT INTO `persons` VALUES ('3', 'Carter', 'Thomas', '26', 'Beijing');

 

CREATE TABLE `orders` (
  `ID_O` int(11) NOT NULL AUTO_INCREMENT,
  `ID_P` int(11) NOT NULL,
  `OrderNo` int(11) NOT NULL,
  `Price` float DEFAULT NULL,
  PRIMARY KEY (`ID_O`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '3', '11000', '11.2');
INSERT INTO `orders` VALUES ('2', '3', '11001', '10');
INSERT INTO `orders` VALUES ('3', '1', '11022', '10.5');
INSERT INTO `orders` VALUES ('4', '1', '11023', '15');
INSERT INTO `orders` VALUES ('5', '1', '11064', '16.6');

数据表显示如下：

persons:

　

 

Orders:

　

 

　　2. Join、Inner Join查询语句：

SELECT Persons.LastName, Persons.FistName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

查询结果：

　　

　

 　　3. Left Join查询：　　

SELECT Persons.LastName, Persons.FistName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

查询结果：

 

 　　4. Right Join查询：　

SELECT Persons.LastName, Persons.FistName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

查询结果：

 

 　　5. Full Join查询：

SELECT FistName,LastName, Age,OrderNo
FROM Persons
FULL JOIN Orders

查询结果：