问题:
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.
Table: Address
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.
Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
FirstName, LastName, City, State
第一次提交(MySQL):
1
2
|
# Write your MySQL query statement below
select FirstName, LastName, City, State from Person, Address where Person.PersonId=Address.PersonId;
|
思想: 使用最常见的select+where语句,也即内连接的形式。将符合Person.PersonId=Address.PersonId条件的,存在于两个表的四个字段FirstName, LastName, City, State的记录,形成连接。 |
提交结果:
报错。
测试用例:
PersonId | FirstName | LastName |
1 | Allen | Wang |
AddressId | PersonId | City | State |
当Address表为空时,Address表中没有匹配的记录,这样会出现连接错误。
通过外连接发方式实现。外联结:分为外左联结和外右联结。对于左表(右表)中没有匹配的记录时,右表(左表)中没有匹配的记录将被设置为null。 提交结果: 通过。 本题知识点: 本题主要考察数据库的内连接与外连接。 |
|