Data Slipping thru Query on SQL?-CodePudding

I do not understand how certain accounts can slip through the query as shown below; Am I doing something wrong? I am trying to make sure only Portfolios 1 - 3's bonds and swaps feed thru.

SELECT DISTINCT portfolio
FROM holdings_table
WHERE as_of_date = '2022-01-01'
AND asset_class = 'bond' OR asset_class = 'swap'
AND portfolio = 'PORT1' OR portfolio = 'PORT2' OR portfolio = 'PORT3'

portfolio
PORT1
PORT2
PORT3
PORT4
PORT6

CodePudding user response：

In SQL, AND has precedence over OR. Therefore, what you wrote is interpreted as:

SELECT DISTINCT portfolio
FROM holdings_table
WHERE (as_of_date = '2022-01-01'
AND asset_class = 'bond') OR (asset_class = 'swap'
AND portfolio = 'PORT1' OR portfolio = 'PORT2' OR portfolio = 'PORT3')

What you probably wanted to write (but please double-check) can be achieved using parentheses:

SELECT DISTINCT portfolio
FROM holdings_table
WHERE as_of_date = '2022-01-01'
AND (asset_class = 'bond' OR asset_class = 'swap')
AND (portfolio = 'PORT1' OR portfolio = 'PORT2' OR portfolio = 'PORT3')

Pro tip: you can use asset_class IN ('bond', 'swap)` and similar for portfolio: this simplifies the query and helps you avoid this kind of mistakes.