题目:
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
添加表结构和数据
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 = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `address` VALUES (1, 1, '烟台', '中国');
INSERT INTO `address` VALUES (2, 2, NULL, NULL);
INSERT INTO `address` VALUES (3, NULL, '蓬莱', '中国');
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 = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `person` VALUES (1, 'H', 'P');
INSERT INTO `person` VALUES (2, 'Z', 'Z');
INSERT INTO `person` VALUES (3, NULL, NULL);
思路:
根据题目,可以得出,要显示 Person 表中所有数据,SQL主查 Person 表;
并且 不是每个人都有地址,所以 要 left join Address
解答:
select Person.FirstName, Person.LastName,Address.City, Address.State from Person
left join Address on Person.PersonId = Address.PersonId