Clustered 는 Leaf Page 에 데이터가 들어가고 이전까지 보던 방식 Non-Clustered 같은 경우는 RID 를 통해 한번 경유하여 데이터를 저장하는 방식이다
Clustered 영한사전 , Non-Clustered 색인
Clustered : 실제로 논리적으로 정렬 되는 데이터 순서가 키 값 정렬 순서가 된다 Leaf Page = Data Page 끝 노드에 실제 데이터가 들어가는 형태 데이터는 clustered index 키 순서로 정렬
Non-Clustered 는 clustered index 유무에 따라서 다르게 동작한다
Non-Clustered 인데 clustered index 가 없는 경우(추가 안한 경우) leaf page 에는 heap table 에 대한 heap rid가 존재하여 이 rid 로 -> Heap table 에 접근 데이터 얻어온다 > heap rid = Heap table 에서 어느 주소에 몇번째 슬롯에 데이터가 있는지를 알 수 있는 RID > 데이터는 heap table 이 있고 이곳에 데이터들이 저장된다 (즉 leaf Page 가 인덱스 페이지가 아니고 데이터 페이지가 아니라는 얘기)
Non-Clustered 가 키가 있는데 이때 clustered index 를 추가 하게 되면 heap table 이 없고 leaf page 에 데이터가 있는 상태이다 => leaf table 에 실제 데이터가 있게된다 >heap table 이 없는 대신에 Clustered Index 의 실제 데이터 키 값을 들고 있게 된다, 이 키 값을 통해 cluster page 에서 데이터를 찾게 된다 > Non clustered 에서 먼저 트리를 통해서 자식 중에서 cluster id에 를 찾고 이 id를 통해 cluster index 가 이루고 있는 트리 부모로 가서 여기서 다시 id 를 찾는 형태가 된다 Non-Clustered 가 기존에 있을때 clustered index 를 추가 하면 기존에 Non-Clustered 도 영향을 주어 한단계 더 찾게 된다
기존 테이블을 지우고 테이블을 새로 하나 만든 다음 non clustered index 를 생성 하여 테스트
select *
into TestORderDetails
from [Order Details];
select *
from TestORderDetails;
--non clustered index 를 생성
create index Index_OrderDetails
ON TestOrderDetails(orderID, ProductID);
--TestORderDetails 테이블에 있는 인덱스 정보 보기
exec sp_helpindex 'TestORderDetails';
TestORderDetails 테이블에 있는 인덱스 정보는 다음과 같다는걸 알 수 있다
인덱스 번호를 찾아 보려면 다음 처럼 작성하면 된다
sys.indexes 에서 오브젝트 id 를 비교하여 TestORderDetails 오브젝트 id 롸 같은 오브젝트의 index_id 들을 가져올 수 있다
select index_id, name
from sys.indexes
where object_id = object_id('TestORderDetails');
TestORderDetails 의 index_id 는 2 번이고 2 번으로 인덱스를 담고 있는 인덱스 페이지들을 볼 수 있다
--Index_OrderDetails Index_id 가 2 임으로 2를 인저로 넣어 페이지들을 보기
DBCC IND('Northwind', 'TestORderDetails', 2);
1048 이 root 고 나머지가 자식으로 들어간 걸 indexLevel 을 통해 알 수 있다
1048
1008 1016 1017 1018 1019 1020
이 중에서 1008 페이지 정보를 보면
--페이지 정보 보기
DBCC PAGE('Northwind', 1 , 1008, 3);
이 처럼 HEAP RID 가 있는 것을 알 수 있다 (index 를 non clustered index 로 생성했기 때문)
Heap RID = ([페이지 주소(4)[파일ID(2)[슬롯(2)] ROW) 의 바이트들로 구성 되는데이 ID 를 통해 Heap Table 테이블에서
Heap Table = [{page} {page}{page}{page}]
몇번째 페이지 몇번 슬롯으로 되어 있는지를 Heap RID가 가리키고 있기 때문에
1008 에서 Heap RID 를 통해서 Heap Table 에 있는 데이터를 바로 검색해 찾을 수 있게 된다
DBCC 로 페이지 정보를 볼때 나오는 PageType 은
1 : Data Page
2 : Index Page
를 나타낸다
현재 까지 상황은
NonClusted Index 가 추가 된 상황이고 여기서 Clustered index 를 추가 해보면
-- clustered index 추가
create clustered index Index_OrderDetails_Clustered
ON TestOrderDetails(orderID);
이렇게 추가 된것을 볼 수 있다
이때 다시 2번 Non Clustered index 를 살펴보면 어떻게 달라져는지를 보면
cluster index 추가 하기 전
cluster index 추가 한후
페이지 번호가 바뀐 것을 알 수 있다
기존에는
1048
1008 1016 1017 1018 1019 1020
이런 번호였지만
1024 부터 자식이 시작 하는 걸 알 수 있는데 clustered index 를 추가 하게 되면
1072
1024 10321033103410351036
로 바뀌었다, 이때 1024 페이지 정보를 보면
--페이지 정보 보기
DBCC PAGE('Northwind', 1 , 1024, 3);
원래는 Heap Rid 가 기존엔 있었지만 clustered index 를 추가하여 Heap Rid 가 없어진 것을 알 수 있다
그리고 페이지 id 가 전과 후가 달라진 것도 이전엔 Heap Rid로 heap table 을 찾는 형식이였지만
이젠 직접적으로 찾게 되니 PagePid 또한 달라지게 된 것
clustered index 를 생성할때 OrderID 에 대해 만들었는데
중복된 OrderID 를 볼 수 있는데 이것은 같은데이터에 한하여 동일한 ID 가 붙는 것이고 동일한 데이터를 구분하기 위하여
UNIQUIFIER 필드가 추가 되어 이들을 구분하게 끔 되어 있다
OrderID + UNIQUIFIER 조합으로 식별한다
정리하자면 Non clustered index 에서 Clustered index 를 추가 하면 기존 Heap RID 가 날라가게 된다는 것이다
위에서 본건 Non clusteered 인덱스 가 있었던건 본것이였는데
이번엔 그에 반해 위에서 새로 추가 했던 clustered 를 보면
--TestORderDetails 테이블에 있는 Index_id 보기
select index_id, name
from sys.indexes
where object_id = object_id('TestORderDetails');
테이블에 있는 인덱스 중에서 인덱스 1번은 검색
--Index_OrderDetails Index_id 가 2 임으로 2를 인저로 넣어 페이지들을 보기
DBCC IND('Northwind', 'TestORderDetails', 1);
9184
9144 915291539154915591569157915891599160
이렇게 구성 되어 있다 즉 여기서 알수 있는건
DBCC 로 페이지 정보를 볼때 나오는PageType은
1 : Data Page
2 : Index Page
였기 때문에 PageType 을 보면 PagePID 가 갖고 있는것이 바로 데이터 페이지가 된다는 걸 알수 있다
정리하자면
Clustered index 있으면 leaf page 가 바로 데이터가 되는것이고, Heap Table 또한 없다
Clustered index 가 없다면 Heap RID로 Heap Table 을 검색해서 데이터를 찾는 한번 경유하는 형태가 된다는 것이다 이때는 Heap Table 또한 생성되게 된다
use Northwind;
SELECT *
FROM [Order Details]
ORDER BY OrderID;
select *
into TestORderDetails
from [Order Details];
select *
from TestORderDetails;
--복합 인덱스 추가
create index Index_TestOrderDetails
ON TestOrderDetails(orderID, ProductID);
--인덱스 정보 보기
exec sp_helpindex 'TestORderDetails';
--Index Seek 이건 인덱스를 활용해 빨리 찾는 것인데
--Index Scan 은 Index Full Scan 으로 전체 다 찾는 거라 느리다
--아래 처럼 and 를 쓰면서 인덱스통하여 검색을 하면 Index Seek 가 일어난 다는 걸 알 수 있다
select *
from TestORderDetails
where OrderID = 10248 and ProductID = 11;
--index seek 으로 검색
select *
from TestORderDetails
where OrderID = 10248;
--table scan 으로 찾음
--이유는 ON TestOrderDetails(orderID, ProductID) 이렇게 생성했을대 orderID 가 첫번째에 있었기 때문에
--orderID 로 찾기를 시작할땐 index seek 로 1차적으로 찾는데
--그렇지 않으면 ProductID 로 찾게 되면 2차적으로 정렬하여 table scan 으로 찾게 된다
select *
from TestORderDetails
where ProductID = 10248;
--index 정보 보기
DBCC IND('Northwind', 'TestORderDetails', 2);
DBCC PAGE('Northwind', 1 , 9160, 3);
복합 인덱스를 두개에대해 추가(orderID, ProductID) 했다 create index Index_TestOrderDetails ON TestOrderDetails(orderID, ProductID);
이렇게 하면 데이터를 찾을때 성능에 대한 차이가 발생 하는 이유는 데이터를 정렬 하는데 있어서 차이가 있어서 그런것인데 인덱스가 두개라서 어떻게 검색에 대한 처리를 하는지 보면
--index 정보 보기
DBCC IND('Northwind', 'TestORderDetails', 2);
9200이 가장 상단이고 나머지는 모두 자식 즉 Leaf 노드 인 것을 알 수 있다(IndexLevel 이 0 임으로)
Leaf 노드의 순서가 트리상 동일한 레벨에서 9168 -> 9169 -> 9170-> 이런 순으로 나가기 때문에
9160에 대한 노드 정보를 보면
OrderID 와 ProductID 두개를 보면 우선 OrderID 로 정렬을 하는데 만약 같은 ID 라면 그다음 ProductID로 비교하는 걸 알 수있다
OrderID 는 정렬이 되어 있는 반면 ProductID 는 첫번째 것을 기준으로 정렬 되어 있어서
바로 ProductID로 찾으려 하면 정렬이 안되어 있는것과 마찬가지 임으로
ProductID 로 검색 기준을 삼으면 Scan 이 되는 것 => 느림
인덱스(A, B) 를 글었다면 인덱스 A 에 대해 따로 인덱스를 걸어줄 필요는 없는데
B로도 검색이 필요하다면 인덱스(B) 에 대해 별도로 걸어줘야 빨라지게 된다
주의 : A,B 둘다 인덱스가 독립적으로 걸려있는게 아니다
페이지 분할(SPLIT) : 정리하면 인덱스를 생성하면 원소들이 페이지 단위로 관리 되는데 이 페이지 단위마다 관리 개수를 넘어가면 추가 페이지를 만들어 원소들을 관리하는 구조가 된다
데이터 50개 강제로 추가 해보면 인덱스 데이터 추가 /갱신/ 삭제시 그 중에서 추가 하는 경우를 보자
DECLARE @i INT = 0;
WHILE @i < 50
BEGIN
INSERT INTO TestORderDetails
VALUES (10248, 100 + @i, 10, 1, 0);
SET @i = @i +1;
END
이렇게 하면 추가 페이지가 만들어지는데 그 이유는 한 페이지에 내용이 너무 많아 지면 기존 페이지에 있던 요소 를 두개,세개 로 분할하여 담기 때문이 새로 페이지가 추가된것을 알 수 있고 이것을 페이지 Split 이라 한다
주의 : 인덱스를 Name 에 대해 걸어 놓았을때 SUBSTRING(NAME,1 ,2) 을 하게 되면 인덱스를 사용하는 최적화 사용이 불가능함으로 Index Scan 을 하게 되는 경우가 발생할 수 있다
SUBSTRING 으로 문자를 일부 가져와 어떤 문자를 찾는 것이 아닌
WHERE Name LIKE 'Ba%' ; 식으로 찾으면 Name 에 대해 최적화를 적용 받을 수 있다
실행 할때 Ctrl + L 로 Index Seek 인디 Index Scan 인지 확인하는 것이 좋다
USE Northwind;
--db 정보 보기
EXEC sp_helpdb 'Northwind';
create table Test
(
EmployeeID INT NOT NULL,
LastName NVARCHAR(20) NULL,
FirstName NVARCHAR(20) NULL,
HireDate DATETIME NULL,
);
SELECT *
FROM Test;
--Employees 데이터를 Test 에 추가한다
INSERT INTO Test
SELECT EmployeeID, LastName, FirstName, HireDate
FROM Employees;
RESULT
--index 를 걸 컬럼= LastName
--FILLFACTOR 리프 페이지 공간 1%만 사용 , 전체 사용 공간 중에 1%만 사용 하겠다는 것, 이러면 데이터가 다 안들어가기 때문에 별도 트리구조로 데이터를 저장함
--PAD_INDEX (FILFACTOR 가 중간 페이지에도 적용되게 하는 것) => 결과적으로 공간을 비효율적으로 사용하게끔 하는 것인데 목적이 테스트 하는 것임
CREATE INDEX Test_Index ON Test(LastName)
WITH (FILLFACTOR = 1, PAD_INDEX = ON)
--인덱스가 만들어진 sys.indexes 에서 index_id와 name 을 출력하는데 조건이
--Test 에 만든 인덱스 id 와 같은것만 출력한다
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('Test');
결과 화면은 이런데 여기서 인덱스 Test_Index 를 조사해보면
--2번 인덱스 정보 살펴보기
DBCC IND('Northwind','Test', 2);
빨간색 박스의 IndexLevel 이
가장 높은 숫자가 트리의 Root 이고
2 -> 1 -> 0
순으로 자식을 이루고 있는 인덱스 트리를 생각 하면 된다
2 가 가장 상단 그다음에 1에 해당 하는 것이 PagePID 보면 1008, 984 두개가 있는데
이 중에서 NextPagePID 를 1 레벨에서 다음을 말하는 것이 984 라고 나와 있는 것
즉 순서가 985 -> 1008 -> 984 로 되어 있다는 것을 알 수 있다
트리 형태로 보면 가장 상단 레벨 1에 1개 2 레벨에 2개 3 레벨에 3개의 노드가 있다고 생각하면 된다
그런데 각 노드는 페이지들인데 이 정보들 또한 볼 수 있다
끝단 3개의 노드를 출력해보면
--1 파일번호, 968 페이지 번호, 3 출력 옵션
DBCC PAGE('Northwind',1, 968, 3);
DBCC PAGE('Northwind',1, 976, 3);
DBCC PAGE('Northwind',1, 977, 3);
위 처럼 되고
원래 추가된 9개 와 비교해보면 3개씩 나뉘어져 들어가 있는 것을 알 수 있다
HEAP RID 라는 걸 볼 수 있는데
HEAP RID = 페이지주소(4바이트) , 파일 ID(2), 슬롯번호(2), 로 조합된 8바이트 ROW 식별자이고 이걸로
테이블에서 정보를 추출한다
TABLE [{PAGE} {PAGE}{PAGE} ....] 여기서 찾는 형태이다
RANDOM ACCESS (한건을 읽기 위해 한 페이지씩 접근 하는것 으로 트리 위에서 부터 아래로 노드를 찾아가는 과정)
BOOKMARK LOOKUP : 노드를 찾은다음 RID를 통해 행을 실제 TABLE 에서 찾는 것을 말함
파티션의 경우 playerID로 Group by 와 유사하게 할수 있는데 다른 점은 Group by 는 하나로 묶어 버리지만 Partition 의 경우에는 각 행별로 모두 보이게 된다
결과
PlayerID 별로 랭킹이 매겨져 있는 걸 볼 수가 있다
LAG(바로 이전 : 나열된것의 바로 위) ,LEAD(바로 다음 : 나열된 것을 바로 아래)
결과
playerID 기준으로 salary 값 위아래를 보면 prevSalary 와 nextSalary 값이 있다는 것을 알 수 있다
FIRST_VALUE : 가장 처음 값 : 여금 여기선 큰
LAST_VALUE : 가장 마지막 값 : 지금 여기선 작은 값
결과를 보면 WORST 가 제대로된 작은 값이 아닌데
그 이유는 Desc 로 정렬하여 큰 값은 나오게 되는데 나오는 검색 과정이
동일 아이디의 가장 위에서 부터 하나씩 증가 시키면서 그 중에 큰걸 보게 되는 순서라
4500000
4500000 , 2750000
4500000 , 2750000, 500000
이런순을 검색하면서 그 중 가장 큰걸 뽑아오는게 FIRST_VALUE 라서 best 는 제대로 되지만 worst 는 제대로 되지 않는다는걸 알 수 있다
그래서 LAST_VALUE 의 경우에는 제일 처음부터 증가하는 형태인 키워드 (UNBOUNDED PRECEDING) 를 넣어주고 현재 까지의 진행라인에 대한 현재 CURRENT_ROW 까지 는 BEST 라인이 범위가 되도록 ROWS 를 지정해주면 되고
그다음 LAST_VALUE 에대해선 현재 라인 CURRENT_ROW 부터 동일 아이디에 대해서 마지막 라인을 얘기하는 UNBOUNDED FOLLOWING 을 넣어주면제대로된 WORST 값이 나오게 된다
제대로된 결과
오라클에서 OVER 절에 WINDOWING 절을 처음 사용할 때 ROWS와 RANGE가 어떤 차이점이 있는지 많이 헷갈릴 수 있다. 간단히 설명하면 ROWS는 각 행의 위치고, RANGE는 값의 범위라고 생각하면 된다. 아래의 예제를 여러 번 반복해서 보면 많이 어렵지 않게 이해할 수 있을 것이다.
DECLARE @i AS INT =100;
IF @i = 100
PRINT('100')
ELSE
PRINT('NOT')
여러줄 묶을땐 BEGIN END 로 묶어야 한다
DECLARE @i AS INT =100;
IF @i = 100
BEGIN
PRINT('100')
PRINT('101')
END
ELSE
PRINT('NOT')
WHILE 문 또한 사용가능하고 CONTINUE 와 BREAK 또한 있다
GO
DECLARE @i AS INT =0;
WHILE @i <= 10
BEGIN
SET @i = @i + 1;
IF @i = 6 CONTINUE;
PRINT @i;
END
원래 CREATE TABEL 명령어는 이런 형태였다
EX)
--테이블 만들기
create table accounts(
accountId integer not null,
accountName varchar(10) not null,
coins integer default 0,
createdTime DATETIME
);
그런데 테이블도 변수로 만들 수 있다 create table accounts 과 비슷하지만 다른건 tempDB 라는 곳에 임시로 데이터 베이스가 저장된다
--테이블도 변수로 만들 수 있다
--create table accounts( 과 비슷하지만 다른건 tempDB 라는 곳에 임시로 데이터 베이스가 저장된다
--
GO
DECLARE @test TABLE
(
name VARCHAR(50) NOT NULL,
salary INT NOT NULL
);
INSERT INTO @test
SELECT p.nameFirst + ' ' + p.nameLast, s.salary
FROM players AS p
INNER JOIN salaries AS s
ON p.playerID = s.playerID;
SELECT *
FROM @test;
--변수 선언 , 생성함과 초기화 할수도있고
DECLARE @i as INT = 10;
--생성만 한다음, 나중에 넣을 수도 있다
DECLARE @j as INT;
SET @j = 20;
SELECT @i, @j;
---배치---
--이전에 썼던 변수는 없는걸로 치고 다시 변수를 선언할수 있는 명령어 go
GO
DECLARE @i AS INT =100;
SELECT @i;
A의 골드 감소 이렇게 모두 처리되야 하는데 중간에 실패가 난다면 완전히 거래가 처리 되는것이 아니고 오류가 되어버리는데 이렇게 되면 테이블 데이터가 잘못되게 된다, 강화 하는 경우에도 비슷한 케이스 All or Nothing 이런걸 해결 하기위해 TRANSACTION 이 있다 아무것도 쓰지 않으면 기본 적으로 TRANSACTION 이 있고 그 뒤에 COMMIT 이 있는 것인데 (EX : INSERT INTO ...) BEGIN TRAN; 을 명시하면 뒤에 COMMIT 또는 ROLLBACK 을 적어 처리할지 되돌릴지를 정할 수 있다
INSERT INTO accounts VALUES(1, 'TESET STR', 100, GETUTCDATE());
BEGIN TRAN;
INSERT INTO accounts VALUES(2, 'TESET STR', 100, GETUTCDATE());
ROLLBACK;
결과를 보면 두번째 것은 추가 안된것을 알수 있다 ROLLBACK 되었음으로
INSERT INTO accounts VALUES(1, 'TESET STR', 100, GETUTCDATE());
BEGIN TRAN;
INSERT INTO accounts VALUES(2, 'TESET STR', 100, GETUTCDATE());
COMMIT;
추가된 경우
아래 구문을 실행하기 전 데이터
--TRY CATCH 와 비슷한 구문
BEGIN TRY
BEGIN TRAN;
INSERT INTO accounts VALUES(1, 'T1', 100, GETUTCDATE());
INSERT INTO accounts VALUES(2, 'T2', 100, GETUTCDATE());
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
END CATCH
@@TRANCOUNT 는 TRAN 이 몇개 인지 알수 있는 매크로인데
BEGIN TRAN;
TRAN;
이렇게 TRAN 을 중첩시키면 1이상이 될 수가 있는데 이때 이 개수를 리턴해주는게 @@TRANCOUNT 이다
그리고 INSERT 는 accountId 가 primary key 임으로 이미 존재하는 키가 있을 경우 또다시 추가 하려고 하면 에러가 발생되어 catch 로 잡히게 된다
실행후 상황
아무 영향이 없었다는 걸 알 수 있다
PRINT('TTT') 를 ROLLBACK 구문 쪽에 써서 ROLLBACK 된 이유를 적어줄 수도 있다
중요한건 TRAN 으로 묶어놓은건 한번에 다 실행하거나 아니면 중간에 오류/예외가 발생하면 모두 실행되지 않는 다는 것이다
그리고 TRANSACTION 은 보통 두개 이상의 테이블에 어떤 변경이나 영향을 줄때 사용된다
사용시 주의 할점은
TRAN 안에는 꼭 원자적으로 실행될 애들만 넣어야 한다
즉 성능적으로 문제가 될 수 있기 때문인데
LOCK이 되기 때문
만약
BEGIN TRAN;
INSERT INTO accounts VALUES(2, 'TESET STR', 100, GETUTCDATE());
이렇게 까지 되어 있으면 위코드는 COMMIT 이나 ROLLBACK 을 만나기 전까지 계속 LOCK 리 걸린 상태가 되며
다른 구문에서 accounts 를 조회하려는 구문을 실행한다 해도 select 구문은 실행 되지 않고 계속 대기하게 된다
대기상태에 빠지게 됨 commit 이나 rollback 을 만나지 않는다면 원자적 특성때문에
같은 얘기지만 TRAN 과 COMMIT/ROLLBACK 사이의 구문은 길지 않게 작성하는 것이 좋다
CROSS JOIN (교차 결합) 서로 교차를 하면서 하나씩 결합을 한다는 것 (1,A), (1,B), (1,C), (2,A)... 총 9개
CREATE TABLE testA
(
a INTEGER
)
CREATE TABLE testB
(
B VARCHAR(10)
)
INSERT INTO testA VALUES(1);
INSERT INTO testA VALUES(2);
INSERT INTO testA VALUES(3);
INSERT INTO testB VALUES('A');
INSERT INTO testB VALUES('B');
INSERT INTO testB VALUES('C');
SELECT *
FROM testA;
SELECT *
FROM testB;
--CROSS JOIN (교차 결합)
--서로 교차를 하면서 하나씩 결합을 한다는 것
--(1,A), (1,B), (1,C), (2,A)... 총 9개
SELECT *
FROM testA
CROSS JOIN testB;
SELECT *
FROM testA, testB;
결과화면
기본 데이터 보기
USE BaseballData;
SELECT *
FROM players
ORDER BY playerID;
SELECT *
FROM salaries
ORDER BY playerID;
INNER JOIN(두개의 테이블을 가로로 결합 + 결합 기준을 ON 으로 한다) UNION 은 세로 즉 위아래로 합치는 것이였다면 INNER JOIN 은 옆으로 합치는 것
SELECT *
FROM players AS P
INNER JOIN salaries AS S
ON P.playerID = S.playerID;
players 의 playerID 와 salaries 의 playerID 가 같은 행 끼리 합치는 것
주의 할점은 조건이 = 인데 양쪽 모두에 playerID 가 있어야 붙여지게 되지 한쪽이라도 id 값이 없다면 해당 행은 걸러진다
즉 양쪽에 모두 정보가 있을때만 나온다
결과를 보면 뒤에 추가 된걸 볼 수 있다
inner join 을 한다는 건 새로운 테이블을 만든 것
-- outer join (외부 결합) --어느 한쪽에만 존재하는 데이터가 있을때 정책을 어떻게 할것인지에 대한 것 -- left join 인경우로 예를 들어보면 두개를 조인 할때 왼쪽에만 있고 오른쪽에는 없다면 --왼쪽 정보를 그대로 채워 넣고 없는 오른쪽 정보는 null 로 채워 넣어서 join 을 한다는 것으로 --inner join 과 유사한데 비어 있는 것을 어떻게 처리 할것인가에 대한 내용이다
SELECT *
FROM players AS P
LEFT JOIN salaries AS S
ON P.playerID = S.playerID
ORDER BY P.playerID;
아래는 playerID 로 정렬하여 두 테이블(players과 salaries )을 본것이고
LEFT JOIN 한다음의 모습이다
adairbi99m 의 줄에서 끝을 보면
salaries 에는 adairbi99m 이 없기 때문에 끝에 NULL 로 채워진것을 볼 수 있다
RIGHT JOIN 은 반대의 개념이 된다
정보가 오른쪽에 있다면 표시되고 그 이후에 같은 행에 왼쪽(plyaer)에 없으면 왼쪽 정보는 NULL 로 채워진다
예시 이미지
오른쪽에 정보는 있지만
왼쪽이 null 로 채워진 경우
그런데 left 나 right 나 테이블 순서를 바꿔주면 동일한 효과가 된다
정리하자면
cross join 은 * 이고
inner join, left join right 조인은 같은 행에 추가 하여 테이블을 만드는 것이다
select playerID, AVG(salary)
from salaries
group by playerID
having AVG(salary) >= 3000000
UNION
--12월에 태어난 선수들의 playerID
select playerID, birthMonth
from players
where birthMonth = 12
order by playerID asc
;
아래는 위 결과를 실행했을때의 결과인데 avg 와 birthmonth 때문에 합쳐지지 않아
silvaca01 이 별도 있는 거을 볼 수 있다
union 열을 같게끔 해주면 중복은제거된다
--커리어 평균 연봉이 3000000 이상인 선수들의 playerID
select playerID
from salaries
group by playerID
having AVG(salary) >= 300000
UNION
--12월에 태어난 선수들의 playerID
select playerID
from players
where birthMonth = 12
order by playerID asc
;
이렇게 하면 합쳐진 하나만 나오는 것을 알수 있다 ( 중복 제거 )
union all 은 중복을 허용한다
select playerID
from salaries
group by playerID
having AVG(salary) >= 300000
UNION all
--12월에 태어난 선수들의 playerID
select playerID
from players
where birthMonth = 12
order by playerID asc
;
union 을 쓰게 되면 order by 는 가장 하단에 와야 한다
교집합(intersect) 을 구한다 즉 양쪽 모두 만족하고 존재하는것을 구한다
--교집합(intersect) 을 구한다 즉 양쪽 모두 만족하고 존재하는것을 구한다
--커리어 평균 연봉이 3000000 이상이거나 (&&) 12월에 태어난 선수들
select playerID
from salaries
group by playerID
having AVG(salary) >= 300000
intersect
--12월에 태어난 선수들의 playerID
select playerID
from players
where birthMonth = 12
order by playerID asc
;
--차집합
--커리어 평균 연봉이 3000000 이상이거나 (-) 12월에 태어난 선수들
select playerID
from salaries
group by playerID
having AVG(salary) >= 300000
except
--12월에 태어난 선수들의 playerID
select playerID
from players
where birthMonth = 12
order by playerID asc
;
use BaseballData;
select birthMonth
from players;
select *,
case birthMonth
when 1 then N'겨울'
when 2 then N'봄'
when 3 then N'가을'
when 8 then N'8이다'
else N'그밖에'
end as birthSeason
from players;
switch case 와 비슷한걸 알 수 있다
위에서 end as birthSeason 끝에 새로 추가된 컬럼의 이름을 birthSeason 으로 지정하겠다는 얘기다
case 의 where 조건에 맞춰 문자로 변환되어 추가 된것을 볼 수 있다
아래 처럼 조건문을 추가 하는 구문 또한 있다
select *,
case
when birthMonth <=1 then N'back'
when birthMonth <=3 then N'나이스'
when birthMonth <=6 then N'앜'
when birthMonth <=9 then N'9이하'
when birthMonth <=12 then N'12이하'
else N'그밖에'
end as birthSeason
from players;
위 구문들에서 else 구문이 없다면 else 에 에 해당 하는것ㅇ느 birthSeason 에서 NULL 이 된다
주의 할점 birthMonth = NULL 이렇게 조건문을 쓸 수 없고 birthMont is NULL 이렇게 비교를 해야한다
USE [BaseballData]
GO
INSERT INTO [dbo].[DateTimeTest]
([time])
VALUES
--('20090909')
(CURRENT_TIMESTAMP)
GO
use BaseballData;
SELECT *
FROM DateTimeTest;
-- 은 주석이다
SELECT *
FROM DateTimeTest;
몇번 추가한 모습
조건식으로 비교할때 다음 처럼 할 수 있다 두개의 결과는 같다
use BaseballData;
SELECT *
FROM DateTimeTest
where time >= CAST('20200101' as DATETIME);
SELECT *
FROM DateTimeTest
where time >= '20200101';
