WHERE/Having Clause Alternative

Few cases where these clauses should be used differently

Try to avoid operations on database objects referenced in the WHERE clause.

Given Query	Alternative
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp WHERE SUBSTR(Ename,1,3) = 'SCO';	FROM EMP WHERE Ename LIKE 'SCO%';
VARIABLE Name VARCHAR2(20)	VARIABLE Name VARCHAR2(20)
Exec Name := 'SCOTT'	Exec Name := 'SCOTT'
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp WHERE Ename = NVL (:Name, Ename);	FROM Emp WHERE Ename LIKE NVL (:Name, '%');
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp	FROM Emp
WHERE TRUNC (Hiredate) = TRUNC (SYSDATE);	WHERE Hiredate BETWEEN TRUNC (SYSDATE)
	AND TRUNC (SYSDATE) + .99999;
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp	FROM Emp
WHERE Ename || Empno = 'SCOTT7788';	WHERE Ename = 'SCOTT
	AND Empno = 7788;
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp	FROM Emp
WHERE Sal + 3000 < 5000;	WHERE Sal < 2000;
SELECT Ename, Hiredate, Sal	SELECT Ename, Hiredate, Sal
FROM Emp	FROM Emp
WHERE Sal != 0;	WHERE Sal > 0;

HAVING Clause 

The HAVING clause filters selected rows only after all rows have been fetched.  Using a WHERE clause helps reduce overheads in sorting, summing, etc.  HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Given Query	Alternative
SELECT D.Dname, AVG (E.Sal) FROM Emp E, Dept D WHERE E.Deptno = D.Deptno GROUP BY D.Dname HAVING Dname != 'RESEAECH' AND Dname != 'SALES';	SELECT D.Dname, AVG (E.Sal) FROM Emp E, Dept D WHERE E.Deptno = D.Deptno AND Dname != 'RESEAECH' AND Dname != 'SALES' GROUP BY D.dname;

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.