In this Leetcode Combine Two Tables problem solution 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

Leetcode Combine Two Tables problem solution


Without using left join.

select P.FirstName,P.LastName,A.City,A.State
from Person P,Address A
where P.PersonId=A.PersonId
UNION
select P.FirstName,P.LastName,null,null
from Person P
where P.PersonId NOT IN (Select PersonId from Address
)



With left join.

select p.FirstName, p.LastName, a.City, a.State
from Person p left join Address a on (p.PersonID = a.PersonID)


TSQL solution using Left Join.

SELECT FirstName, LastName, City, State 
FROM Person
LEFT OUTER JOIN Address 
ON Person.PersonID = Address.PersonID


PLSQL solution.

select
p.firstname,
p.lastname,
a.city,
a.state
from person p , address a
where p.personid = a.personid(+)