수색…


소개

SQL (Structured Query Language)에서 JOIN은 단일 쿼리에서 두 개의 데이터 테이블을 연결하여 한 번에 두 테이블의 데이터가 포함 된 집합을 반환하거나 한 테이블의 데이터를 두 번째 테이블에서 필터링하십시오. ANSI SQL 표준에는 여러 유형의 JOIN이 정의되어 있습니다.

내부 결합

Inner join 은 하나 이상의 조건 ​​( ON 키워드를 사용하여 지정)에 따라 두 표에 모두 일치 / 존재하는 레코드 / 행만 반환합니다. 가장 일반적인 유형의 조인입니다. inner join 의 일반 구문은 다음과 같습니다.

SELECT * 
FROM table_1
INNER JOIN table_2
  ON table_1.column_name = table_2.column_name

JOIN 으로 단순화 할 수도 있습니다.

SELECT * 
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name

/* Sample data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpets');
/* Sample data prepared. */

SELECT 
    * 
FROM 
    @Animal
    JOIN @AnimalSound
        ON @Animal.AnimalId = @AnimalSound.AnimalId;
AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpets

왼쪽 외부 조인을 사용한 내부 조인 사용 (존재하지 않음으로 대체)

이 쿼리는 테이블 1에서 테이블 2와 일치하는 필드와 조건 및 키를 사용하여 Table2와 비교할 때 테이블 1에없는 데이터 및 데이터를 반환합니다

select * 
  from Table1 t1
    inner join Table2 t2 on t1.ID_Column = t2.ID_Column 
    left  join Table3 t3 on t1.ID_Column = t3.ID_Column 
  where t2.column_name = column_value 
    and t3.ID_Column is null 
  order by t1.column_name;

크로스 조인

A cross join 은 데카르트 조인이며 두 테이블의 데카르트 곱을 의미합니다. 이 조인은 두 테이블을 조인하기위한 조건이 필요하지 않습니다. 왼쪽 테이블의 각 행은 오른쪽 테이블의 각 행에 연결됩니다. 교차 결합 구문 :

SELECT * FROM table_1
CROSS JOIN table_2 

예:

/* Sample data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
/* Sample data prepared. */

SELECT 
    * 
FROM 
    @Animal 
    CROSS JOIN @AnimalSound;

결과 :

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  1             1           Barks
3           Elephant             1             1           Barks
1           Dog                  2             2           Meows
2           Cat                  2             2           Meows
3           Elephant             2             2           Meows
1           Dog                  3             3           Trumpet
2           Cat                  3             3           Trumpet
3           Elephant             3             3           Trumpet

CROSS JOIN을 적용 할 수있는 다른 방법이 있습니다. 이것은 조건이없는 "구식" 조인 (ANSI SQL-92 이후 사용되지 않음)이므로 크로스 / 데카르트 조인이됩니다.

SELECT * 
FROM @Animal, @AnimalSound;

이 구문은 "항상 참"인 조인 조건으로도 작동하지만 권장되지 않으므로 가독성을 위해 명시 적 CROSS JOIN 구문을 사용하지 않는 것이 좋습니다.

SELECT * 
FROM 
    @Animal 
    JOIN @AnimalSound 
        ON 1=1

외부 조인

왼쪽 외부 조인

LEFT JOINON 절 조건이 충족되는 오른쪽 테이블의 행과 일치하는 왼쪽 테이블의 모든 행을 반환합니다. ON 절이 충족되지 않은 행은 모든 오른쪽 테이블의 열에 NULL 을가집니다. LEFT JOIN 구문은 다음과 같습니다.

SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

오른쪽 외부 조인

RIGHT JOINON 절 조건이 충족되는 왼쪽 테이블의 행과 일치하는 오른쪽 테이블의 모든 행을 반환합니다. ON 절이 충족되지 않은 행은 왼쪽 테이블의 모든 열에서 NULL 을가집니다. RIGHT JOIN 구문은 다음과 같습니다.

SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

전체 외부 조인

FULL JOINLEFT JOINRIGHT JOIN FULL JOIN 결합한 것입니다. ON 절의 조건이 충족되는지 여부에 관계없이 모든 행이 두 테이블에서 반환됩니다. ON 절을 만족하지 않는 행은 반대쪽 테이블의 모든 열에 NULL 과 함께 반환됩니다. 즉, 왼쪽 테이블의 행에 대해 오른쪽 테이블의 모든 열에 NULL 이 포함되고 반대의 경우도 마찬가지입니다. FULL JOIN 구문은 다음과 같습니다.

SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column  

예제들

/* Sample test data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @Animal (Animal) VALUES ('Frog');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (5, 'Roars');
/* Sample data prepared. */

LEFT OUTER JOIN

SELECT * 
FROM @Animal As t1 
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

LEFT JOIN 대한 검색 결과

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL

오른쪽 외부 조인

SELECT * 
FROM @Animal As t1 
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

RIGHT JOIN 대한 검색 결과

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
NULL        NULL                 4             5           Roars

전체 외부 조인

SELECT * 
FROM @Animal As t1 
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

FULL JOIN 대한 검색 결과

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL
NULL        NULL                 4             5           Roars

업데이트에서 조인 사용

UPDATE 문에서 조인을 사용할 수도 있습니다.

CREATE TABLE Users (
    UserId int NOT NULL,
    AccountId int NOT NULL,
    RealName nvarchar(200) NOT NULL
)

CREATE TABLE Preferences (
    UserId int NOT NULL,
    SomeSetting bit NOT NULL
)

다음과 같이 Users 테이블의 조건 SomeSetting 필터링 한 Preferences 테이블의 SomeSetting 열을 업데이트하십시오.

UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

p 는 명령.의 FROM 절에 정의 된 Preferences 의 별명입니다. Users 테이블의 AccountId 와 일치하는 행만 업데이트됩니다.

왼쪽 외부 조인 문으로 업데이트

Update t 
SET  t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2 
ON t2.ID=t.ID

내부 조인 및 집계 함수가있는 테이블 업데이트

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3  

하위 쿼리에 조인

자식 쿼리 / 세부 테이블에서 집계 데이터 (예 : Count, Avg, Max 또는 Min)를 가져와 부모 / 헤더 테이블의 레코드와 함께 표시하려는 경우 하위 쿼리에 조인하는 것이 자주 사용됩니다. 예를 들어, 날짜 또는 ID를 기준으로 상위 / 첫 번째 하위 행을 검색하거나 모든 하위 행 수 또는 평균을 원할 수 있습니다.

이 예에서는 여러 테이블이 관련되어있을 때 쿼리를 더 쉽게 읽을 수 있도록하는 별칭을 사용합니다. 이 경우 부모 테이블 Buy Orders에서 모든 행을 검색하고 하위 테이블 PurchaseOrderLineItems에서 마지막 (또는 가장 최근) 자식 행만 검색합니다. 이 예제에서는 자식 테이블이 증분 숫자 ID를 사용한다고 가정합니다.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo, 
  item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN 
     (
       SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Max(l.id) as Id 
       FROM PurchaseOrderLineItems l
       GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
     ) AS item ON item.PurchaseOrderId = po.Id

자체 조인

테이블은 자기 조인 (self join)이라고하는 자체에서 조인 될 수 있으며 테이블의 레코드를 같은 테이블의 다른 레코드와 결합합니다. 자체 조인은 일반적으로 테이블의 열에있는 계층 구조가 정의 된 쿼리에서 사용됩니다.

Employees 테이블의 샘플 데이터를 고려하십시오.

신분증 이름 보스 _ID
1 단발
2 1
2

각 직원의 Boss_ID 는 다른 직원의 ID 매핑됩니다. 상사의 이름이있는 직원 목록을 검색하려면이 매핑을 사용하여 테이블을 자체적으로 조인 할 수 있습니다. 이 방식으로 테이블을 조인 할 때는 원래 표와 구별하기 위해 테이블에 대한 두 번째 참조에서 별칭 (이 경우 Bosses 을 사용해야합니다.

SELECT Employees.Name,
    Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses 
    ON Employees.Boss_ID = Bosses.ID

이 쿼리를 실행하면 다음 결과가 출력됩니다.

이름 보스
단발
단발

가입을 사용하여 삭제

조인은 DELETE 문에서 사용될 수도 있습니다. 주어진 스키마는 다음과 같습니다.

CREATE TABLE Users (
    UserId int NOT NULL,
    AccountId int NOT NULL,
    RealName nvarchar(200) NOT NULL
)

CREATE TABLE Preferences (
    UserId int NOT NULL,
    SomeSetting bit NOT NULL
)

Preferences 테이블에서 행을 삭제할 수 있으며 다음과 같이 Users 테이블의 조건 자로 필터링 할 수 있습니다.

DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

여기서 p 는 명령문의 FROM 절에 정의 된 Preferences 의 별명이며 Users 테이블에서 일치하는 AccountId 가있는 행만 삭제합니다.

실수로 외부 조인을 내부 조인으로 변환

외부 조인은 하나 또는 두 테이블의 모든 행과 일치하는 행을 반환합니다.

Table People
PersonID FirstName
       1 Alice
       2 Bob
       3 Eve

Table Scores
PersonID Subject Score
       1 Math    100
       2 Math     54
       2 Science  98

테이블에 왼쪽으로 합류 :

Select * from People a
left join Scores b
on a.PersonID = b.PersonID

보고:

PersonID FirstName PersonID Subject Score
       1 Alice            1 Math    100
       2 Bob              2 Math     54
       2 Bob              2 Science  98
       3 Eve           NULL NULL   NULL

적용 가능한 수학 점수와 함께 모든 사람들을 돌려주고 싶다면 일반적인 실수는 다음과 같습니다 :

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'

이것은 Subject 이 그녀를 위해 NULL 이기 때문에 Bob의 과학 점수를 제거 할뿐만 아니라 결과에서 Eve를 제거합니다.

의 모든 개인 유지하면서 올바른 구문은 비 수학 레코드를 제거하는 People 것 테이블 :

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'


Modified text is an extract of the original Stack Overflow Documentation
아래 라이선스 CC BY-SA 3.0
와 제휴하지 않음 Stack Overflow