0

I want to have a parameter optionally excluding pricing information in a query. I've designed it to exclude information by shortcutting an evaluation to false. Which of these two would be faster and why?

DECLARE @fetchPricingData = 'No'

Example 1, shortcut to false in Join Condition:

SELECT...
FROM myTable
  FULL JOIN (...) AS derivedPricingTable
     ON derivedPricingTable.key = myTable.key
        AND @fetchPricingData = 'Yes'

Example 2, shortcut to false in the Where Clause:

SELECT...
FROM myTable
  FULL JOIN (
     SELECT ...
     FROM pricing_info
     WHERE @fetchPricingData = 'Yes'
  ) AS derivedPricingTable
     ON derivedPricingTable.key = myTable.key
Minimistro
  • 73
  • 7
  • 2
    Inspect the execution plan and find out. Performance is totally database specific... the query is just a logical expression of the results you want. How that is carried out under the hood depends on many many factors. – Dale K Sep 21 '21 at 23:04
  • 1
    Filtering for a `full join` is quite tricky. The two are not equivalent. – Gordon Linoff Sep 21 '21 at 23:17
  • Your query is malformed. The predicate `derivedPricingTable.key = myTable.key` silently changes the `FULL JOIN` to a `LEFT JOIN` or `INNER JOIN`. I would suggest you fix the join first and then you can tackle the performance. – The Impaler Sep 21 '21 at 23:35
  • The question doesn't make sense. Voting to close. – The Impaler Sep 22 '21 at 00:12
  • @GordonLinoff's answer [here](https://stackoverflow.com/questions/31809767/sql-server-derived-table-data-storage), helped. – Minimistro Sep 22 '21 at 00:19
  • @TheImpaler, must be something about the way I've described it here. Works fine in my query. Feel free to close this or I can delete it. – Minimistro Sep 22 '21 at 00:20
  • The query runs, yes, but it's not doing what you think it's doing. Please read my first comment and fix the logic of the query. – The Impaler Sep 22 '21 at 02:27

0 Answers0