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.