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。
Combine Two Tables
1. 建表
create database test;
use test;
create table if not exists Person (
PersonId int(10) not null auto_increment,
FirstName varchar(20) default null,
LastName varchar(20) default null,
primary key(PersonId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Address (
AddressId int(10) not null auto_increment,
PersonId int(10) not null,
City varchar(20) default null,
State varchar(20) default null,
primary key(AddressId),
foreign key(PersonId) references Person(PersonId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Person(PersonId, FirstName, LastName) values (2, "Wang", "Tom");
insert into Address(AddressId, PersonId, City, State) values (1, 2, "New York City", "New York");
//Person表左连接 Address表查询,不能用条件查询,因为不管该人是否有地址
(regardless if there is an address for each of those people)
2. 解答
#正确答案
SELECT FirstName, LastName, City, State from Person LEFT JOIN Address on Person.PersonId = Address.PersonId;
#错误答案
SELECT FirstName, LastName, City, State from Person , Address where Person.PersonId = Address.PersonId;