[리트코드/SQL] 15. Combine Two Tables (175)

👉https://42-snoopy.tistory.com/entry/%EB%A6%AC%ED%8A%B8%EC%BD%94%EB%93%9CSQL-14-Second-Highest-Salary-176

Second Highest Salary - LeetCode

 

---

🤔 문제 분석

1) Write an SQL query to report the first name, last name, city, and state of each person in the Person table.

2) If the address of a personId is not present in the Address table, report null instead.

3) Return the result table in any order.

 

💡 풀이

SELECT P.firstName, P.lastName, A.city, A.state
FROM Person AS P
    LEFT JOIN Address AS A 
    ON P.personID = A.personID

+ 처음에는 null이라는 글자를 작성해야하는 줄 알고 아래와 같이 코드를 짰었다.

SELECT P.firstName, P.lastName
        CASE
            WHEN A.addressID IS NULL THEN A.state
            ELSE A.city
        END AS city,
        CASE
            WHEN A.state IS NULL THEN A.state
            ELSE A.state
        END AS stae

FROM Person AS P
    LEFT JOIN Address AS A 
    ON P.personID = A.personID