Using Common Table Expressions To Drive Queries

Kevin Feasel

2017-06-01

Syntax

Lukas Eder wants one result set which returns records using predicate B if and only if there were no records using predicate A:

We’ve seen that we can easily solve the original problem with SQL only: Select some data from a table using predicate A, and if we don’t find any data for predicate A, then try finding data using predicate B from the same table.

Oracle and PostgreSQL can both optimise away the unnecessary query 2 by inserting a “probe” in their execution plans that knows whether the query 2 needs to be executed or not. In Oracle, we’ve even seen a situation where the combined query outperforms two individual queries. SQL Server 2014 surprisingly does not have such an optimisation.

Interesting totally-not-a-comparison between the three database products.  There are some things I’d ideally like the SQL Server optimizer to do with common table expressions, but as Lukas notes, it doesn’t, so user beware.

Related Posts

The Joy Of OUTPUT

Mark Wilkinson shows off some fun stuff you can do with the OUTPUT clause: A common command in the Linux world is the tee command. What tee allows you to do is pipe the output of a command to a file as well as the console. This same functionality can be implemented using multiple OUTPUT clauses in a T-SQL statement. In […]

Read More

Using JSON_MODIFY To Modify Existing JSON

Jovan Popovic shows off the JSON_MODIFY function in SQL Server:   Recently I found this question on stack overflow. The problem was in appending a new JSON object to the existing JSON array: UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}') WHERE Condition = 1; JSON_MODIFY function should take the array value […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930