Its a simple question of Left Join. My solution attached


  • 38
    L
    SELECT Person.FirstName, Person.LastName, Address.City, Address.State from Person LEFT JOIN Address on Person.PersonId = Address.PersonId;

  • 17
    K
    SELECT `FirstName`, `LastName`, `City`, `State` FROM `Person`
        LEFT JOIN `Address` USING(`PersonId`)
    

  • 23
    P

    since those selected columns are unique among two tables, the table prefix can be omitted from your solution:

    select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId;

  • 0
    L
    This post is deleted!

  • 1
    L

    A "left join" solution with alias for the tables.

    SELECT FirstName, LastName, City, State FROM Person P
    LEFT JOIN Address A ON P.PersonId = A.PersonId;


  • 1
    S

    Could you please explain a bit why use LEFT JOIN?
    I didn't use SQL for quite a while, what I used originally was INNER JOIN, and everything else was exactly the same as yours, but mine didn't get accepted.
    Thanks.


  • 0
    S

    OK, I figured it out myself. Sorry for the silly question.


  • 0

    Right Join

    SELECT FirstName, LastName, City, State FROM Address a
    RIGHT JOIN Person p ON a.PersonId = p.PersonId
    

    Left Join

    SELECT FirstName, LastName, City, State FROM Person p
    LEFT JOIN Address a ON a.PersonId = p.PersonId

  • 2
    L

    @steve.j.sun Why we cannot use INNER JOIN?


  • 2
    S

    @luckyever619 INNER JOIN only returns records that are in BOTH tables. That means for a person who does not have an address record in the Address Table will not be displayed in your solution using INNER JOIN. Remember in the question it mentioned "regardless if there is an address for each of those people".


  • 0
    Z

    Hi,

    I got a question when I was dealing with this. Would you mind......

    Because I chose OUT JOIN( FULL JOIN) first rather than LEFT JOIN, while there is no output, this situation made me confused. Since, even if I use OUT JOIN, it can not be with no output. Is the problem of Leetcode System Setting or MySQL? Cause I am much more familiar with HQL.


  • 0
    L

    @zhipinggao i have the same problem with you...


  • 0
    W

    @LoveLearning Me too.... I was thinking to use FULL OUTER JOIN


  • 0
    D

    @luckyever619
    This is the key point:
    " regardless if there is an address for each of those people: "
    if you useINNER JOIN,the result will exclude those people who don't have an address in the data.
    It's like intersection in mathematics---"A∩B"


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.