LEFT 그리고 RIGHT JOIN

SQL에서 데이터 조인하기

Maham Faisal Khan

Senior Content Developer, Data Science

INNER JOIN 다이어그램

`id` 필드에 `ON`을 사용하는 `INNER JOIN` 다이어그램

A diagram repeated from a previous lesson, showing two tables, left_table and right_table, and the result set after an inner join is performed on the two tables. Records that are not of interest to inner join have been faded out.

초기 LEFT JOIN 다이어그램

LEFT JOIN은 왼쪽 테이블의 모든 레코드와, 제공된 조인 필드에서 일치하는 오른쪽 테이블의 레코드를 모두 반환함
id 필드에 ON을 사용하는 LEFT JOIN 다이어그램

A diagram showing two tables, left table and right table, with arrows pointing to therecords that match on the id column in both tables. Records that are not of interest to left join have been grayed out.

LEFT JOIN 다이어그램

`LEFT JOIN` 필드에 `ON`을 사용하는 `id` 다이어그램

A diagram showing two tables, left table and right table, and the result set after a left join is performed on these tables. Records that are not of interest to left join have been faded out.

LEFT JOIN 구문

SELECT p1.country, prime_minister, president
FROM prime_ministers AS p1
LEFT JOIN presidents AS p2
USING(country);

참고. LEFT JOIN은 LEFT OUTER JOIN으로 쓸 수도 있음.

| country        | prime_minister    | president               |
|----------------|-------------------|-------------------------|
| Egypt          | Mostafa Madbouly  | Abdel Fattah el-Sisi    |
| Portugal       | Luís Montenegro   | Marcelo Rebelo de Sousa |
| Pakistan       | Shehbaz Sharif    | Asif Ali Zardari        |
| United Kingdom | Keir Starmer      | null                    | 
| India          | Narendra Modi     | Droupadi Murmu          |
| Australia      | Anthony Albanese  | null                    | 
...

RIGHT JOIN

`id` 필드에 `ON`을 사용하는 `RIGHT JOIN` 다이어그램

A diagram showing two tables, left table and right table, and the result set after a left join is performed on these tables. Records that are not of interest to right join have been grayed out.

RIGHT JOIN 구문

SELECT *
FROM left_table
RIGHT JOIN right_table
ON left_table.id = right_table.id;

참고. RIGHT JOIN은 RIGHT OUTER JOIN으로 쓸 수도 있음.

대통령·총리 테이블로 보는 RIGHT JOIN

SELECT p1.country, prime_minister, president
FROM prime_ministers AS p1
RIGHT JOIN presidents AS p2
USING(country);

| country        | prime_minister   | president               |
|----------------|------------------|-------------------------|
| Egypt          | Mostafa Madbouly | Abdel Fattah el-Sisi    |
| Portugal       | Luís Montenegro  | Marcelo Rebelo de Sousa |
| USA            | null             | Joe Biden               |
| Uruguay        | null             | Luis Lacalle Pou        |     
| Pakistan       | Shehbaz Sharif   | Asif Ali Zardari        |
| Chile          | null             | Gabriel Boric           |      
| India          | Narendra Modi    | Droupadi Murmu          |

LEFT JOIN vs RIGHT JOIN

RIGHT JOIN은 LEFT JOIN에 비해 덜 사용됨
모든 RIGHT JOIN은 LEFT JOIN으로 바꿔쓸 수 있음
왼쪽에서 오른쪽으로 타이핑할 때 LEFT JOIN이 사용자에게 더 직관적으로 느껴짐

연습해봅시다!