Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.
Find all employees who has a unique job title in their respective department.
Solution #1: Using NOT EXISTS
SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job
Result:
ENAME DEPTNO JOB SAL
---------- ---------- --------- ----------
MILLER 10 CLERK 1300
CLARK 10 MANAGER 2450
KING 10 PRESIDENT 5000
JONES 20 MANAGER 2975
JAMES 30 CLERK 950
BLAKE 30 MANAGER 2850
Solution #1.1 – Generic substitution for NOT EXISTS
SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job
Solution #2: Using NOT IN
SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
FROM emp b
WHERE a.deptno=b.deptno
AND a.empno!=b.empno)
ORDER BY deptno, job
Result:
ENAME DEPTNO JOB SAL
---------- ---------- --------- ----------
MILLER 10 CLERK 1300
CLARK 10 MANAGER 2450
KING 10 PRESIDENT 5000
JONES 20 MANAGER 2975
JAMES 30 CLERK 950
BLAKE 30 MANAGER 2850
Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.
SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
FROM emp b
WHERE a.empno!=b.empno)
ORDER BY deptno, job
Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)
SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job
Result:
ENAME DEPTNO JOB SAL
---------- ---------- --------- ----------
MILLER 10 CLERK 1300
CLARK 10 MANAGER 2450
KING 10 PRESIDENT 5000
JONES 20 MANAGER 2975
JAMES 30 CLERK 950
BLAKE 30 MANAGER 2850
Solution #3.2: A mixed version of Solutions #1.1 and #3.1:
SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job
Solution #4: Using Analytical function COUNT
WITH x AS ( SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt FROM emp a ) SELECT ename, deptno, job, sal FROM x WHERE cnt=1 ORDER BY deptno, job Result: ENAME DEPTNO JOB SAL ---------- ---------- --------- ---------- MILLER 10 CLERK 1300 CLARK 10 MANAGER 2450 KING 10 PRESIDENT 5000 JONES 20 MANAGER 2975 JAMES 30 CLERK 950 BLAKE 30 MANAGER 2850
Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4
WITH x AS (
SELECT ename, deptno, job, sal,
MAX(empno) OVER(PARTITION BY deptno, job) max_no,
MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job
Solution #5: Using In-Line view (WITH)
WITH x AS ( SELECT deptno, job FROM emp GROUP BY deptno, job HAVING COUNT(*)=1 ) SELECT ename, deptno, job, sal FROM emp JOIN x USING (deptno, job) ORDER BY deptno, job Result: ENAME DEPTNO JOB SAL ---------- ---------- --------- ---------- MILLER 10 CLERK 1300 CLARK 10 MANAGER 2450 KING 10 PRESIDENT 5000 JONES 20 MANAGER 2975 JAMES 30 CLERK 950 BLAKE 30 MANAGER 2850
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.
sqlpatterns 