Contents

1️⃣고급연산자(Advanced operator)

2️⃣서브쿼리 (Sub Query)

3️⃣ 조인(Join)

4️⃣유니온(UNION)

5️⃣ 질의문 연습(Query Practice)

We've learned about the basic syntax of SQL so far. Today, we'll delve into writing queries at a higher level. We'll explore advanced operators and the effective use of joins, as well as the application of subqueries in various forms, which we haven't covered yet. Additionally, we'll learn how to implement joins in SQL. Finally, we'll have some practice time by solving examples.

지금까지 SQL의 기본적인 작성 방법에 대해 배웠습니다. 오늘은 이보다 높은 수준의 질의어를 작성하는 방법을 배워보겠습니다. 이를 위해 지금까지 다루지 않았던 고급 연산자, 조인의 효과를 내거나 다양한 형태로 응용할 수 있는 서브쿼리, 조인을 SQL에서 구현하는 법 등에 대해 배울 예정입니다. 마지막으로는 예제를 풀면서 연습하는 시간을 가질 것입니다.

1️⃣고급연산자(Advanced operator)

기본 연산자 외에도 다양한 고급 연산자를 활용하여 더 복잡하고 정교한 쿼리를 작성할 수 있다. 회사 데이터베이스 상태로 연습을 해보자.

회사 데이터베이스 상태(Company DB)

사원 employee, 부양가족: dependent

부서: department 부서위치: dept_locations

프로젝트: project, 참여: works_on;

연산자의 역할(Role of Operator)

연산자는 SELECT 문에서 WHERE <조건> 에 위치한다. 조회하려는 데이터에 특정 조건을 부여할 목적으로 사용한다. 조건문은 연산자, 피연산자(Operand, 오퍼랜드)로 구성된다. 이때 연산자는 비교연산자, SQL연산자, 논리연산자로 구분이 된다.

연산자의 종류(Type of Operator)

단순 비교 연산자 연습(Practice simple comparison operator)

질의문을 보고 SQL문으로 변환하기를 목표로 연습을 많이 해야한다. 복잡한것이 아닌 간단한 질의문먼저 시작해도 된다.

노란색처럼 논리연산자인 AND를 사용해 할 수 있지만 BETWEEN a AND b를 사용할수있다. 이때 정수 4000과 5000은 포함한다는 사실 알아두자.
LIKE 는 쉽게 찾아볼 수 있다. 특히 관계형데이터베이스는 숫자, 혹은 문자이기 때문에 문자열 정보는 많이 사용하게 된다. 특히 위의 예제처럼 검색을 할 때 문자열의 일부값을 비교하여 검색하는 경우도 많다. ➡️*⬅️이 *뜻은 ALL이라는 뜻
%위치는 글자가 어디에 위치하느냐에 따라 달라진다. 만약 '%이' 였으면 끝이 '이'여야 한다는 뜻이다. 양쪽에 %는 위치와 관계없이 '이'만 있으면 검색이 된다는 뜻이다.
"관리자 사원이 없다" 는 뜻은 해당 컬럼의 데이터가 NULL인 리스트를 출력하라는 뜻이다. 반대로 "있다"는 IS NOT NULL로 변경하면 "관리자 사원이 있는" 결과가 뜬다.
노란색처럼 쓸 수 있지만 파란색처럼 IN을 사용할 수 도 있다. "1번 또는 5번 부서에 속하지 않는 사원"을 검색할땐 dno NOT IN (1,5);

문자도 숫자와 마찬가지이다.

2️⃣서브쿼리 (Sub Query)

서브쿼리란 SQL 문 안에 포함된 또 다른 SQL 문이다. SELECT문 하나로만 표현하기에는 여러 테이블의 데이터를 가져오거나, 데이터 추출후 다른 데이터에 가져올 때 등 복잡해지면 SELCT문 하나로는 질의문이 불가능하게 된다. 서브쿼리를 이용한다는 것은 SELECT문 안에 중첩에 또다른 SELCT문을 수행하는 것을 의미한다. 포함된 SQL문의 결과(릴레이션)이 SQL문의 피연산자로 동작한다.

WHERE salary > 4000은 단순히 4천이상이지만 요구하는 질의문이 "전체직원의 평균연봉보다 높은 사람만 가져와라"일때 서브쿼리를 이용해서 단일값으로 작성할 수 있다.

2개의 질의문으로 분해가 가능하다. 부서 검색, 그 부서에서 박현식 사원과 같이 근무하는 사원

사원의 이름과 주소를 검색하는 것은 간단하지만 "20번 프로젝트에 참여"하는 것이다.
우선 프로젝트에 참여하는 사원을 WHERE ssn으로 먼저 구하고, IN연산자를 활용하여 사원의 이름, 주소를 검색하는 과정을 거친다.

참여하지 "않는" 것은 IN과 반대이다.

SELECT ename, address / FROM employee에서 사원의 이름과 주소를 가져온다. 가져올때는 전체 행을 가져오고 보여주는 것만 이름, 주소인 것이다. 노란색 블록을 통해 특정원하는 행을 가져온다.

3️⃣ 조인(Join)

조인이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과집합으로 만들어 내는 것이다. 묶어야하는 이유로는 데이터베이스 설계시 관계모델에 맞게 데이터의 중복성, 이상을 방지하기 위해서 독립적으로 나누어서 저장한다. 즉 잘 설계된 릴레이션 스키마는 독립적인 테이블로 구성되어 있고 그들사이의 관계또한 테이블로 표현이 된다. 하지만 실제 검색할때는 사용자가 원하는 대로 정보를 가져오기 위해 조인을 해야할때가 많다.

"정규화(normalization) 과정을 통해 분할된 테이블"들로부터 통합적인 정보를 얻기 위해서 조인 과정이 필요하다. 조인에는 크게 4가지가 있다.

카티션 프로덕트: 크로스조인 (Cartesian Product: Cross Join)
내부조인:동등조인, 자연조인(Internal Join: Equi Join, Natural Join)
외부조인(Outer Join)
같은 테이블 조인(Self Join) *따로 설명은 없지만 4. 연습에서 예제 언급됨

✅ 카티션 프로덕트

: 크로스조인 (Cartesian Product: Cross Join)

크로스조인을 연산자로 사용한다. 쉼표로도 대체가능하다. 결과는 아래에

곱하기의 결과가 쭉 나열되어있다.
SQL 조인연산자로는 맞는지 확인하기 어려운 부분이 있다. 크로스조인으로

✅내부조인: 세타조인, 동등조인, 자연조인

(Inner Join: theta-join, Equi Join, Natural Join)

두 테이블을 대상으로 조인 조건에 맞는 로우들을 결합하여 새로운 테이블 생성하는 개념이다. 일반적인 조인은 보통 내부조인을 뜻한다. 아래의 3가지가 속한다.

세타조인(theta-join): 비교연산자 {=. <, ≤, ≥, >, ≠} 를 일반화 해서 θ 로 표현한 것이다.
동등조인(Equi-join): 특정 열들 간의 값이 서로 일치하는 경우에만 행을 결합한다.
자연조인(Natural-join): 동일조인에서 중복되는 애트리뷰트를 제거한 것이다. ⋈N 으로 표시한다.

자세한 조인 설명은 11주 관계데이터연산(Relational algebra and Join) https://heesu.io/sql23-basics-of-relational-algebra-and-joins-for-database-system-week11

JOIN을 쓰지 않아도 두번째 노란줄처럼 카디션 프로덕트와 WHERE로 같은 것을 구현할 수 있다.
JOIN을 배우면 여러 테이블을 가져와서 결합 후, 조건에 맞는 행들만 추출할 수 있게 된다.
ON의 조인 조건은 department의 dnumber 과 project의 dnum이 같아야한다. 이다.

두번째는 카티션 프로덕트의 결과이다. 두 개 모두 같은 결과를 보여준다.

✅외부조인(Outer Join) 원칙만 잘 이해하면 된다.

조인 조건을 만족하지 않는 행까지 결과 집합에 포함하여 돌려주는 특수한 조인이다.

집계 등에 종종 사용된다. 아래와 같이 3가지로 구성된다.

왼쪽(Left) 외부 조인
오른쪽(Right) 외부 조인
완전(Full) 외부 조인

ssn,essn = 사원번호 ename = 사원이름

(노란색) 사원과 부양가족 내부조인. employee의 e 는 줄임말이고 dependent d 또한 줄임말이다.
(초록색, 조인조건) e.ssn과 d.essn은 같다. 위에 설정한 줄임말로 인해 e.d사용 가능

만약 부양가족이 없는 사원도 보고싶다면?

이때 사용가능한 조인이 "외부조인, Outer Join"이다.

FROM employee e LEFT OUTER JOIN dependent d : employee 테이블에서 조인에 만족하지 않는 행(e.ssn, e.ename)도 보여주라는 뜻이다.
부양가족이 없는 사원은 NULL로 보여준다.

(노란색) employee e 와 dependent d 동등조인하게 되면 카티션프로덕트 결과에 의해 6개의 행이 추출된다.
(초록색) 그 행들을 사원번호(e.ssn)으로 그룹핑하면 3그룹이 나오는데 그 3그룹의 카운트를 해준다.

"부양가족이 없는 사원"도 보여줘야할 땐 JOIN 에서 LEFT OUTER JOIN을 쓴다. 그렇게 하면 결과에 count(d.essn)이 0으로 되서 보여주게된다.

4️⃣유니온(UNION) 연산자 (+)

둘 이상의 집합을 합성해서 하나의 결과 집합으로 만들어주는 연산자이다.
비슷한 결과 집합을 가지지만 하나의 SELECT 문으로 만들기 힘들 경우에 유니온 연산자를 사용한다.
복잡한 단일 SELECT 문보다 유니온 연산자를 이용하여 여러 개의 단순한 SELECT 문으로 분리하면 성능 개선도 가능하다.

5️⃣ 질의문 연습

(Query Practice)

단일 테이블 검색 - 간단한 검색이다.

조인 검색
사원번호(ssn), 부서번호(dno)는 employee 테이블에서 추출이 가능하다. (노란색) 부서명(dname)은 department 테이블에 있기 때문에 조인을 해야한다. (파란색)
employee와 department를 JOIN한다. (보라색)
ON조건에는 employee의 dno 와 department의 dnumber가 같은 것만 보여달라고 쓴다.
ON조건에 부합하는 결과들중 보여주는 것은 ssn, dname만 보여달라고 요청한다. (분홍색별) 부서이름(dname)대신 사원이름(ename)을 보고싶으면 바꿔쓴다.

조인 검색 - 조건 추가
위에서 연습한 예제에서 '확장'한 조건을 추가한 조인검색이다.(노란색)
결과에서 department의 d.name이 '연구소'이다.라는 조건을 where로 달 수 있다.
이름(ename)과 주소(address)를 추출했다.

조인 검색 - 별명 사용
파란색처럼 FROM에서 줄임말을 e, d이렇게 지정하면 다른 clause에서도 사용가능하다. (노란색)

같은 테이블 조인, 검색 테이블 조인
줄임말은 여기서 효과를 볼 수 있다. 왼쪽에있는 정보는 사원(e)이고 오른쪽에 있는 정보는 직속 상사(s)를 뜻한다.
조인이 어렵게 느껴질 수 도 있지만 기본적으로 카티션 프로덕트로 곱하기 한후 걸러내는 것이라고 생각하면 쉽다.
직속상사가 없는 사람도 보여주는 정보를 추출하려면 LEFT OUTER JOIN을 쓰면된다.

중복값 제거는 DISTINCT 이다.
노란색만 입력하면 첫번째결과가, DISTINCT를 다시 실행면 두번째 결과가 나온다.

LIKE연산자로 문자열 검색을 할 수 있다.

서브쿼리 - 우선 10번 프로젝트에 참여하는 사원의 이름과 연봉을 가져온다.
UPDATE SET을 통해 연봉을 * 1.1 한다.

조건의 나열 : 더쉬움

오늘 배운 것 중에 가장 복잡할 수 있는 2개 이상 테이블의 조인이다.
3개 이상의 테이블을 조인하려면 2개를 조인하고, 나머지 1개를 조인하는 식이다.
또는 3개의 테이블을 카티션 프로덕트 후 조건에 맞는 행을 추출할 수 도 있다.

사원의이름(employee), 프로젝트 리스트(project), 각사원이 참여(works_on) 3가지의 테이블을 조인해야한다. 무엇 먼저 조인할지 고민해본다.
결과적으로 사원의이름, 사원이 참여하는 프로젝트를 먼저해야한다. (서브쿼리)
분홍색) 서브쿼리에서 프로젝트 p와 works_on w를 카티션 프로덕트 한다. p의 pnumber와 w의 pno가 같은 것만 골라내라는 뜻이다. 결과로는 프로덕트와 works_on의 골라낸 것들이 조인해서 나타나게된다. 그 결과를 "q"로 지정한다.
그레이) e의 ssn와 q의 essn이 같은 것만 추출한다.
파란색) 마지막으로 e.ename으로 ORDER BY한다.

2개이상 테이블의 조인에서 조건이 추가된 형식이다. 프로젝트의 정보는 project, 사원의 이름, 주소, 생년월일을 employee에서 추출가능하고 '부서 관리자'인지 아닌지는 department에서 가능하다.
1: 서브쿼리, e와 d를 먼저 조인한다. 그결과를 q로 지정한다
2: WHERE clause로 '서울'의 조건을 건다.

2개이상의 테이블을 조인하고 조건추가까지 가능하면 높은 수준인 것이라고 하셨다 )교수님왈