Query no 1:-
SELECT COUNT(ENAME) FROM EMP WHERE JOB IN 'MANAGER' OR JOB IN 'ANALYST' AND SAL IN ( SELECT SAL + NVL (COMM,0) FROM EMP WHERE SAL LIKE '%0') GROUP BY JOB;
The Query 1 gives me the following output:-
COUNT(ENAME) ------------ 3 2
Query no 2:-
SELECT COUNT(ENAME) FROM EMP WHERE JOB = ANY ( SELECT JOB FROM EMP WHERE JOB IN ('MANAGER', 'ANALYST') ) AND SAL IN ( SELECT SAL + NVL (COMM,0) FROM EMP WHERE SAL LIKE '%0' ) GROUP BY JOB;
The Query 2 gives me the following output:-
COUNT(ENAME) ------------ 2 2
Advertisement
Answer
Summary: The AND
operator has higher precedence than the OR
operator. To fix it, use brackets around the OR
expression.
AND
has higher precedence than OR
so your first query is the equivalent of (with added brackets and indentation to emphasise the precedence):
SELECT COUNT(ENAME) FROM EMP WHERE JOB IN 'MANAGER' OR ( JOB IN 'ANALYST' AND SAL IN ( SELECT SAL + NVL (COMM,0) FROM EMP WHERE SAL LIKE '%0' ) ) GROUP BY JOB;
So you are finding either: managers with any salary; or analysts whose salary equals the salary plus commission of any other employee.
Your second query is the equivalent of:
SELECT COUNT(ENAME) FROM EMP WHERE ( JOB IN 'MANAGER' OR JOB IN 'ANALYST' ) AND SAL IN ( SELECT SAL + NVL (COMM,0) FROM EMP WHERE SAL LIKE '%0' ) GROUP BY JOB;
Which finds: any employee who is either a manager or an analyst and whose salary equals the salary plus commission of any other employee.
You could rewrite your first query to:
SELECT COUNT(ENAME) FROM EMP WHERE JOB IN ('MANAGER', 'ANALYST') AND SAL IN ( SELECT SAL + NVL (COMM,0) FROM EMP WHERE SAL LIKE '%0' ) GROUP BY JOB;