--1 DESTINCT
SELECT
DISTINCT A.DEPARTMENT_NAME
FROM
DEPARTMENTS A
, EMPLOYEES B
WHERE B.HIRE_DATE >= '20060101'
AND B.DEPARTMENT_ID = A.DEPARTMENT_ID;
--2 INNER JOIN (ON)
SELECT
DISTINCT A.DEPARTMENT_NAME
FROM
DEPARTMENTS A INNER JOIN EMPLOYEES B
ON B.DEPARTMENT_ID = A.DEPARTMENT_ID
WHERE B.HIRE_DATE >= '20060101';
--3 NATURAL JOIN
SELECT
DISTINCT DEPARTMENT_NAME
FROM DEPARTMENTS A NATURAL JOIN EMPLOYEES B
WHERE B.HIRE_DATE >= '20060101';
--4 NATURAL JOIN (USING)
SELECT
DISTINCT DEPARTMENT_NAME
FROM DEPARTMENTS A JOIN EMPLOYEES B
USING (DEPARTMENT_ID)
WHERE B.HIRE_DATE >= '20060101';
--5 SUBQUERY (IN)
SELECT
DISTINCT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101');
--6 SUBQUERY (IN - GROUP)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
GROUP BY DEPARTMENT_ID
);
--7 SUBQUERY (ANY)
SELECT
DISTINCT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = ANY (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101');
--8 SUBQUERY (SOME)
SELECT
DISTINCT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = SOME (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101');
--9 SUBQUERY (EXITST)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A
WHERE EXISTS(
SELECT 'X' FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
AND DEPARTMENT_ID = A.DEPARTMENT_ID
);
--10 SUBQUERY (연관서브쿼리)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
AND DEPARTMENT_ID = A.DEPARTMENT_ID
);
--11 Scalar subquery
SELECT
DISTINCT
(SELECT DEPARTMENT_NAME FROM DEPARTMENTS A WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME
FROM EMPLOYEES B
WHERE B.HIRE_DATE >= '20060101'
AND B.DEPARTMENT_ID IS NOT NULL;
--12 Scalar subquery + FROM 절 (GROUP BY)
SELECT
(SELECT DEPARTMENT_NAME FROM DEPARTMENTS A WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME
FROM (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
GROUP BY DEPARTMENT_ID) B
WHERE B.DEPARTMENT_ID IS NOT NULL;
--13 Scalar subquery + FROM 절 (DISTINCT)
SELECT
(SELECT DEPARTMENT_NAME FROM DEPARTMENTS A WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME
FROM (SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
) B
WHERE B.DEPARTMENT_ID IS NOT NULL;
--14 FROM절
SELECT
DISTINCT A.DEPARTMENT_NAME
FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101') B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;
--15 FROM절 (DISTINCT)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A , (SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101') B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;
--16 FROM절 (GROUP BY)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;
--17 FROM절 (GROUP BY)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101') B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_NAME;
--18 HAVING절
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A
GROUP BY A.DEPARTMENT_NAME, A.DEPARTMENT_ID
HAVING A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101');
--19 GROUP BY
SELECT
A.DEPARTMENT_NAME
FROM
DEPARTMENTS A
, EMPLOYEES B
WHERE B.HIRE_DATE >= '20060101'
AND B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_NAME;
--20 ROWNUM (젤빠름)
SELECT
A.DEPARTMENT_NAME
FROM DEPARTMENTS A
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID FROM EMPLOYEES
WHERE HIRE_DATE >= '20060101'
AND DEPARTMENT_ID = A.DEPARTMENT_ID
AND ROWNUM <= 1
);
더 있으면 알려주시요^^
