분류 sql

SQL 조인 자습서 : 교차 조인, 완전 외부 조인, 내부 조인, 왼쪽 조인 및 오른쪽 조인.

컨텐츠 정보

  • 조회 478 (작성일 )

본문

SQL 조인을 사용하면 관계형 데이터베이스 관리 시스템이 관계형이 될 수 있습니다.


https://www.freecodecamp.org/news/sql-joins-tutorial/ 


조인을 사용하면 분리 된 데이터베이스 테이블을 애플리케이션을 구동하는 관계로 다시 재구성 할 수 있습니다.


이 기사에서는 SQL의 각 조인 유형과 사용 방법을 살펴 보겠습니다.


조인이란 무엇입니까? 


조인은 두 행을 하나의 행으로 결합하는 작업입니다.


이러한 행은 일반적으로 두 개의 서로 다른 테이블에서 가져 오지만 반드시 그럴 필요는 없습니다.


조인 자체를 작성하는 방법을 보기 전에 조인의 결과가 어떻게 나타나는지 살펴 보겠습니다.


예를 들어 사용자 및 주소에 대한 정보를 저장하는 시스템을 살펴 보겠습니다.


사용자 정보를 저장하는 테이블의 행은 다음과 같습니다.


 id |     name     |        email        | age
----+--------------+---------------------+-----
  1 | John Smith   | johnsmith@gmail.com |  25
  2 | Jane Doe     | janedoe@Gmail.com   |  28
  3 | Xavier Wills | xavier@wills.io     |  3
...
(7 rows)

그리고 주소 정보를 저장하는 테이블의 행은 다음과 같습니다.


 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

사용자 정보와 주소 정보를 모두 검색하기 위해 별도의 쿼리를 작성할 수 있지만 이상적으로는 하나의 쿼리를 작성하고 동일한 결과 집합에서 모든 사용자와 해당 주소를 수신 할 수 있습니다.


이것이 바로 조인으로 우리가 할 수 있는 것입니다!


조인을 작성하는 방법을 곧 살펴 보겠지만 사용자 정보를 주소 정보에 조인하면 다음과 같은 결과를 얻을 수 있습니다.


 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | johnsmith@gmail.com |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | janedoe@Gmail.com   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | xavier@wills.io     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

여기에서 모든 사용자와 주소를 하나의 멋진 결과 집합으로 볼 수 있습니다.


결합 된 결과 집합을 생성하는 것 외에도 조인의 또 다른 중요한 용도는 필터링 할 수 있는 쿼리에 추가 정보를 가져 오는 것입니다.


예를 들어 오클라호마 시티에 거주하는 모든 사용자에게 실제 메일을 보내려는 경우 이 결합 된 결과 집합을 사용하고 도시 열을 기준으로 필터링 할 수 있습니다.


조인의 목적을 알았으니 이제 몇 가지를 작성해 보겠습니다!


데이터베이스 설정 


쿼리를 작성하기 전에 데이터베이스를 설정해야 합니다.


이 예에서는 PostgreSQL을 사용하지만 여기에 표시된 쿼리와 개념은 다른 최신 데이터베이스 시스템 (예 : MySQL, SQL Server 등)으로 쉽게 변환됩니다.


PostgreSQL 데이터베이스로 작업하기 위해 대화 형 PostgreSQL 명령 줄 프로그램인 psql을 사용할 수 있습니다. 작업을 즐기는 다른 데이터베이스 클라이언트가 있는 경우에도 괜찮습니다.


시작하려면 데이터베이스를 생성 해 보겠습니다. PostgreSQL이 이미 설치된 상태에서 터미널에서 createdb <database-name> 명령을 실행하여 새 데이터베이스를 생성 할 수 있습니다. 


$ createdb fcc

다음으로 psql 명령을 사용하여 대화 형 콘솔을 시작하고 \c <database-name>을 사용하여 방금 만든 데이터베이스에 연결하겠습니다.


$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

참고 :이 예제에서는 읽기 쉽도록 psql 출력을 정리 했으므로 여기에 표시된 출력이 터미널에서 본 것과 정확히 일치하지 않더라도 걱정하지 마십시오.


이 예제를 따라 수행하고 이러한 쿼리를 직접 실행하는 것이 좋습니다. 이 예제를 읽는 것보다 이 예제를 통해 작업함으로써 훨씬 더 많은 것을 배우고 기억하게 될 것입니다.


이제 조인으로!


CROSS JOIN 


우리가 할 수 있는 가장 간단한 조인은 CROSS JOIN 또는 "Cartesian product"입니다.


이 조인은 한 테이블에서 각 행을 가져 와서 다른 테이블의 각 행과 조인합니다.


하나는 1, 2, 3을 포함하고 다른 하나는 A, B, C를 포함하는 두 개의 목록이 있는 경우 이 두 목록의 데카르트 곱은 다음과 같습니다.


1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

첫 번째 목록의 각 값은 두 번째 목록의 각 값과 쌍을 이룹니다.


이 동일한 예제를 SQL 쿼리로 작성해 보겠습니다.


먼저 두 개의 매우 간단한 테이블을 만들고 여기에 데이터를 삽입 해 보겠습니다.


CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

두 개의 테이블, 문자와 숫자에는 단순한 텍스트 필드라는 하나의 열만 있습니다.


이제 CROSS JOIN으로 함께 결합 해 보겠습니다.


SELECT *
FROM letters
CROSS JOIN numbers;
 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

이것은 우리가 할 수 있는 가장 간단한 조인 유형입니다. 그러나 이 간단한 예에서도 조인이 작동하는 것을 볼 수 있습니다. 두 개의 개별 행 (하나는 문자, 하나는 숫자)이 결합되어 하나의 행을 형성합니다.


이러한 유형의 조인은 단순한 학문적 예로서 종종 논의되지만, 날짜 범위를 포함하는 하나 이상의 좋은 사용 사례가 있습니다.


CROSS JOIN with date ranges 


CROSS JOIN의 좋은 사용 사례 중 하나는 테이블에서 각 행을 가져 와서 날짜 범위 내에서 매일 적용하는 것입니다.


예를 들어 양치질, 아침 식사 또는 샤워와 같은 일상 업무를 추적하는 애플리케이션을 구축하고 있다고 가정 해보십시오.


모든 작업 및 지난주의 각 요일에 대한 레코드를 생성하려는 경우 날짜 범위에 대해 CROSS JOIN을 사용할 수 있습니다.


이 날짜 범위를 만들기 위해 generate_series 함수를 사용할 수 있습니다.


SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

generate_series 함수는 세 개의 매개 변수를 사용합니다.


첫 번째 매개 변수는 시작 값입니다. 이 예에서는 CURRENT_DATE-INTERVAL '5 일'을 사용합니다. 현재 날짜에서 5 일을 뺀 날짜 또는 "5 일 전"을 반환합니다.


두 번째 매개 변수는 현재 날짜 (CURRENT_DATE)입니다.


세 번째 매개 변수는 "단계 간격"또는 매번 값을 얼마나 증가 시킬 것인지입니다. 일상적인 작업이므로 1 일 간격 (INTERVAL '1 일')을 사용합니다.


모두 합치면 5 일 전에 시작하여 오늘 끝나고 한 번에 하루씩 일련의 날짜가 생성됩니다.


마지막으로 :: DATE를 사용하여 이러한 값의 출력을 날짜로 캐스팅하여 시간 부분을 제거하고 AS day를 사용하여 이 열의 별칭을 지정하여 출력을 좀 더 멋지게 만듭니다.


이 쿼리의 출력은 지난 5 일과 오늘을 더한 것입니다.


    day
------------
 2020-08-19
 2020-08-20
 2020-08-21
 2020-08-22
 2020-08-23
 2020-08-24
(6 rows)

일일 작업 예제로 돌아가서 완료하려는 작업을 보관하고 몇 가지 작업을 삽입하는 간단한 테이블을 만들어 보겠습니다.


CREATE TABLE tasks(
  name TEXT
);

INSERT INTO tasks(name) VALUES
('Brush teeth'),
('Eat breakfast'),
('Shower'),
('Get dressed');

작업 테이블에는 하나의 열, 이름 만 있으며 이 테이블에 4 개의 작업을 삽입했습니다.


이제 쿼리를 사용하여 작업을 CROSS JOIN하여 날짜를 생성 해 보겠습니다.


SELECT
  tasks.name,
  dates.day
FROM tasks
CROSS JOIN
(
  SELECT generate_series(
    (CURRENT_DATE - INTERVAL '5 day'),
    CURRENT_DATE,
    INTERVAL '1 day'
  )::DATE	AS day
) AS dates

(날짜 생성 쿼리는 실제 테이블이 아니므로 하위 쿼리로 작성합니다.)


이 쿼리에서 작업 이름과 요일을 반환하고 결과 집합은 다음과 같습니다.


     name      |    day
---------------+------------
 Brush teeth   | 2020-08-19
 Brush teeth   | 2020-08-20
 Brush teeth   | 2020-08-21
 Brush teeth   | 2020-08-22
 Brush teeth   | 2020-08-23
 Brush teeth   | 2020-08-24
 Eat breakfast | 2020-08-19
 Eat breakfast | 2020-08-20
 Eat breakfast | 2020-08-21
 Eat breakfast | 2020-08-22
 ...
 (24 rows)

예상대로 날짜 범위의 매일 각 작업에 대한 행을 얻습니다.


CROSS JOIN은 우리가 할 수 있는 가장 간단한 조인이지만 다음 몇 가지 유형을 살펴 보려면 보다 사실적인 테이블 설정이 필요합니다.


Creating directors and movies 


다음 조인 유형을 설명하기 위해 영화 및 영화 감독의 예를 사용합니다.


이 상황에서 영화에는 한 명의 감독이 있지만 영화에는 감독이 있어야 하는 것은 아닙니다. 새 영화가 발표되지만 감독의 선택이 아직 확인되지 않았다고 가정 해보십시오.


directors 테이블에는 각 감독의 이름이 저장되고 movies 테이블에는 영화의 이름과 영화 감독에 대한 참조 (있는 경우)가 저장됩니다.


이 두 테이블을 만들고 여기에 데이터를 삽입 해 보겠습니다.


CREATE TABLE directors(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO directors(name) VALUES
('John Smith'),
('Jane Doe'),
('Xavier Wills')
('Bev Scott'),
('Bree Jensen');

CREATE TABLE movies(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  director_id INTEGER REFERENCES directors 
);

INSERT INTO movies(name, director_id) VALUES
('Movie 1', 1),
('Movie 2', 1),
('Movie 3', 2),
('Movie 4', NULL),
('Movie 5', NULL);

우리는 5 명의 감독, 5 개의 영화가 있고, 그 중 3 개의 영화에는 그들에게 감독이 배정되어 있습니다. 감독 ID 1에는 2 개의 영화가 있고 감독 ID 2에는 1 개의 영화가 있습니다.


FULL OUTER JOIN 


이제 작업 할 데이터가 있으므로 FULL OUTER JOIN을 살펴 보겠습니다.


FULL OUTER JOIN은 CROSS JOIN과 몇 가지 유사점이 있지만 몇 가지 중요한 차이점이 있습니다.


첫 번째 차이점은 FULL OUTER JOIN에는 조인 조건이 필요하다는 것입니다.


조인 조건은 두 테이블 간의 행이 서로 관련되는 방식과 함께 조인 되어야 하는 기준을 지정합니다.


이 예에서 movies 테이블에는 director_id 열을 통해 director에 대한 참조가 있으며 이 열은 directors 테이블의 id 열과 일치합니다. 조인 조건으로 사용할 두 개의 열입니다.


두 테이블간에 이 조인을 작성하는 방법은 다음과 같습니다.


SELECT *
FROM movies
FULL OUTER JOIN directors
  ON directors.id = movies.director_id;

영화가 감독과 일치하도록 지정한 결합 조건을 확인하십시오. ON movies.director_id = directors.id.


결과 집합은 일종의 이상한 데카르트 곱처럼 보입니다.


  id  |  name   | director_id |  id  |     name
------+---------+-------------+------+--------------
    1 | Movie 1 |           1 |    1 | John Smith
    2 | Movie 2 |           1 |    1 | John Smith
    3 | Movie 3 |           2 |    2 | Jane Doe
    4 | Movie 4 |        NULL | NULL | NULL
    5 | Movie 5 |        NULL | NULL | NULL
 NULL | NULL    |        NULL |    5 | Bree Jensen
 NULL | NULL    |        NULL |    4 | Bev Scott
 NULL | NULL    |        NULL |    3 | Xavier Wills
(8 rows)

우리가 보는 첫 번째 행은 영화에 감독이 있고 우리의 조인 조건이 참으로 평가 된 행입니다.


그러나 이러한 행 뒤에는 각 테이블의 나머지 행이 각각 표시되지만 다른 테이블과 일치하지 않는 NULL 값이 있습니다.


참고 : NULL 값에 익숙하지 않은 경우 이 SQL 연산자 자습서의 설명을 참조하십시오. 


또한 여기서 CROSS JOIN과 FULL OUTER JOIN의 또 다른 차이점을 볼 수 있습니다. FULL OUTER JOIN은 여러 테이블이 있는 CROSS JOIN과 달리 각 테이블에서 하나의 고유 한 행을 반환합니다.


INNER JOIN 


다음 조인 유형 인 INNER JOIN은 가장 일반적으로 사용되는 조인 유형 중 하나입니다.


내부 조인은 조인 조건이 참인 행만 반환합니다.


이 예에서 영화와 감독 테이블 간의 내부 조인은 영화에 감독이 할당 된 레코드 만 반환합니다.


구문은 기본적으로 이전과 동일합니다.


SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id;

결과는 감독이 있는 세 편의 영화를 보여줍니다.


 id |  name   | director_id | id |    name
----+---------+-------------+----+------------
  1 | Movie 1 |           1 |  1 | John Smith
  2 | Movie 2 |           1 |  1 | John Smith
  3 | Movie 3 |           2 |  2 | Jane Doe
(3 rows)

내부 조인에는 조인 조건과 일치하는 행만 포함되므로 조인에서 두 테이블의 순서는 중요하지 않습니다.


쿼리에서 테이블 순서를 반대로 하면 동일한 결과를 얻습니다.


SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id;
 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

이 쿼리에서 먼저 directors 테이블을 나열하고 모든 열 (SELECT *)을 선택 했으므로 directors 열 데이터가 먼저 표시되고 영화의 열이 표시되지만 결과 데이터는 동일합니다.


이것은 내부 조인의 유용한 속성이지만 다음 유형과 같은 모든 조인 유형에 해당되는 것은 아닙니다.


LEFT JOIN / RIGHT JOIN 


다음 두 조인 유형은 결과 세트에 포함되는 테이블의 데이터에 영향을 주는 수정 자 (LEFT 또는 RIGHT)를 사용합니다.


참고 : LEFT JOIN 및 RIGHT JOIN은 LEFT OUTER JOIN 및 RIGHT OUTER JOIN이라고도 합니다. 


이러한 조인은 특정 테이블의 모든 데이터와 관련 테이블의 데이터 (있는 경우)를 모두 반환하려는 쿼리에 사용됩니다.


관련 데이터가 존재하지 않는 경우에도 "기본"테이블의 모든 데이터를 반환합니다.


특정 항목에 대한 정보와 해당 보너스 정보가 있는 경우 보너스 정보에 대한 쿼리입니다.


이것은 예를 들어 이해하기 쉽습니다. 모든 영화와 감독을 찾아 보겠습니다.하지만 감독이 있든 없든 상관하지 않습니다. 보너스입니다.


SELECT *
FROM movies
LEFT JOIN directors
  ON directors.id = movies.director_id;

쿼리는 이전과 동일한 패턴을 따릅니다. 방금 조인을 LEFT JOIN으로 지정했습니다.


이 예에서 movies 테이블은 "왼쪽"테이블입니다.


한 줄에 쿼리를 작성하면 좀 더 쉽게 볼 수 있습니다.


... FROM movies LEFT JOIN directors ...

왼쪽 조인은 "왼쪽"테이블의 모든 레코드를 반환합니다.


왼쪽 조인은 조인 조건과 일치하는 "오른쪽"테이블의 모든 행을 반환합니다.


조인 조건과 일치하지 않는 "오른쪽"테이블의 행은 NULL로 반환 됩니다.


 id |  name   | director_id |  id  |    name
----+---------+-------------+------+------------
  1 | Movie 1 |           1 |    1 | John Smith
  2 | Movie 2 |           1 |    1 | John Smith
  3 | Movie 3 |           2 |    2 | Jane Doe
  4 | Movie 4 |        NULL | NULL | NULL
  5 | Movie 5 |        NULL | NULL | NULL
(5 rows)

그 결과 집합을 살펴보면 이러한 유형의 조인이 "이 모든 것과 존재하는 경우 일부"유형 쿼리에 유용한 이유를 알 수 있습니다.


RIGHT JOIN 


RIGHT JOIN은 LEFT JOIN과 똑같이 작동하지만 두 테이블에 대한 규칙이 반대입니다.


오른쪽 조인에서는 "오른쪽"테이블의 모든 행이 반환 됩니다. "왼쪽"테이블은 조인 조건에 따라 조건부로 반환됩니다.


위와 동일한 쿼리를 사용하지만 RIGHT JOIN 대신 LEFT JOIN을 사용합니다.


SELECT *
FROM movies
RIGHT JOIN directors
  ON directors.id = movies.director_id;
  id  |  name   | director_id | id |     name
------+---------+-------------+----+--------------
    1 | Movie 1 |           1 |  1 | John Smith
    2 | Movie 2 |           1 |  1 | John Smith
    3 | Movie 3 |           2 |  2 | Jane Doe
 NULL | NULL    |        NULL |  5 | Bree Jensen
 NULL | NULL    |        NULL |  4 | Bev Scott
 NULL | NULL    |        NULL |  3 | Xavier Wills
(6 rows)

이제 결과 집합은 모든 directors 행과 영화 데이터 (있는 경우)를 반환합니다.


우리가 한 것은 "기본"테이블 (관련 데이터가 있는지 여부에 관계없이 모든 데이터를 보려는 테이블)을 고려 중인 테이블을 전환하는 것 뿐입니다.


프로덕션 애플리케이션에서 LEFT JOIN / RIGHT JOIN 


프로덕션 응용 프로그램에서는 LEFT JOIN 만 사용하고 RIGHT JOIN은 사용하지 않습니다.


제 생각에는 LEFT JOIN을 사용하면 쿼리를 더 쉽게 읽고 이해할 수 있기 때문입니다.


쿼리를 작성할 때 "기본"결과 집합으로 시작하여 모든 영화를 말한 다음 해당 기본에서 사물 그룹을 가져 오거나 빼는 것을 생각합니다.


나는 기본으로 시작하는 것을 좋아하기 때문에 LEFT JOIN은 이러한 생각에 적합합니다. 기본 테이블 ( "왼쪽"테이블)의 모든 행을 원하고 "오른쪽"테이블의 행을 조건부로 원합니다.


실제로 프로덕션 애플리케이션에서 RIGHT JOIN을 본 적이 없다고 생각합니다. RIGHT JOIN에는 잘못된 것이 없습니다. 쿼리를 이해하기 더 어렵게 만든다고 생각합니다.


RIGHT JOIN 재 작성 


위의 시나리오를 뒤집고 대신 모든 감독과 조건부로 영화를 반환하려면 RIGHT JOIN을 LEFT JOIN으로 쉽게 다시 작성할 수 있습니다.


쿼리에서 테이블 순서를 뒤집고 RIGHT를 LEFT로 변경하기 만하면 됩니다.


SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

참고 : 조인 중인 테이블 (위의 영화 예에서 "오른쪽"테이블)을 조인 조건 (ON movies.director_id = ...)에 먼저 배치하고 싶지만 이것은 개인적인 선호 사항입니다. 


Filtering using LEFT JOIN 


LEFT JOIN (또는 RIGHT JOIN)을 사용하는 두 가지 사용 사례가 있습니다.


우리가 이미 다룬 첫 번째 사용 사례 : 한 테이블에서 모든 행을 반환하고 다른 테이블에서 조건부로 반환하는 것입니다.


두 번째 사용 사례는 두 번째 테이블의 데이터가 없는 첫 번째 테이블의 행을 반환하는 것입니다.


시나리오는 다음과 같습니다. 영화에 속하지 않는 감독을 찾습니다.


이를 위해 LEFT JOIN으로 시작하고 directors 테이블이 기본 또는 "왼쪽"테이블이 됩니다.


SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

영화에 속하지 않는 감독의 경우 movies 테이블의 열은 NULL입니다.


 id |     name     |  id  |  name   | director_id
----+--------------+------+---------+-------------
  1 | John Smith   |    1 | Movie 1 |           1
  1 | John Smith   |    2 | Movie 2 |           1
  2 | Jane Doe     |    3 | Movie 3 |           2
  5 | Bree Jensen  | NULL | NULL    |        NULL
  4 | Bev Scott    | NULL | NULL    |        NULL
  3 | Xavier Wills | NULL | NULL    |        NULL
(6 rows)

이 예에서 감독 ID 3, 4, 5는 영화에 속하지 않습니다.


결과 집합을 이러한 행으로 만 필터링 하려면 영화 데이터가 NULL 인 행만 반환하도록 WHERE 절을 추가 할 수 있습니다.


SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NULL;
 id |     name     |  id  | name | director_id
----+--------------+------+------+-------------
  5 | Bree Jensen  | NULL | NULL |        NULL
  4 | Bev Scott    | NULL | NULL |        NULL
  3 | Xavier Wills | NULL | NULL |        NULL
(3 rows)

그리고 영화 없는 세 명의 감독이 있습니다!


(WHERE movies.id IS NULL)에 대해 필터링 하기 위해 테이블의 id 열을 사용하는 것이 일반적이지만 movies 테이블의 모든 열은 NULL이므로 둘 중 하나가 작동합니다.


(movie 테이블의 모든 열이 NULL이라는 것을 알고 있으므로 위의 쿼리에서 SELECT * 대신 SELECT directors. *를 작성하여 모든 감독 정보를 반환 할 수 있습니다.)


LEFT JOIN을 사용하여 일치 항목 찾기 


이전 쿼리에서 영화에 속하지 않은 감독을 찾았습니다.


동일한 구조를 사용하여 영화 데이터가 NULL이 아닌 행을 찾도록 WHERE 조건을 변경하여 영화에 속하는 감독을 찾을 수 있습니다.


SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NOT NULL;
 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

이것은 편리해 보일 수 있지만 실제로 INNER JOIN을 다시 구현했습니다!


Multiple joins 


두 테이블을 함께 조인하는 방법을 살펴 봤지만 한 행에 여러 조인을 하면 어떨까요?


실제로는 매우 간단하지만 이를 설명하기 위해 세 번째 테이블 인 티켓이 필요합니다.


이 테이블은 영화에 대해 판매 된 티켓을 나타냅니다.


CREATE TABLE tickets(
  id SERIAL PRIMARY KEY,
  movie_id INTEGER REFERENCES movies NOT NULL
);

INSERT INTO tickets(movie_id) VALUES (1), (1), (3);

티켓 테이블에는 영화 (movie_id)에 대한 ID와 참조 만 있습니다.


또한 영화 ID 1 용으로 판매 된 티켓 2 장과 영화 ID 3 용으로 판매 된 티켓 1 장을 삽입했습니다.


이제 감독을 영화에 참여 시키고 영화를 티켓에 참여 시키자!


SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id
INNER JOIN tickets
  ON tickets.movie_id = movies.id;

이들은 내부 조인이기 때문에 조인을 작성하는 순서는 중요하지 않습니다. 티켓으로 시작한 다음 영화에 참여하고 감독에 참여할 수 있습니다.


다시 한 번 쿼리 하려는 내용과 쿼리를 가장 이해하기 쉽게 만드는 요소가 있습니다.


결과 집합에서 반환 되는 행의 범위를 더 좁혔음을 알 수 있습니다.


 id |    name    | id |  name   | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
  1 | John Smith |  1 | Movie 1 |           1 |  1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |  2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |  3 |        3
(3 rows)

또 다른 INNER JOIN을 추가했기 때문에 의미가 있습니다. 실제로 이것은 쿼리에 또 다른 "AND"조건을 추가합니다.


우리의 쿼리는 기본적으로 "티켓 판매가 있는 영화에 속한 모든 감독을 반환합니다."라고 말합니다.


대신 아직 티켓 판매가 없는 영화에 속한 감독을 찾으려면 마지막 INNER JOIN을 LEFT JOIN으로 대체 할 수 있습니다.


SELECT *
FROM directors
JOIN movies
  ON movies.director_id = directors.id
LEFT JOIN tickets
  ON tickets.movie_id = movies.id;

이제 Movie 2가 결과 세트로 돌아온 것을 볼 수 있습니다.


 id |    name    | id |  name   | director_id |  id  | movie_id
----+------------+----+---------+-------------+------+----------
  1 | John Smith |  1 | Movie 1 |           1 |    1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |    2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |    3 |        3
  1 | John Smith |  2 | Movie 2 |           1 | NULL |     NULL
(4 rows)

이 영화는 티켓 판매가 없었기 때문에 이전에 INNER JOIN으로 인해 결과 집합에서 제외되었습니다.


독자를 위한 연습 문제로 남겨 두겠습니다. 티켓 판매가 없는 영화에 속한 감독을 어떻게 찾을 수 있습니까?


Join execution order 


결국 우리는 조인이 어떤 순서로 실행되는지는 신경 쓰지 않습니다.


SQL과 다른 최신 프로그래밍 언어의 주요 차이점 중 하나는 SQL이 선언적 언어라는 것입니다.


즉, 원하는 결과는 지정하지만 실행 세부 정보는 지정하지 않습니다. 이러한 세부 정보는 데이터베이스 쿼리 플래너에 맡깁니다. 원하는 조인과 조건을 지정하면 쿼리 플래너가 나머지를 처리합니다.


그러나 실제로 데이터베이스는 세 개의 테이블을 동시에 결합하지 않습니다. 대신 처음 두 테이블을 하나의 중간 결과로 결합한 다음 해당 중간 결과 집합을 세 번째 테이블에 결합 할 가능성이 있습니다.


(참고 : 이것은 다소 단순화 된 설명입니다.)


따라서 쿼리에서 여러 조인으로 작업 할 때 이러한 테이블 중 하나가 상당히 커질 수 있지만 두 테이블 간의 일련의 조인으로 생각할 수 있습니다.


Joins with extra conditions 


우리가 다룰 마지막 주제는 추가 조건이 있는 조인입니다.


WHERE 절과 유사하게 조인 조건에 원하는 만큼 조건을 추가 할 수 있습니다.


예를 들어 "John Smith"라는 이름이 아닌 감독이 있는 영화를 찾으려면 AND를 사용하여 조인에 추가 조건을 추가 할 수 있습니다.


SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
  AND directors.name <> 'John Smith';

이 조인 조건에서 WHERE 절에 넣을 모든 연산자를 사용할 수 있습니다.


조건을 대신 WHERE 절에 넣는 경우에도 이 쿼리에서 동일한 결과를 얻습니다.


SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
WHERE directors.name <> 'John Smith';

여기에는 몇 가지 미묘한 차이가 있지만 이 기사의 목적 상 결과 집합은 동일합니다.


(SQL 쿼리를 필터링 할 수 있는 모든 방법에 익숙하지 않은 경우 여기에서 앞서 언급 한 기사를 확인하십시오.)


조인으로 쿼리 작성에 대한 현실 


실제로 저는 조인을 세 가지 방법으로 만 사용합니다.


INNER JOIN 


첫 번째 사용 사례는 두 테이블 간의 관계가 존재하는 레코드입니다. 이것은 INNER JOIN에 의해 ​​수행됩니다.


"감독이 있는 영화"또는 "게시물이 있는 사용자"를 찾는 것과 같은 상황입니다.


LEFT JOIN 


두 번째 사용 사례는 한 테이블의 레코드이며 관계가 있는 경우 두 번째 테이블의 레코드입니다. 이것은 LEFT JOIN에 의해 ​​수행됩니다.


"감독이 있는 경우 감독이 있는 영화"또는 "게시물이 있는 경우 게시물이 있는 사용자"와 같은 상황입니다.


LEFT JOIN exclusion 


세 번째로 가장 일반적인 사용 사례는 LEFT JOIN에 대한 두 번째 사용 사례입니다. 한 테이블에서 두 번째 테이블에서 관계가 없는 레코드를 찾습니다.


"감독이 없는 영화"또는 "게시물이 없는 사용자"와 같은 상황입니다.


매우 유용한 두 가지 조인 유형 


프로덕션 응용 프로그램에서 FULL OUTER JOIN 또는 RIGHT JOIN을 사용한 적이 없다고 생각합니다. 유스 케이스가 충분히 자주 나오지 않거나 쿼리가 더 명확한 방식으로 작성 될 수 있습니다 (RIGHT JOIN의 경우).


저는 가끔 CROSS JOIN을 사용하여 날짜 범위에 걸쳐 레코드를 분산하는 것과 같은 작업을 수행했지만 (처음 살펴본 것처럼) 그 시나리오도 너무 자주 발생하지 않습니다.


그래서 좋은 소식입니다! 실제로 만나게 될 사용 사례의 99.9 %에 대해 이해해야 하는 조인 유형은 INNER JOIN과 LEFT JOIN입니다!