OR in SQL giving problems-CodePudding

SELECT * 
    FROM FEEDER A
    WHERE A.CF = 'X'
    OR A.SF = 'X'
    AND A.YEAR = 2021;

When I run the above query, it brings back all the years ! I only want 2021 where CF OR SF is X.

CodePudding user response：

You simply need 2 Parenthesis -

SELECT * 
    FROM FEEDER A
    WHERE (A.CF = 'X'
    OR A.SF = 'X')
    AND A.YEAR = 2021;

Or can shorten your query to -

SELECT * 
    FROM FEEDER A
    WHERE 'X' IN (A.CF, A.SF)
    AND A.YEAR = 2021;

CodePudding user response：

The AND operator has higher precedence than the OR operator so your query is the same as:

SELECT * 
FROM   FEEDER A
WHERE  (A.CF = 'X')
OR     (A.SF = 'X' AND A.YEAR = 2021);

and will return: any year where CF = 'X'; or only the 2021 year when SF = 'X'.

If you want to fix your query then you need either:

SELECT * 
FROM   FEEDER A
WHERE  A.CF = 'X'
AND    A.YEAR = 2021
OR     A.SF = 'X'
AND    A.YEAR = 2021;

or, slightly easier to read, use brackets:

SELECT * 
FROM   FEEDER A
WHERE  (  A.CF = 'X'
       OR A.SF = 'X')
AND    A.YEAR = 2021;

or, use the IN operator:

SELECT * 
FROM   FEEDER A
WHERE  'X' IN (A.CF, A.SF)
AND    A.YEAR = 2021;