Microsoft SQL Server
어울리다
수색…
소개
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 JOIN 은 ON 절 조건이 충족되는 오른쪽 테이블의 행과 일치하는 왼쪽 테이블의 모든 행을 반환합니다. ON 절이 충족되지 않은 행은 모든 오른쪽 테이블의 열에 NULL 을가집니다. LEFT JOIN 구문은 다음과 같습니다.
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
오른쪽 외부 조인
RIGHT JOIN 은 ON 절 조건이 충족되는 왼쪽 테이블의 행과 일치하는 오른쪽 테이블의 모든 행을 반환합니다. ON 절이 충족되지 않은 행은 왼쪽 테이블의 모든 열에서 NULL 을가집니다. RIGHT JOIN 구문은 다음과 같습니다.
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
전체 외부 조인
FULL JOIN 은 LEFT JOIN 과 RIGHT 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'
