[MariaDB] chapter5

INNER JOIN

INNER JOIN은 두 테이블 간에 일치하는 행만 반환. 즉, 연결 조건을 충족하는 행만 결과에 포함

SELECT ename, job,sal,e.deptno, d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno;

[join 여러테이블] 
여러개의 테이블(3개이상) 조인
select 컬럼명
from 테이벌이름 1 별칭1 [inner] join 테이블이름2 별칭2 
on 별칭1.컬럼명 = 별칭2.컬럼명 [inner]  join 테이블이름3 별칭3
on 별칭2.컬럼명 = 별칭3.컬럼명 ...

SELECT p.profno, concat(p.name,' ' ,'교수') 교수이름,
s.studno, s.name, s.grade, d.dname
FROM professor p JOIN student s 
ON p.profno = s.profno JOIN department d
ON p.deptno = d.deptno;

LEFT JOIN (또는 LEFT OUTER JOIN)

LEFT JOIN은 왼쪽(첫 번째) 테이블의 모든 행을 반환하고, 오른쪽(두 번째) 테이블과 일치하는 행이 없으면 NULL 값을 반환.

SELECT ename, job,sal,e.deptno, dname
FROM dept d left outer JOIN emp e  
-- left outer join 기준 왼쪽에 있는 데이터 모두, 
오른쪽 데이터는 일치하는 것만 출력
ON d.deptno = e.deptno;

RIGHT JOIN (또는 RIGHT OUTER JOIN)

RIGHT JOIN은 오른쪽(두 번째) 테이블의 모든 행을 반환하고, 왼쪽(첫 번째) 테이블과 일치하는 행이 없으면 NULL 값을 반환.

SELECT ename, job,sal,e.deptno, dname
FROM dept d right outer JOIN emp e 
-- right outer join 기준 오른쪽에 있는 데이터 모두, 
왼쪽 데이터는 일치하는 것만 출력
ON d.deptno = e.deptno;

FULL JOIN (또는 FULL OUTER JOIN)

FULL JOIN은 양쪽 테이블의 모든 행을 반환하며, 일치하지 않는 행에 대해 NULL 값을 반환.

MariaDB는 FULL JOIN을 직접 지원하지 않습니다. 
대신 LEFT JOIN과 RIGHT JOIN을 UNION 연산자를 사용하여 결합하여
동일한 효과를 얻을 수 있습니다.

CROSS JOIN (또는 CARTESIAN JOIN)

CROSS JOIN은 두 테이블 간의 모든 가능한 조합을 생성. 이것은 테이블의 크기에 따라 결과 행 수가 급격하게 증가할 수 있으므로 주의해야 함.

SELECT * 
FROM buy
CROSS JOIN member; -- buy table rowcount * member row count

SELF JOIN

SELF JOIN은 동일한 테이블을 사용하여 테이블 내의 행을 서로 연결하는 데 사용. 자체 참조적인 관계를 다룰 때 유용.

SELECT e.EMPNO 사원번호, e.ENAME 이름, e.JOB 직책, 
em.MGR 매니저번호, em.ENAME 매니저이름
FROM emp e JOIN emp em
ON e.mgr = em.mgr;

서브쿼리

쿼리 내에서 다른 쿼리를 포함하는 쿼리
서브쿼리는 주 쿼리의 WHERE, HAVING, FROM 절에서 사용할 수 있으며, 다양한 상황에서 유용

목적

• 데이터 필터링: 서브쿼리를 사용하여 주 쿼리의 결과 집합을 필터링하거나 제한.
• 데이터 검색: 서브쿼리를 사용하여 주 쿼리에서 검색된 결과 집합의 일부 데이터를 추가로 검색.
• 집계: 서브쿼리를 사용하여 주 쿼리의 결과를 집계하거나 서브쿼리 결과를 기반으로 주 쿼리의 집계를 수행.
• 비교: 서브쿼리를 사용하여 주 쿼리의 결과와 비교하거나 일치하는 항목 찾기.

[예시]
SELECT * FROM emp2 
WHERE pay > (SELECT pay FROM emp2 WHERE NAME='백원만');

SELECT * FROM emp2 
WHERE pay < (SELECT avg(pay) FROM emp2); 

유형

• 스칼라 서브쿼리 : 서브쿼리가 하나의 값을 반환.

-- 직원 중에서 최고 급여를 받는 직원 검색
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

• 테이블 서브쿼리 : 서브쿼리가 여러 행과 열을 반환.

-- 부서별로 평균 급여를 계산하여 부서 이름과 평균 급여를 반환
SELECT department_name, avg_salary
FROM departments
JOIN (SELECT department_id, AVG(salary) AS avg_salary 
FROM employees GROUP BY department_id) AS subquery
ON departments.department_id = subquery.department_id;

• EXISTS 서브쿼리 : 서브쿼리가 결과가 존재하는지 여부를 검사.

-- 주문이 있는 고객 검색
SELECT customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

• IN 서브쿼리 : 주 쿼리 결과 집합 내에서 서브쿼리 결과와 일치하는 값을 반환.

-- 특정 부서에 속한 직원 검색
SELECT name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Engineering');

subquery의 종류 3가지

1. Single Row Subquery (단일 행 서브쿼리):
   단일 행 서브쿼리는 서브쿼리가 딱 한 행을 반환하는 경우를 나타냅니다. 이 경우, 서브쿼리의 결과 집합은 단일 행과 단일 열의 값을 가집니다.
   일반적으로 단일 행 서브쿼리는 비교 연산자 (예: =, >, <)와 함께 사용되어 주 쿼리의 결과와 비교됩니다.
   주로 단일 값을 반환하는 경우에 사용되며, 주로 SELECT 문에서 사용됩니다.

-- 부서별로 최고 급여를 가진 직원 검색 (단일 행 서브쿼리)
SELECT department_id, name, salary
FROM employees
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

2. Multiple Row Subquery (다중 행 서브쿼리):
   다중 행 서브쿼리는 서브쿼리가 여러 행을 반환하는 경우를 나타냅니다. 이 경우, 서브쿼리의 결과 집합은 여러 행과 여러 열의 값을 가집니다.
   다중 행 서브쿼리는 주로 IN, NOT IN, ANY, ALL과 같은 비교 연산자와 함께 사용됩니다.
   주로 여러 값을 반환하는 경우에 사용됩니다.

-- 특정 부서에 속한 직원 검색 (다중 행 서브쿼리)
SELECT name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

3. Multiple Column Subquery (다중 열 서브쿼리):
   다중 열 서브쿼리는 서브쿼리가 여러 열을 반환하는 경우를 나타냅니다. 이 경우, 서브쿼리의 결과 집합은 여러 행과 여러 열의 값을 가집니다.
   다중 열 서브쿼리는 주로 SELECT 문에서 사용되며, 서브쿼리 결과를 주 쿼리의 열에 대응시킬 때 유용합니다.

-- 부서별로 평균 급여와 최고 급여를 비교 (다중 열 서브쿼리)
SELECT department_id, AVG(salary) AS avg_salary,
  (SELECT MAX(salary) FROM employees WHERE employees.department_id = departments.department_id) AS max_salary
FROM departments;