Rules for HAVING clause is not so easy as it may look like at first glance. It is an assumption that HAVING clause can contain the same grouping/aggregation as SELECT and it will work just fine because the role of HAVING is to filter whatever has left after aggregation done […]
Monthly Archives: May 2019
5 posts
DECODE output may be type sensitive Assuming current months is MAY Since MM format will return ’05’ it won’t match ‘5’ as shown below select decode(to_char(sysdate,'MM'),'05','MAY','other month') from dual; --MAY select decode(to_char(sysdate,'MM'),'5','MAY','other month') from dual; -- other month At the same time, if 05 or 5 will be used with […]
NULL is not determinate value and is not counted when the COUNT function As we can see when you supply NULL explicitly to COUNT it will produce 0, even if the query was executed against dual table. In plain words, you can translate this operation as “nothing to count” since […]
The following table summarizes the equivalents for ANY and ALL: Let’s have a look for each case: < ANY ( LESS THAN THE HIGHEST ) select * from employees where salary < ANY (select salary from employees where department_id = 80) ; The query will show all employees with […]
This is one more common mistake the one can make when it comes to NULL engaged with comparison Consider these two statements, they will produce different results: select * from emp where commission_pct in (0.1, NULL) ; select * from emp where commission_pct = 0.1 or commission_pct IS NULL ; […]