You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
49 lines
1.2 KiB
49 lines
1.2 KiB
USE Study
|
|
GO
|
|
|
|
SELECT DNAME
|
|
FROM DEPARTMENT
|
|
WHERE DEPARTMENT.DNO = (SELECT DNO
|
|
FROM EMPLOYEE
|
|
WHERE EMPLOYEE.ENO = 103)
|
|
|
|
-- 위 쿼리와 동일
|
|
SELECT DNAME
|
|
FROM DEPARTMENT
|
|
JOIN EMPLOYEE ON EMPLOYEE.DNO = DEPARTMENT.DNO
|
|
WHERE EMPLOYEE.ENO = 103
|
|
|
|
|
|
-- 단일행 서브쿼리
|
|
SELECT ENO,DNO
|
|
FROM EMPLOYEE
|
|
WHERE EMPLOYEE.DNO = (SELECT DNO FROM EMPLOYEE WHERE EMPLOYEE.ENO = 110)
|
|
|
|
-- 다중행 서브쿼리
|
|
SELECT ENAME, ENO,DNO, SALARY
|
|
FROM EMPLOYEE
|
|
WHERE EMPLOYEE.DNO IN (SELECT DNO FROM EMPLOYEE WHERE SALARY >= 500)
|
|
|
|
SELECT ENAME, ENO,DNO, SALARY
|
|
FROM EMPLOYEE
|
|
WHERE SALARY >= ANY (SELECT SALARY FROM EMPLOYEE WHERE DNO = 20)
|
|
|
|
SELECT ENAME, ENO,DNO, SALARY
|
|
FROM EMPLOYEE
|
|
WHERE SALARY >= ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO = 10)
|
|
|
|
SELECT ENAME
|
|
FROM EMPLOYEE
|
|
WHERE EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY + COMMISSION > 500)
|
|
|
|
SELECT ENO, ENAME, DNO, SALARY
|
|
FROM EMPLOYEE
|
|
WHERE DNO IN (SELECT DNO FROM EMPLOYEE WHERE ENO = 101)
|
|
AND SALARY IN (SELECT SALARY FROM EMPLOYEE WHERE ENO = 101)
|
|
|
|
|
|
-- 상호 연관 서브쿼리: 일반적으로 성능이 떨어지며, 조인을 쓰는게 좋음
|
|
-- (메인 쿼리에 따라 서브쿼리 결과가 달라지는 상호 연관된 서브쿼리는 빈번한 조회를 유발)
|
|
SELECT ENO, ENAME, SALARY, DNO
|
|
FROM EMPLOYEE AS E
|
|
WHERE DNO IN (SELECT DNO FROM EMPLOYEE AS M WHERE E.MANAGER = M.ENO) |