인공지능 부트캠프 24기 _ 5일차 (ANSI JOIN, Subquery, DML)

※ ANSI JOIN ※

"ANSI JOIN"은 Oracle JOIN과 달리 모든 DBMS에 사용이 가능한 JOIN문법이다.

 

추가적으로, Oracle JOIN은 Where절에 join문법을 작성한 것과 달리 ANSI join은 FROM 절에 JOIN 문법을 작성하게 된다.

 

이에따라 FROM절과 Where절의 사용용도가 명확히 구별되고 Oracle join보다 더 범용적으로 사용되는 JOIN 문법이다.

 

1) Natural JOIN

Natural join은 Oracle join 에서의 inner join과 동일한 역할을 한다.

Natural join 문법

Equi join과의 차이점은 Where절이 아닌 From절에 join 문법을 입력한다는점, 그리고 join할 column을 따로 입력하지 않는다.

즉, 두 테이블 간의 공통명의 column이 존재하여야 하며 이렇지 않을경우에는 사용이 불가능하다.

또한, 공통 column을 표현할때 앞의 table명을 붙이지 않아야한다. (Oracle join의 경우 반드시 붙임)

Natural join 사용 예시

 

2) Cross JOIN

Oracle join 에서 Cartesian product와 동일한 역할을 한다.

다만, Oracle join에서는 table수에 비해 join의 갯수가 적은경우 나온다면 ANSI join의 경우 Cross join을 명시하여 확인한다.

하지만, 쓰레기 데이터임은 동일하므로 쓸일은 없다.

Cross join 사용 예시

 

3) Using 절

Natural join과 동일하게 equi join의 역할을 하지만 만일 공통컬림이 2개 이상이라면 Natural Join을 사용하지 못하기에 Using절을 사용해주어야 한다.

 

Using 절은 Using 뒤에 Join할 Column명을 정확히 명시하기 때문에 Natural Join보다 범용적이고 가독성 있게 사용될 수 있다.

 

Using 절 기본 문법

Using절 사용 예시

4) ON 절

Using절보다 더 범용성 있는 역할을 하는 ON절이다. Using 절에 non-equi 절의 역할까지 추가하여 사용할 수 있다.

 

On절 뒤에 Using절과 달리 공통 컬럼이 아닌 조인 조건을 명시한다. 조인 조건 안에는 동등, 부등 등의 논리 연산자들을 다 기재할 수 있다.

ON절 기본 문법

ON절 사용 예시(동등 조건)

ON절 활용 예시(부등 조건)

On 절 활용 예시 (self join)

<ANSI - inner join 정리>

5) ANSI Outer join

ANSI Outer join도 역시 Oracle outer join과 크게 개념이 다르지 않으나, from 절에 join문법을 입력한다는점 그리고 양쪽의 누락된 data를 모두 볼 수 있다는 점의 차이가 있다.

 

ANSI Outer JOIN 문법

Left OUTER JOIN 의 경우 왼쪽 테이블 누락된 데이터 보존, Right OUTER JOIN의 경우 우른쪽 테이블 보존 그리고 FULL OUTER JOIN의 경우 양쪽 테이블을 보존하여 JOIN하게 된다.

ANSI OUTER JOIN 사용 예시 (LEFT OUTHER JOIN)

※ Subquery ※

Subquery 역시 분산된 데이터를 취합하기 위해 사용되는 문법이나 JOIN과 다른 목적으로 사용되게 된다.

JOIN vs SUBQUERY

모든 Subquery문은 ()안에 새로운 select문으로 작성되어진다. (괄호가 없는 select문은 main query라고 칭한다.)

 

Subquery는 보통 Where 절에 사용되고 그 외에도 Column이 들어갈 수있는 자리에는 사용될수 있다.

 

Subquery의 반환값으로는 단일 record와 복수 records가 나올 수 있는데 이에 따라 사용되는 연산자 혹은 Main query의 구성요소가 달라지니 주의하여야 한다.

단일행 subquery vs 복수행 subquery

 

Subquery Where 절 사용 문법

 

1) 단일행 subquery

말그대로 subquery의 결과값으로 단일 record를 반환하는 것이다. 이를 취급하기 위해 앞의 연산자는 무조건 비교연산자를 사용하여야 한다. 이는 !! subquery에서 가장 error가 많이 발생하는 부분이므로 주의가 필요하다.

 

단일행 subquery 사용 예시

 

2) 복수행 subquery

단일행과 달리 subquery에서 복수의 record 결과값을 반환한다.

그러기에 단일행 subquery와 다르게 비교연산자가 아닌 IN,ANY,ALL,EXIST 연산자를 사용한다.

복수행 subquery에 사용할 수 있는 연산자 종류

2_1) IN 연산자

지난 Where 절에서 배운 IN연산자와 동일하다. IN () 안의 내용중 하나라도 일치하는 내용을 포함한다.

복수행 Subquery IN 연산자 사용 예시

2_2) ANY, ALL 연산자

둘다 항상 부등연산자(<,>)와 함께 쓰이며 ANY의 경우 ()안의 요소중 하나라도 만족하는 경우, ALL의 경우는 모두 만족하는 경우에만 성립한다.

이 특징으로 아래와 같이 정리하여 사용할수 있따.

> all () : ()안의 최대보다 큰값

< all () : ()안의 최소보다 작은값

>any () : ()안의 최소보다 큰값

< any () : ()안의 최대보다 작은값

복수행 subquery ALL연산자 사용 예시

2_3) EXIST 연산자

위의 연산자는 subquery의 결과에 따라 만족하는 조건의 한해 실행 범위를 결정하는거라면, EXIST연산자는 조금 특이하게 select문의 실행 여부자체를 판단한다.

즉, EXIST()의 subquery문장에 값이 발생하면 mainquery 실행, 발생하지 않으면 실행x.

다시말해서, 스위치 역할을 하게되고 원하지 않는 상황 발생시 문장을 중단시킬 수 있는 역할을 한다.

Subquery Exist 연산자 활용 예시

위는 EMPOLYEE 테이블에 id 가 다 NULL일경우 메인쿼리를 실행하지 않겠다는 문장이다.

 

**** 다중 컬럼 서브 쿼리 ****

SUBQUERY select절에 복수개의 column이 나온경우 당연하게도 대응 하는 컬럼의 갯수도 동일하여야 한다.

이는 2개의 조건이 "동시에!!!" 모두 일치하여야만 조건에 일치하는것으로 판단된다.

 

둘중 하나라도 만족하지 않으면 조건에 포함되지 않는다.

다중 컬럼 Subquery 사용 예시

 

**** 인라인뷰 서브쿼리(From절 subquery) ****

From 절에 subquery를 작성하는 것을 말하며 모든데이터를 탐색하기전에 필요한 데이터를 축약하여 탐색하기 위해 사용되어진다.

이는 탐색해야 하는 데이터의 양을 효과적으로 줄여주어 성능향상과 큰 연관이 있다.

 

객체중 보고자 하는 데이터만을 보는 view의 역할과 비슷해 Inline view subquery라고 칭한다.

In-line subquery 기본 문법

 

일반 JOIN 문법 vs In-line subquery 활용 예시

JOIN 대상의 크기를 미리 줄여줌으로써 성능을 향상 시킬수 있다.

※ Data Manipulation Language (DML) ※

말그대로 테이블 데이터를 조작하는 언어이다.

 

"Select문과 별개"로 Insert, Delete, Update, Merge등이 존재하고 각 문법이 상이하기에 잘 숙지해두는 것이 좋다.

 

모든 DML을 수행할경우 Table을 보여주는것이 아닌 ACTION뒤에 몇개 행이 삭제, 수정, 삽입 되었는지 표기되고 Python, jave에서 데이터를 긁어올 때도 동일하므로 잘 참조하여야 한다.

 

1) Insert 문 - 레코드 생성

대상 테이블에 레코드를 생성하는 역할을 하며 대상 테이블의 갯수 혹은 삽입하는 레코드의 갯수에 따라 사용방법이 조금 상이하다.

 

1-1) 단일 테이블 단일 레코드 생성

단일 테이블 단일 레코드 Insert 기본 문법

 

컬럼의 갯수와 vaule갯수가 동일해야 하고 지정하고자 하는 컬럼에만 값을 저장된다.

지정되지 않은 컬럼은 NULL 값이 저장된다.

 

** Not null 제약조건에 null이 들어가면 당연하게도 error발생

 

칼럼을 생략하게 되면 컬럼 순서대로 값이 저장되기는 하지만 가독성이 좋지 않기때문에 컬럼명을 명시하는 것을 권장한다. (컬럼을 생략한다면 실제 table의 column갯수대로 값을 다 입력해야해서 귀찮기도 하다)

Insert 문 사용 예시

1-2) 단일 테이블 다중 레코드 생성

테이블에 다중레코드를 생성하기 위해서는 다중 레코드를 반환하는 Subquery를 Value대신 입력한다.

단일 테이블 다중 레코드 Insert 문 사용 예시

 

*** CTAS

이러한 Insert문 과 유사하게 기존의 TABLE을 이용하여 새로운 Table을 만드는 CTAS도 존재한다.

CTAS 기본 문법

CTAS 사용 예시

** Table 복사하여도 제약조건은 복사가 안된다! (Notnull 제외)

** 만일 CTAS 에서 subquery where 조건에 무조건 거짓인 조건을 넣는다면 공백의 table을 형성할 수 있다. (Column만 생성)

CTAS 빈 table 생성 예시

1-3) 다중 테이블 다중 레코드 생성

여러개의 테이블에 다중 record를 삽입하는 방법으로 기본적으로 Insert ALL 문장으로 시작하며  When~then절을 사용하여 조건을 추가한다.

다중행 다중테이블 insert 문 기본 문법

When 조건식 then 절을 추가하지 않으면 table의 Subqurey에 해당하는 내용을 각각 table에 맞추어 저장하게 된다.

Insert all (조건식 없을시)

Insert all (조건식 있을시)

 

** Insert first

Insert all 대신 사용할 수 있으며 all과 다르게 첫번쨰 조건에 해당할 경우 두번쨰 조건에 일치하여도 저장하지 않는다.

Insert first 작성 예시

2) Update 문 - 레코드 수정

Insert는 해당 칼럼들에 추가 내용을 추가하는거라면 Coulmn의 내용을 수정하는 기능을 가지고있다.

Update 문 기본 문법

변경하고자 하는 조건은 Where 절에 명시하며 명시하지 않을시 모든 record를 변경하므로 명시하는 것이 권장된다.

Update 문 where 절 포함

Update문 Where 절 미포함

 

** Where절에도 비교연산자와 함께 subquery가 들어갈 수 있다.

Update 문 where 절 subquery 사용 예시

 

3) Delete 문 - 레코드 삭제

명료하게 Delete From을 사용하며 제거하고자 하는 record의 조건은 where절에 명시한다.

Delete 문 기본문법

역시 Where 절을 명시 하지 않으면 모든 레코드를 삭제한다.

 

 

** LOCK 경합

DML사용시 많이 발생하는 error중 하나로 2사용자가 한번에 동일 데이터베이스를 취급한경우 COMMIT 하지 않으면 한 사용자는 TCL을 사용하기 전까지 무한 대기 상태가 된다.

 

팀프로젝트시 많이 발생하는 상황이므로 주의하는 것이 좋다.