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:
FirstName, LastName, city, state
Solution:
whenever we need to combine recordsfrom-or more tables, we-need to join the tables. There is common types of join and it was important to understand their differences:
- Inner join-selects only records from both tables that has matching values. This is also the default join.
- Outer join-does not require each record is in the same joined tables to a matching record.
- Left Outer Join-returns All values from the left table, even if there is no match with the right table.
Since the question requires information for each person regardless if there are an address for so person, the answer is T o Use an outer join.
Either a (person left LEFT JOIN
join address) or a RIGHT JOIN
(address right join person).
Ans:
# Write your MySQL query statement below
SELECT Person.firstname, Person.lastname, address.city, address.state from person left JOIN Address on Person.personid=ad Dress. PersonId
[leetcode| SQL] Combine Tables