SQL架构
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
先开启本地mysql服务
使用数据库test,新建两个表person和address,并向两数据表中添加数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`PersonId` int(11) NOT NULL,
`FirstName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`LastName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`PersonId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES (100001, '张', '三');
INSERT INTO `person` VALUES (100002, '李', '四');
INSERT INTO `person` VALUES (100003, '王', '五');
INSERT INTO `person` VALUES (100004, '马', '六');
INSERT INTO `person` VALUES (100005, '陈', '七');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for address
-- ----------------------------
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`AddressId` int(11) NOT NULL,
`PersonId` int(11) NULL DEFAULT NULL,
`City` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`State` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`AddressId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of address
-- ----------------------------
INSERT INTO `address` VALUES (200001, 100003, '北京', '1');
INSERT INTO `address` VALUES (200002, 100005, '上海', '0');
INSERT INTO `address` VALUES (200003, 100002, '杭州', '1');
INSERT INTO `address` VALUES (200004, 100004, '深圳', '0');
INSERT INTO `address` VALUES (200005, 100001, '广州', '1');
SET FOREIGN_KEY_CHECKS = 1;
题目要求:
编写一个 SQL 查询,满足条件:
无论 person 是否有地址信息,
都需要基于上述两表提供 person 的以下信息:FirstName, LastName, City, State
SELECT
FirstName,
LastName,
City,
State
FROM
person p
LEFT JOIN address a ON p.PersonId = a.PersonId
SELECT
FirstName,
LastName,
City,
State
FROM
person p
INNER JOIN address a ON p.PersonId = a.PersonId
SELECT
FirstName,
LastName,
City,
State
FROM
person p
RIGHT JOIN address a ON p.PersonId = a.PersonId
-
INNER JOIN:内连接,如果表中有至少一个匹配,则返回行
-
LEFT JOIN:左连接,即使右表中没有匹配,也从左表返回所有的行
-
RIGHT JOIN:右连接,即使左表中没有匹配,也从右表返回所有的行