Database 5. SQL로 데이터 조회 및 관련 키워드 학습

• MySQL 문법을 기준으로 서술한다.

Select Statement

SELECT name, position FROM employee WHERE id = 9;

SELECT employee.id, employee.name, position FROM project, employee WHERE project.id = 2002 and project.leader_id = employee.id;

As 사용하기

• AS는 테이블이나 attribute에 별칭(alias)을 붙일 때 사용한다.
• AS는 생략 가능함.

SELECT E.id AS leader_id, E.name AS leader_name, position FROM project AS P, employee AS E WHERE P.id = 2002 and P.leader_id = E.id;

DISTINCT 사용하기

SELECT DISTINCT P.id, P.name FROM employee AS E, works_on AS W, project AS P WHERE E.position = 'DSGN' and E.id = W.empl_id and W.proj_id = P.id;

• DISTINCT는 select 결과에서 중복되는 tuples은 제외하고 싶을 때 사용한다.

LIKE 사용하기

SELECT name FROM employee WHERE name LIKE 'N%' or name LIKE '%N'; -- 이름이 N으로 시작하거나 N으로 끝나는 임직원들 조회

SELECT name FROM employee WHERE name LIKE '%NG%'; -- 이름에 NG가 포함되는 임직원들 조회

SELECT name FROM employee WHERE name LIKE 'J___'; -- 이름이 J로 시작하고 4글자인 임직원들 조회

• escape 문자와 사용할 때는 ‘\‘를 앞에 추가해줌으로써 표현 가능하다.

*(asterisk) 사용하기

• 선택된 tuples의 모든 attribute를 보여주고 싶을 때 사용한다.

Select without WHERE

• 테이블에 있는 모든 tuples를 반환한다.

주의 사항

1. SELECT로 조회할 때 조건들을 포함해서 조회한다면 이 조건들과 관련된 attributes에 index가 걸려있어야 한다. 그렇지 않다면 데이터가 많아질수록 조회 속도가 느려진다.

Select with subquery

SELECT id, name, birth_date FROM employee WHERE birth_date < (
    SELECT birth_date FROM employee WHERE id = 14
  );

SELECT id, name, position FROM employee WHERE (dept_id, sex) = (
    SELECT  dept_id, sex FROM employee WHERE id = 1
  );

SELECT DISTINCT empl_id FROM works_on WHERE empl_id != 5 AND proj_id IN (
    SELECT proj_id FROM works_on WHERE empl_id = 5
  );

• subquery(nested query or inner query) : SELECT, INSERT, UPDATE, DELETE에 포함된 query

• outer query(main query) : subquery를 포함하는 query

  • subquery는 괄호 안에 기술된다.
• IN(v1, v2, v3, ...) : v가 괄호 안의 요소 들 중에 하나와 값이 같다면 TRUE를 반환한다.
  • (v1, v2, v3, ...)는 명시적인 값들의 집합일 수도 있고 subquery의 결과(set or multi-set)일 수도 있다.

• NOT IN(v1, v2, v3, ...) : v가 괄호의 모든 값과 값이 다르다면 TRUE를 반환한다.

• unqualified attribute가 참조하는 table은 해당 attribute가 사용된 query를 포함하여 그 query의 바깥쪽으로 존재하는 모든 queries 중에 해당 attribute 이름을 가지는 가장 가까이에 있는 table을 참조한다.
  • 쉽게 말해 attribute의 테이블이 명시되어 있지 않다면 가장 가까운 table의 값이라고 생각한다.

SELECT id, name FROM employee,
  (
    SELECT DISTINCT empl_id FROM works_on WHERE empl_id != 5 AND proj_id IN (
      SELECT proj_id FROM works_on WHERE empl_id = 5
    )
  ) AS DSTNCT_E
WHERE id = DSTNCT_E.empl_id;

SELECT * P.id, P.name FROM project P WHERE EXISTS (
    SELECT * FROM works_on W WHERE W.proj_id = P.id W.empl_id IN (7, 12)
  ); -- ID가 7혹은 12인 임직원이 참여한 프로젝트의 ID와 이름을 알고 싶을 때

• correlated query : subquery가 바깥쪽 query의 attribute를 참조할 때, correlated subquery라고 부름
• EXISTS : subquery의 결과가 최초 하나의 row라도 있다면 TRUE를 반환한다.
• NOT EXISTS : subquery의 결과가 단 하나의 row도 없다면 TRUE를 반환한다.

SELECT E.id, E.name, E.salary FROM department D, employee E WHERE D.leader_id = E.id AND E.salary < ANY(
    SELECT salary FROM employee WHERE id <> D.leader_id AND dept_id = E.dept_id
  );

• v comparison_operation ANY(subquery) : subquery가 반환한 결과들 중에 단 하나라도 v와의 비교 연산이 TRUE라면 TRUE를 반환한다.
• SOME 또한 ANY와 같은 역할을 한다.
• v comparison_operator ALL(subquery) : subquery가 반환한 결과들과 v와의 비교 연산이 모두 TRUE라면 TRUE를 반환한다.

성능 비교 : IN vs EXISTS

• RDBMS의 종류와 버전에 따라 다르며, 최근 버전은 많은 개선을 통해 성능 차이가 거의 없다.

NULL 관련 지식

NULL과 Three-Valued Logic

• SQL에서 NULL과 비교 연산을 하게 되면 그 결과는 UNKNOWN이다.
• UNKNOWN은 ‘TRUE일 수도 있고 FALSE일 수도 있다’라는 의미이다.
• three-valued logic : 비교/논리 연산의 결과로 TRUE, FALSE, UNKNOWN을 가진다.

비교 연산 예제	결과
1=1	TRUE
1!=1	FALSE
1=NULL	UNKNOWN
1!=NULL	UNKNOWN
1 > NULL	UNKNOWN
1<= NULL	UNKNOWN
NULL = NULL	UNKNOWN

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

• WHERE 절에 있는 condition(s)의 결과가 TRUE인 tuple(s)만 선택 된다.
  • 즉, 결과가 FALSE거나 UNKNOWN이면 TUPLE은 선택되지 않는다

NOT IN 사용 시 주의 사항

NOT IN 예제	결과
3 not in (1, 2, 4)	TRUE
3 not in (1, 2, 3)	FALSE
3 not in (1, 3, NULL)	FALSE
3 not in (1, 2, NULL)	UNKNOWN