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

Your Reminder Not To MERGE

Kevin Wilkie points out the numerous problems with the MERGE operator: Now, when I last posted, I’m sure you thought I was done talking about the MERGE statement. You are so wrong, compadre! One more post is absolutely needed! There are a few issues with the MERGE statement. Well, as of this writing, there are 361 possible issues according […]

Read More

NULL Replacement In SQL Server And Oracle

Daniel Janik shows a pair of non-standard functions you can use to replace NULL values: It’s Wednesday and that means another SQL/Oracle post. Today we’ll be discussing NULL Values, which can sometimes be a real pain. Don’t worry though there’s a simple solution. Simply replace the NULL value with another. Comparing a column with NULL […]

Read More

Categories

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