세미 조인과 안티 조인을 사용한 서브 쿼리

SQL에서 데이터 조인하기

Maham Faisal Khan

Senior Content Developer, DataCamp

조인 총정리

id 필드에 대한 INNER JOIN 다이어그램

A diagram showing two tables: left_table and right_table. The result of joining the two queries is shown on the right, returning only the records that matched on the id column for both tables.

SQL에서 데이터 조인하기

추가 조인

SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
SQL에서 데이터 조인하기

추가 조인

A diagram explaining an additive join. On the left side, two tables, left_table and right_table are displayed. On the right side, the result of an INNER JOIN using an id column on the two tables is shown. Fields with different names are added with their original names. Since the date field appears in both tables, it is added twice.

SQL에서 데이터 조인하기

세미 조인

세미 조인은 두 번째 테이블의 조건을 만족하는 첫 번째 테이블의 레코드를 선택함.

A diagram showing two tables, left_table and right_table, that will be used to demonstrate a semi join. left_table contains two columns, id and col1. right_table contains only one column, col2.

SQL에서 데이터 조인하기

세미 조인

A diagram showing two tables, left_table and right_table, that are being used to demonstrate a semi join. col2 will be used to filter col1. Records in left_table where col1 does not find a match in col2 have been faded out.

SQL에서 데이터 조인하기

세미 조인

A diagram showing two tables, left_table and right_table. The result of a semi join on both tables is shown on the right. Only records in left_table where col1 finds a match in col2 have been returned. These correspond to ids 2 and 3.

SQL에서 데이터 조인하기

세미 조인 시작하기

SELECT country, continent, president
FROM presidents;

| country  | continent     | president               |
| -------- | ------------- | ----------------------- |
| Egypt    | Africa        | Abdel Fattah el-Sisi    |
| Portugal | Europe        | Marcelo Rebelo de Sousa | 
| USA      | North America | Joe Biden               |
| Uruguay  | South America | Luis Lacalle Pou        |
| Pakistan | Asia          | Asif Ali Zardari        |
| Chile    | South America | Gabriel Boric           |
| India    | Asia          | Droupadi Murmu          |
SQL에서 데이터 조인하기

세미 조인 활용하기

SELECT country
FROM states
WHERE indep_year < 1800;

|----------|
| country  |
|----------|
| Portugal |
| Spain    |
|----------|
SQL에서 데이터 조인하기

세미 조인 완성하기(서브쿼리 소개)

SELECT president, country, continent
FROM presidents
WHERE country IN
    (SELECT country
     FROM states
     WHERE indep_year < 1800);

|-------------------------|-----------|-------------|
| president               | country   | continent   |
|-------------------------|-----------|-------------|
| Marcelo Rebelo de Sousa | Portugal  | Europe      |
|-------------------------|-----------|-------------|
SQL에서 데이터 조인하기

안티 조인

A diagram showing two tables, left_table and right_table, that are being used to demonstrate an anti join. col2 will be used to filter col1. Records in left_table where col1 does not find a match in col2 have NOT been faded out.

SQL에서 데이터 조인하기

안티 조인

A diagram showing two tables, left_table and right_table. The result of an anti join on both tables is shown on the right. Only records in left_table where col1 does not find a match in col2 have been returned. These correspond to ids 1 and 4.

SQL에서 데이터 조인하기

대통령들과 안티 조인

SELECT country, president
FROM presidents
WHERE continent LIKE '%America' 
    AND country NOT IN
        (SELECT country
         FROM states
         WHERE indep_year < 1800);

| country  |  president       |
| -------- | ---------------- | 
| Uruguay  | Luis Lacalle Pou |
| Chile    | Gabriel Boric    |
SQL에서 데이터 조인하기

연습해봅시다!

SQL에서 데이터 조인하기

Preparing Video For Download...