[DB] SQL2: SELECT와 Relational Algebra 중심으로(조회)

SELECT S.sname
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid
WHERE R.bid = 102;

일단 그 전에 용어를 간단히 짚고 넘어가면 아래와 같다. 

데이터 베이스와 스키마는 Database(Schema): Set of named Relations, 즉 여러 table이 있는 공간이다. 
Relation(Table)은 문자 그대로 table 형태, 데이터가 저장될 형태를 말하며 이 때 내부적으로 Schema는 description (“metadata”)을 의미하고 Instance는 set of data satisfying the schema을 뜻한다. 
Attribute(Column, Field)는 각 속성을 의미하고 Tuple(Record, Row)은 행 형태의 데이터를 의미한다

또한 SQL은 multiset을 지원한다

 

CREATE와 TABLE

우선 아래의 예시 Database를 생성해보자. 

 

작성하면 아래와 같이 CREATE 할 수 있을 것이다. 

 

중점적으로 확인 가능한 것은 Primary key와 Foregin key의 구분이다. 즉, Foregin key는 table을 reference하며 이는 다른 테이블의 primary key이다. 

SELECT 기본 문법

위의 SELECT를 기반으로 SELECT FROM WHERE GROUP BY HAVING ORDER BY를 이해해보자.

우선 가장 기본적인 SELECT이다. 

SELECT *
FROM Sailors AS S
WHERE S.age=27;

 

위 SQL은

SELECT * :  모든

FROM Sailors AS s: sailor relation에서

WHERE S.age: age attribute가 27인 걸 가져와라는 말이다 

DISTINCT

SELECT DISTINCT S.name, S.gpa
FROM students S
WHERE S.dept= 'CS'

 

위처럼 DISTINCT 옵션을 SELECT에 붙여주면 duplicate를 허용하지 않는 즉, unique한 것들을 뽑는다.

ORDER BY

order by는 사전 순서대로 결과를 정리해서 보여준다

SELECT S.name, S.gpa, S.age*2 AS a2
FROM Students S
WHERES.dept= 'CS'
ORDER BY S.gpa, S.name, a2;

 

LIMIT

SELECT S.name, S.gpa, S.age*2 AS a2
FROM Students S
WHERES.dept= 'CS'
ORDER BY S.gpa, S.name, a2
LIMIT 3
;

Limit은 반환되는 결과값의 개수를 제한한다. 

 

FROM Multi Queries

위와 같이 FROM에 multi query로 작성되면 Cross Product인 relation에서 참조한다. 

String Comparisons

특정 문자열로 끝나거나 시작하는 등 String에 조건을 넣어 검색할 때는 LIKE를 사용하거나 ~를 사용하면 된다.

SELECT COUNT(*)
FROM customer
WHERE email LIKE '%@hotmail.com';

위와 동일하다.

SELECT COUNT(*)
FROM customer
WHERE email ~ '*@hotmail.com';

 

GROUP BY & HAVING

Group by는 해당 column value를 기준으로 묶은 결과를 반환한다. 

 

HAVING은 group을 필터링해서 알려준다. Grouping 이후의 값을 계산한다. HAVING은 aggregate quires 이후에만 사용 가능하다. WHERE과 HAVING은 차이는 WHERE은 그룹이 되기 전에 데이터를 필터링하고 HAVING의 경우 그룹화 후의 데이터를 필터링한다. 

SELECT *
FROM watch w
JOIN (
    SELECT c_id
    FROM watch
    GROUP BY c_id
    HAVING COUNT(m_id) >= 2
) AS filtered_customers ON w.c_id = filtered_customers.c_id;

Combining:  OR, AND / UNION, INTERSECT

OR이나 AND를 써서 Where문을 쓸 때 일부 주의해야하는데, 몇몇 예제를 통해 이를 확인해보자. 

 

- Find sid’s of sailors who’ve reserved a red or green boat

초록색 혹은 붉은색 속성을 가진 정보를 찾고 싶을 때 우선 OR과  UNION ALL이 사용 가능하다. 

 

 

위의 2 SQL문 모두 red or  green인 경우에 관해 잘 작동하고 있는 것을 확인 가능하다. 

이때 UNION ALL이라는 문법이 사용되었는데, 이 ALL의 경우에는 multi set을 허용하는 즉, 중복을 허용하는 문법이다. 

 

- Find sid’s of sailors who’ve reserved a red and a green boat

주의 해야할 것은 and문이다. AND를 통해 green과 red를 모두 빌린 이력이 있는 sailor를 뽑으려고 할 때 단순히 AND 문을 아래와 같이 사용하는 것이 우선 생각난다. 

 

하지만 위와 같이 작성하면 아무런 결과도 나오지 않는다. 그 이유는 위 SQL 문은 red와 green  속성을 동시에 가지는 sid를 찾는 sql문이 되었기 때문이다. 

그럼으로 아래와 같이 INTERSECT, 교집합을 통해 각각을 찾고 겹치는 sid를 찾는 것이 올바를 것이다. 

ALL(UNION, INTERSECT, EXCEPT): Set sementics vs multi set sementic

위와 같은 예제가 있을 때, 단순히 UNION과 INTERSECT를 한다면 아래와 같이 집합 연산에 의해 중복을 허용하지 않는 결과가 나올 것이다. EXCEPT는 차집합이다. 

하지만  multi set sementic에서는 중복을 허용하기에 아래와 같이 연산이 되며, 이때 ALL을 뒤에 붙여준다. 즉. 아래와 같이 원소의 개수를 따지면서 연산 또한 다음과 같다. 

즉 ALL을 사용하면 중복을 고려할 수 있다. 

IN / NOT  IN

IN과 NOT IN 모두 특정 값이 서브쿼리 혹은 지정된 목록에 포함되어있는지 여부를 검사하는데 사용되는 연산자이다. 주로 WHERE 절에서 조건을 지정할 때 사용하며 다수의 값과 비교 시 사용한다. 

- IN 예시

 

위의 예시는 102를 빌린 sid가 있는지 확인하는 쿼리 문으로 서브쿼리와 함께 사용되었다. 

IN 혹은 NOT IN을 통해 JOIN 등을 사용하지 않아도 쉽게 검색이 가능하다. 

- NOT IN 예시

위의 예시는 반대로 103을 빌리지 않은 s.name을 찾으며 이는 서브쿼리 안에 해당 sid가 없는지를 확인한다.

 

-Nested Queries with Correlation

외부 쿼리의 값을 참조하여 위와 같이 사용가능하다. 보면 S를 FROM 즉, 서브쿼리에 밖에서 참조하고 있다. 

다만, 이 경우 성능면에서 비효율저일 수 있어 JOIN을 사용하는 것을 권장한다( 외부 쿼리의 각 행에 대해 다시 서브쿼리를 실행해야 하기에 비효율적)

SELECT S.sname
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid
WHERE R.bid = 102;

JOIN

JOIN은 특정 스키마를 기준으로 데이터를 결합하여 하나의 결합된 집합을 반환한다. 

JOIN ON을 기본적으로 사용하고 INNER JOIN을 기본값으로 가진다. 

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

 

- INNER JOIN

INNER JOIN은 두 테이블 간의 공통된 값을 기준으로 교차하는 데이터만 반환된다. 기준을 ON으로 작성한다. 

- NATURAL JOIN

NATURAL JOIN은  equi join을 의미하며 동일한 이름을 가지는 attribute의 조합을 기준으로 교차하여 반환한다. 

아래 링크를 통해 예시를 확인하면 모두 동일한 값을 반환하는 것을 확인 가능하다. 

http://sqlfiddle.com/#!17/4215a/10

SQL Fiddle - Online SQL Compiler for learning & practice

- Left outer JOIN / Right outer JOIN / Full Outer Join

그림과 같이 sid를 기준으로 22, 95가 아닌 31은 남는 상황일때 일반적인 JOIN이라는 match가 이루어지지 않은 95값은 날라간다. 하지만 Outer JOIN은 그런 값들을 보존하며, Left, Right은 어떤 table의 값을 보존할지를 지칭한다. 

예를 들어 위의 값의 결과는 우측의 값이 보존되어있고 NULL이 빈 값에 생기는 것을 확인 가능하다.

NULL과 datatype

위에서 NULL이라고 말했는데 이 NULL은 매우 특수한 case이다. 이는 Field values are sometimes unknown, 즉 값이 비어있는 경우를 말한다. NULL은 특수한 case라 where문을 다룰 때나 논리연산자를 다룰때 주의를 요한다. 

1. NULL의 비교 연산의 기본원칙:

• NULL과의 **모든 비교 연산(>, <, >=, <=)**은 UNKNOWN (알 수 없음)으로 평가된다
• SQL에서 NULL은 값이 아예 없는 것으로 간주되기 때문에, 어떤 값과도 직접적으로 비교할 수 없다
• 즉, 다음과 같은 비교는 모두 FALSE 또는 UNKNOWN으로 평가됩니다:
  NULL > 5 → UNKNOWN
  NULL < 5 → UNKNOWN
  NULL = NULL → UNKNOWN

2. NULL은 WHRE 절에 일반적으로 작동하지 않는다, 따라서 =이 아닌 IS NULL 혹은 IS NOT NULL을 사용해야한다. 

3. NULL은 True, False와 다른 상태이다. 즉 아래와 같이 Tree valued logic을 따른다

즉. NULL과의 연산은 모두 UNKNOWN을 반환한다고 생각하자. 

https://sqlfiddle.com/postgresql/online-compiler?sqlFiddleLegacyID=17-f35aa-2

FREE AI-Enhanced Online PostgreSQL Compiler - For learning & practice