SELECT *
FROM emp E
WHERE E.sal > 50000
AND 25 > (SELECT COUNT(*)
FROM emp M
WHERE M.mgr = E.empno);
-- CPU = 156 detik --
SELECT *
FROM emp E
WHERE 25 > (SELECT COUNT(*)
FROM emp M
WHERE M.mgr = E.empno)
AND E.sal > 50000;
-- CPU = 10 detik --
SELECT *
FROM emp E
WHERE 25 > (SELECT COUNT(*)
FROM emp M
WHERE M.mgr = E.empno)
OR E.sal > 50000;
-- CPU = 100 detik --
SELECT *
FROM emp E
WHERE E.sal > 50000
OR 25 > (SELECT COUNT(*)
FROM emp M
WHERE M.mgr = E.empno);
-- CPU = 30 detik --
SELECT deptno,
AVG(sal)
FROM emp
GROUP BY deptno
HAVING deptno IN (10, 20);
-- Having: Baris akan disaring setelah hasilnya di return --
SELECT deptno,
AVG(sal)
FROM emp
WHERE deptno IN (10, 20)
GROUP BY deptno;
-- Where: baris disaring duluan/hasil lebih sedikit saat di grup
-- penggunaan lebih cepet ~50%
SELECT COUNT(*)
FROM emp;
--dari
SELECT COUNT(empno)
FROM emp;