Data Slipping thru Query on SQL?

August 18, 2022

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

>Solution :

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.