Why are these 2 queries giving different outputs?-CodePudding

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

CodePudding user response：

JOB IN 'MANAGER' OR ...

means all managers OR the next predicate

please put parentheses to achieve the expected result

JOB IN ('MANAGER', 'ANALYST')

CodePudding user response：

[TL;DR] 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;