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

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‘);

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

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

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

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