Join

두 개의 테이블 결합
조인 조건은 Where에 적는다.
가장 대표적으로 " = " 연산.

Q: Find the names and department names of all employees.

-- sql
Select	EMPNAME, DEPTNAME
From 	EMPLOYEE as E, DEPARTMENT as D
Where	E.DNO = D.DEPTNO;

---

Self join

테이블 하나로 조인.
이 경우, 한 테이블로 서로 다른 두 개의 alias를 적용해 From에 적어야 함.

Q. Find the names and superior's names of all employees.

-- sql
Select	E.EMPNAME, M.EMPNAME
From 	EMPLOYEE as E, EMPLOYEE as M
Where	E.MANAGER = M.EMPNO;

---

Join & Order by

Q. Find the department names, names, titles, and salaries of all employees and sort department names in ASC. If department names are same, sort salaries in DESC.

-- sql
Select	DEPTNAME, EMPNAME, TITLE, SALARY
From 	EMPLOYEE as E, DEPARTMENT as D
Where	E.DNO = D.DEPTNO
Order by DEPTNAME, SALARY DESC;

---

Nested query

중첩 질의.
Where에 SELECT FROM WHERE가 들어감.
중첩 질의는 3가지 유형을 리턴함.

• scalar value
• relation of one attribute.
• relation of multiple attributes.

1) return scalar value

Q. Find the names and titles of all employees who have the same title as that of 'Y.PARK'.

-- sql
Select	EMPNAME, TITLE
From 	EMPLOYEE
Where	TITLE = (
		Select	TITLE
       	From	EMPLOYEE
        Where	EMPNAME = 'Y.PARK');

Y.PARK의 직급이 매니저인지 모름. 따라서 Where에 매니저를 찾기 위한 select from where가 들어감.

2) return relation of one attribute

Attribute가 하나인 테이블이 WHERE에 리턴된다.
IN, ANY, ALL, EXISTS 연산이 Where에 쓰임.

• IN: 애트리뷰트가 포함되어 있냐?
  

• ANY: 하나 이상의 애트리뷰트가 포함되어 있냐?
  

• ALL: 모든 애트리뷰트가 포함되어 있냐?
  

Q. Find the names of all employees who work for the 'SALES' or 'DEVELOPMENT' department.

1. Nested query

-- Nested query
Select	EMPNAME
From 	EMPLOYEE
Where	DNO IN (
		Select	DEPTNO
       	From	DEPARTMENT
        Where	DEPTNAME = 'SALES' or DEPTNAME = 'DEVELOPMENT');

2. Join query without nested query

-- Join query
Select	EMPNAME
From 	EMPLOYEE as E, DEPARTMENT as D
Where	E.DNO = D.DEPTNO
		and (D.DEPTNAME = 'SALES' or D.DEPTNAME = 'DEVELOPMENT');

3) return relation of nultiple attributes

다수의 애트리뷰트의 테이블을 리턴할 경우, 중첩 질의의 결과에 대한 EXISTS operator를 사용할 수 있다. (if not empty, return TRUE)

Q. Find the names of all empolyees who work for the 'SALES' or 'DEVELOPMENT' department.

-- Nested query
Select	EMPNAME
From 	EMPLOYEE as E
Where	EXISTS (
		Select	*
        From	DEPARTMENT as D
        Where	E.DNO = D.DEPTNO 
        		and
        		(DEPTNAME = 'SALES' or DEPTNAME='DEVELOPMENT');

이 역시 물론 중첩 질의 없이 Join query로 작성할 수 있겠지

-- Join query
Select	EMPNAME
From 	EMPLOYEE as E, DEPARTMENT as D
Where	E.DNO = D.DEPTNO 
		and
        (D.DEPTNAME = 'SALES' or D.DEPTNAME='DEVELOPMENT');

---

Correlated nested query

중첩 질의 외부에 선언된 테이블의 컬럼을 참조하는 질의.

Q. Find the names, department numbers, and salaries of all employees who get higher salaries than the average salary of theirs department.

-- Correlated nested query
Select	EMPNAME, DNO, SAPARY
From 	EMPLOYEE as E
Where	SALARY > (
		Select	AVG(SALARY) as AVGSAL
        From	EMPLOYEE
        Where	DNO = E.DNO);