4-6강 SQL 활용-고급 SQL 작성(2)

과목4. 데이터베이스 구축, 6강. SQL 활용-고급 SQL작성(2)

 

[ 목차 ]

1. 조인

2. 관계대수에서의 조인

3. 카티션 프로덕트

4. 조인 유형

5. 서브쿼리

6. 서브쿼리의 유형

7. 반환 데이터 형태에 따른 서브쿼리 유형

 

1. 조인

1) 두 개 이상의 테이블로부터 연관된 데이터를 결합해서 검색하는 방법

2) SELECT 문의 FROM 절에 두 개 이상의 테이블을 열거하고 WHERE 절에 조인 조건을 명시

3) 두 테이블 사이에 속하는 칼럼 값들을 비교 연산자로 연결한 형태

4) 조인 조건을 생략한 경우 또는 조인 조건을 잘 못 작성하면 카티션 프로덕트 연산이 수행되어 원치않는 결과 발생

5) 두 테이블의 칼럼 이름이 동일하다면 반드시 칼럼 이름 앞에 테이블 이름을 명시해야 함

5) 하나의 sql 명령문으로 여러 테이블에 저장된 데이터를 한 번에 검색할 수 있는 강력한 기능

6) 잘못 사용하면 오히려 질의 처리 성능을 저하시키는 경우도 발생할 수 있음

7) 두 개 테이블을 연결하는 조인 속성이 반드시 있어야 함

8) 속성명 중복을 해결하기 위해 테이블 이름과 열 사이에 .으로 구분하여 표시

 

2. 관계대수에서의 조인

R(리본모양) 조인속성r = S 조인속성s

1) 두 개의 릴레이션(테이블) A와 B에서 공통된 속성을 연결하는 것

2) 동등 조인 EQUI JOIN : 공통 속성값 중복

3) 자연 조인 NATURAL JOIN : 공통 속성값 제거

 

3. 카티션 프로덕트

1) A에 속한 각 튜플a에 대하여 B에 속한 튜플b를 모두 접속시킨 튜플들로 구성된 릴레이션

 

4. 조인의 종류

1) CROSS JOIN -> 카티션 프로덕트

  1] 조인된 테이블 사이에 조건이 걸리지 않을 경우에 발생

  2] 테이블간의 모든 경우의 수에 대해서 로우가 생성되는 조인 방식

  3] 실제 많이 사용하지는 않지만  SQL 성능 튜닝에 사용하기도 함

  4] ex. SELECT * FROM 부서 CROSS JOIN 사원;

2) INNER JOIN(내부조인) -> JOIN

  1] 일반적으로 의미하는 조인

  2] 조인되는 테리블 간에 서로 연결되는 컬럼이 존재하여 이것에 의해 연결된 로우의 데이터가 출력

  3] 테이블간의 관계에서 참조 무결성 관계를 통해 발생된 칼럼에 의해 조인이 발생

  4] INNER는 생략 가능하며 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인 가능

  5] 동일한 칼럼이 2개 이상일 경우 JOIN ~USING 문장으로 칼럼을 제어

  6] ex. SELECT 부서.부서번호, 사원이름FROM 부서 INNER JOIN 사원 ON 부서.부서번호 = 사원.부서번호;

3) THETA JOIN (세타조인이라는 것이 있고, 등호에 따라 두 종류로 나뉘는 것만 암기)

  1] 다양한 조인 조건을 세타 연산자(비교 연산자)를 이용해 표현할 수 있는 것

  2] > < >= <= <> 등

  3] EQUI JOIN : =가 들어가는 경우

  4] Non-EQUI JOIN : =가 들어가지 않는 다른 연산자만 사용

4) OUTER JOIN 

  1] 조인되는 A테이블에서 B테이블에 연결되는 칼럼값이 존재하지 않더라도 A테이블의 데이터를 가져올 수 있음

  2] 오라클에서는 외부 조인을 위해 + 기호(LEFT일 때 오른쪽)를, MS SQL은 * 기호(LEFT일 때 왼쪽)를 사용

  3] ex. SELECT 부서.부서번호, 부서명.사원번호 FROM 부서 LEFT OUTER JOIN 사원 ON 부서.부서번호=사원.부서번호;

  4] FULL  OUTER JOIN : INNER JOIN + OUTER JOIN

5) SELF JOIN 셀프조인

  1] 자기 자신 테이블을 조인

  2] 테이블이 하나지만 A, B로 2개인 것 처럼 처리

 

5. 서브쿼리

1) 다른 SQL문 안에 포함되어 사용된 또 다른 SQL문

2) WHERE절 뿐만 아니라 INSERT, DELETE, UPDATE문에도 사용 가능

3) ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 서브쿼리에서는 사용 불가

4) 메인 쿼리는 서브쿼리의 칼럼 사용 불가

5) 질의 결과는 메인 쿼리 칼럼이 표시

6) 메인쿼리와 서브쿼리는 주종 관계로, 서브쿼리에 사용되는 칼럼 정보는 메인쿼리의 칼럼 정보를 사용할 수 있으나 반대는 불가

7) 서브쿼리는 단일 행(= 등) 또는 복수 행(IN 등) 비교 연산자와 함께 사용 가능, 즉 서브 쿼리의 경과가 단일 행일 경우 부등호를 사용하나 다중행일 경우 IN을 이용하여 연결하고, 서브쿼리는 ( ) 안에 표현(IN은 항상 가능)

 

6. 서브쿼리의 유형

1) 동작하는 방식과 반환되는 데이터 형태에 따라 분류

2) 비상관(비연관) 서브쿼리 : 서브쿼리가 메인쿼리의 칼럼을 가지고 있지 않은 형태(메인쿼리에 값을 제공)

  ex. SELECT 주소 FROM 수강생 WHERE 이름 IN (SELECT 수강생이름 FROM 수강과목 WHERE 과목 = '정보');

3) 상관(연관) 서브쿼리 : 서브쿼리가 메인 쿼리의 칼럼을 가지고 있는 형태(메인쿼리를 만족하는 행의 수 만큼 여러 번 서브쿼리가 실행)

  ex. SELECT 사번, 부서, (SELECT AVG(급여) FROM 직원 WHERE 직원.부서 = 직.부서) AS 부서평균 FROM 직원 AS 직;

 

7. 반환 데이터 형태에 따른 서브쿼리 유형

1) single row (단일 행)서브쿼리 

  1] 서브쿼리의 결과가 항상 1건 이하

  2] 단일 행 비교 연산자 사용

2) multiple row (다중 행)서브쿼리

  1] 결과가 여러 건인 서브쿼리

  2] 다중 행 비교 연산자(in, all, any, some ,exist)가 사용

  3] in : 해당하는 값이 있으면 참(반대의 경우는 not in). 칼럼의 값이 서브쿼리의 결과 값들의 집합에 속하는지 확인

  4] any : 하나 이상을 만족하면 참. 범위의 개념 = some. 칼럼의 값이 서브쿼리의 결과 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는지 확인

  5] all : 모든 값을 만족하면 참. 칼럼의 값이 서브쿼리의 결과 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는지 확인

  6] exist : 하나라도 있으면 참. 존재여부가 확인되면 검색이 바로 종료되기 때문에 처리가 빠름

3) multiple column (다중 칼럼) 서브쿼리

  1]여러 칼럼으로 반환되는 서브쿼리

  2] 서브쿼리가 두 개 이상의 칼럼을 반환하는 경우 사용