ORDER BY JOIN DESC

 

ORDER BY절부터 조인까지 내림차순으로 설명하는 글.

 

ORDER BY절 어디까지 알고 있었나? 단순히 최종 결과 집합에서 특정 칼럼을 기준으로 올림차순 및 내림차순 하는 기능 아니여?라고 나도 생각했지만, 겸손하기로 했다.

 

ORDER BY절

 

 - SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼 기준으로 정렬하여 출력하는 데 사용한다.

 (COLUMN 명 대신 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서로 나타내는 정수로도 사용 가능하다.)

 

정수로도 사용이 가능하다는 말을 예제로 한 번에 설명하겠다.

SELECT p.PLAYER_NAME, p.BIRTH_DATE , p."POSITION" FROM PLAYER p
ORDER BY 2 desc;

SELECT절에 명시한 플레이어의 생일을 기준으로 내림차순으로 정렬하겠다는 말인데, 2라는 정수로 간단히 표현했다. 하지만 가독성은 매우 떨어지니 사용하지 않고 ALIAS명이나 칼럼을 직접 명시하는 것이 좋다. 근데 왜 NULL값이 나왔는지 궁금하신 분들이 있다면, ORACLE에서는 NULL값을 가장 큰 값으로 취급한다. 데이터베이스마다 NULL의 크기를 취급하는 게 다르기 때문에 벤더별 확인을 하고 사용하는 것이 좋다.

 

- 기본값은 ASC(오름차순)이고 DESC(내림차순)은 별도로 표시를 해주어야 한다. SQL 문장의 제일 마지막에 위치한다.

 

SELECT 문장의 실행 순서를 한번 알아보자 

 

FROM WHERE GROUP BY HAVING SELECT ORDER BY

다음과 같은 순서로 이루어지는데 이 실행 순서는 옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서이기도 하다.

 

- 이 ORDER BY절에는 SELECT 목록에 나타나지 않는 문자형 항목이 포함될 수 있다. 단 SELECT DISTICT를 지정하거나, SQL 문장에 GROUP BY절이 있거나, UNION 연산자가 있으면 열정 의가 SELECT 목록에 표시되어야 한다.

 

★ 왜 SELECT목록에 칼럼을 지정하지 않은 칼럼을 기준으로 정렬할 수 있을 까?

 

그건 관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져오게 되므로, SELECT절에서 일부 칼럼만 선택하더라도 ORDER BY절에서 메모리에 올라와 있는 다른 컬럼 데이터를 사용할 수 있다.

 

★ 왜 GROUP BY절이 있거나 UNION연산자가 있으면 열정 의가 SELECT 목록에 표시되어 있어야 할까?

 

GROUP BY절에서 그룹핑 기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블 구조를 그대로 가지고 가는 것이 아니라 GROUP BY절의 그룹핑 기준에 사용된 칼럼과 집계 함수에 사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만들기 때문이다. 

UNION도 DISTINC SELECT도 생각해보면 답이 나올 것이다.

 

결과적으로 GROUP BY 절을 사용한 SELECT절에서의 ORDER BY절에는 그룹핑 기준과 숫자 형식 칼럼의 집계 함수를 사용할 수 있지만, 그룹핑 기준 외의 문자 형식 칼럼을 사용할 수 없다는 것이다.

 

TOP N 쿼리

 

-ROWNUM

 ORACLE에서 순위가 높은 N개의 로우를 추출하기 위해 ORDER BY절과 ROWNUM 조건을 같이 사용하는 경우가 있는데 이 두 조건으로는 원하는 결과를 얻을 수 없다.

 

ORACLE은 정렬이 완료된 후 데이터의 일부에 ROWNUM번호가 부여되어 추출되는 것이 아니고,

데이터 일부가 먼저 추출된 후 데이터에 대한 정렬 작업이 일어난다.  (ORDER BY 절은 결과 집합을 결정하는데 기여하지 않는다.)

 

즉 ORACLE은 ROWNEUM 조건을 ORDER BY절보다 먼저 처리하는 WHERE절에서 처리하기 때문에 , 정렬 후 원하는 데이터를 얻기 위해선 인라인 뷰에서 데이터를 수행한 후 메인 쿼리에서 ROWNUM 조건을 사용해야 한다.

 

JOIN

 

join은 관계형 데이터베이스에서 가장 중요한 개념이며 핵심 요소이다.  JOIN은 두 개 이상의 테이블들을 연결 또는 결합하여서 관계있는 데이터들을 출력하기 위해 사용한다.

대부분의 SQL 문장의 상당 수가 JOIN이다.

다만 한 가지 주의할 점은 FROM절에 여러 테이블이 나열되어도 SQL에서 데이터를 처리할 때는 단 두 개의 집합 간에만 조인이 일어난다.

 

예를 들면 FROM A, B, C, D를 조인한다고 했을 때 AD/B, C , ADB/C , ADBC 이렇게 3번의 연산이 이루어진다.

테이블의 조인 순서는 옵티마이저에 의해서 결정되고 성능 개선 시 주요 튜닝 포인트 이기도하다.

 

 

EQUI JOIN

 

- 등가 조인은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 대부분 PK와 FK의 관계를 기반으로 한다. 

오라클은 WHERE 절에 ANSI/ISO SQL 표준은 ON절에 사용한다. 사실 WHERE 절은 예전의 문법이며 조건절과 조인 절이 헷갈리기 때문에 FROM절과 ON 절을 통한 조인을 많이 사용한다.

 

최소한의 연관관계를 위해서 테이블 개수 -1개의 JOIN조건을 FROM/WHERE절에 명시하고 부수적인 제한 조건을 논리 연산자를 통하여 추가로 입력할 수 있다.

 

 

NON EQUI JOIN

 

비등가 조인은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용한다.

즉 "="연산자가 아닌 (<,>, BETWEEN a And B)와 같은 연산자를 사용하여 JOIN을 수행하는 것이다.

 

 

 

위에서 이야기했던  FROM절 JOIN형태에 대해서 알아보자.

 

ANSI/ISO SQL 표준에서 표시하는 FROM절의 JOIN 형태는 다음과 같다.

전부 FROM 절에서 사용할 수 있는 JOIN형태이다.

 

INNER JOIN - NATURE JOIN - USING (조건절) - ON 조건절 - CROSS JOIN - OUTERJOIN

 

사용자는 기존 WHERE절의 검색조건과 테이블 간의 JOIN조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서 추가된 선택 기능으로 테이블 간의 JOIN조건을 FROM절에서 명시적으로 정의할 수 있게 되었다.(하나만 하자)

 

각각 하나씩 알아보기 전 이러한 JOIN형태들이 왜? 어떻게 나왔는지 잠깐 알아보자.

 

현재 사용하는 SQL에 많은 기능이 관계형 데이터베이스의 이론을 수립한 E.F.Codd 박사의 논문에 언급이 되어 있다.

 

8가지 관계형 대수는 각각 4개의 일반 집합 연산자와 순수 관계 연산자로 나눌 수 있으며 데이터베이스 엔진 및 SQL의 기반 이론이 되었다.

 

1. 일반 집합 연산자

 

 

1) Union 연산은 수학적 합집합을 제공하기 위해, 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템에 부하를 주는 정렬 작업이 발생한다. 이후 UNION ALL 기능이 추가되었고, 공통집합을 중복해서 그대로 출력하는 UNION ALL은 정렬이 일어나지 않아 공통집합이 발생하지 않거나 발생해도 문제가 없을 때는 UNION ALL을 권장한다.

 

Union연산은 UNION/ UNION ALL 기능으로 추가되었다.

 

2) Intersection은 수학의 교집합으로써 두 집합의 공통집합을 출력한다.

 

Intersection연산은 intersect기능으로 추가되었다.

 

3) Difference는 수학의 차집합으로써 순서가 중요하며 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외한 부분이다.

 

Difference연산은 EXCEPT , MINUS(ORACLE) 기능으로 추가되었다.

 

4) Product의 경우는 CROSS PRODUCT라고 불리는 곱집합으로 JOIN조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 양쪽 집합의 M * N 간의 데이터 조합이 발생하며, 카티션 곱 (Cartesian product)이라고 표현하기도 한다.

 

Product연산은 CROSS JOIN기능으로 추가되었다.

 

 

2. 순수 관계 연산자

 

관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자이다.

 

1) Select연산은 WHERE 절로 구현되었다. 

 

2) Project연산은 SELECT절의 칼럼을 선택 가능하도록 구현하였다.

 

3) (Natural) JOIN연산은 다양한 JOIN기능으로 구현하였다.

JOIN연산을 WHERE절의 INNER JOIN 조건과 함께 FROM절의 NATURAL JOIN, INNER JOIN, OUTER JOINM USING조건절, ON 조건절 등으로 가장 다양하게 발전했다.

 

4) Divide연산은 현재 사용되지는 않지만 정의는 다음과 같다 X ⊃ Y 두 개의 릴레이션 R(x)와 S(y)가 있을 때 R의 속성이 S의 속성 값을 모두 가진 튜플(레코드)에서 S가 가진 속성을 제외한 속성만 구하는 연산이다.

 

 

 

다양한 JOIN 연산

 

NATURAL JOIN

 

 - 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI 조인(등가 조인)을 수행한다.

NATURAL JOIN이 명시되면 추가로 Using 조건절, on, where에서 JOIN 조건을 정의할 수 없다.

또한 JOIN에서 사용된 칼럼들은 같은 데이터 유형이 여야 하며 ALIAS나 테이블 명과 같은 접두사를 붙일 수 없다.

SELECT * FROM PLAYER p NATURAL JOIN TEAM t ;

여기서 확인할 수 있는 것은 SELECT 칼럼을 지정안할 시 자동적으로 등가조인 컬럼이 맨앞으로 나온다. 또한 INNER JOIN과 다르게 등가조인 대상 컬럼을 하나로 합쳐서 취급한다.

 

 

USING 조건절

 

 - NATURAL JOIN에서는 모든 일치되는 칼럼에 대해 JOIN이 이루어지지만 FROM절 USING 조건절을 이용하면 같은 이름을 가진 칼럼들에 대해 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.

SELECT * FROM PLAYER p JOIN TEAM t USING (TEAM_ID);

 

 

OUTER JOIN

 

 - JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용한다. LEFT/RIGHT OUTER JOIN인 경우 기준이 되는 테이블이 조인 수행 시 무조건 드라이빙 테이블이 된다. 옵티마이저는 이 원칙에 위배되는 다른 실행 계획은 고려하지 않는다.

OUTER는 생략이 가능하다.

LEFT와 RIGHT JOIN은 비슷하기 때문에 LEFT OUTER JOIN만 기술한다.

 

•  LEFT OUTER JOIN
  • 조인 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후(중요) 나중에 표기된 우측 테이블에서 JOIN대상 데이터를 읽어온다. 즉 좌측이 기준이 되며 우측 조인 칼럼에서 같은 값이 있을 때 해당 데이터를 가져오고 같은 값이 없을 땐 우측 테이블에서 가져오는 칼럼들은 NULL값으로 채운다.

 

예를 들어 직원과 부서 데이터를 출력하는데 직원이 없는 부서도 출력할 때 다음과 같이 사용할 수 있다.

 

SELECT * FROM DEPT d LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO ORDER BY e.DEPTNO DESC;

 

ON 조건절

 

 - 이름이나 다른 칼럼명을 JOIN조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서 ON 조건절을 사용한다. 

ALIAS나 테이블명과 같은 접두사를 사용해 SELECT에 사용되는 칼럼들을 명시적으로 지정해주어야 한다.

현재 가장 많이 사용되며 칼럼을 직접적으로 명시하여 이해도가 빨라짐

 * ON 조건절 + 데이터 검증 조건 : OUTER JOIN에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건은 ON절에 표기한다.