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 are the primary key column for this table.
Write a SQL query for a report this provides the following information for each person in the person table, regardless if There is a address for each of those people:
Main topic:
There are two data tables: Person table and Address table. The person table primary key is the personid,address (address) Table primary key is Addressid, which is associated with the People table by PersonID.
Write an SQL query, for everyone in the person table, remove the FirstName, LastName, City, and state properties, regardless of whether their address information exists.
Problem Solving Ideas:
The subject is a simple even table query, with the person table as the main table, the Address table as a secondary table. Therefore, you can use the left associative query to query the tables.
About left (outer) association, Right (outer) association, Inner Association, and Outer Association query. See this blog: http://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html
In a word, the left side of the association is the left table, as long as the left table has data on the output, regardless of the right table has no data. The right association is reversed, and the table on the right is whichever. The internal correlation is that both tables have data to be queried. The external association is to be queried and displayed as long as any one of the tables has data.
So the subject uses left association.
SQL statements:
SELECT P.firstname, P.lastname, a.city, a.state
From the person p left joins Address a on A.personid=p.personid;
"Leetcode Brush Problem" Sql-combine the Tables