数据库学习之四--Join, Left Join, Right Join, Full Join对比
2024-09-15 10:36:16
一、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 ('', 'Adams', 'John', '', 'London');
INSERT INTO `persons` VALUES ('', 'Bush', 'George', '', 'New York');
INSERT INTO `persons` VALUES ('', 'Carter', 'Thomas', '', '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 ('', '', '', '11.2');
INSERT INTO `orders` VALUES ('', '', '', '');
INSERT INTO `orders` VALUES ('', '', '', '10.5');
INSERT INTO `orders` VALUES ('', '', '', '');
INSERT INTO `orders` VALUES ('', '', '', '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
查询结果：
最新文章
- json在项目中的应用大总结
- 2-c语言作业
- 51nod 1051 最大子矩阵和（dp）
- unity3d 扩展NGUI —— 限制UI点击响应间隔
- 安装交叉编译器arm-linux-gcc
- Highlighting Text Item On Entry In Oracle Forms
- 流媒体基础实践之——RTMP直播推流
- Web- 一些标签样式
- git push 报错
- HDU 5033 Building(单调栈)
- NSDictionary 总结 -iOS
- 浅谈MVC
- 数据结构与算法（C#）入门 --- 序
- Write Sling Servlet using a resource type and selector
- Web程序报错：Error instantiating servlet
- cmd & tree & bash
- Android AsyncTask将讲解
- 小鬼难缠--python小bug备忘
- 使用hive分析nginx访问日志方法
- 【NMS与IOU代码】