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

Multiple Mentions Of A Column In An UPDATE Statement

Doug Lane walks us through various scenarios with updates including the same column multiple times: An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?” Yes and no. It depends on what you mean by “use”. Read on to see what Doug means.

Read More

Using AT TIME ZONE In SQL Server

Randolph West looks at the AT TIME ZONE clause when working with a specific time zone in SQL Server: The time zone name is taken from a list maintained in the following Windows registry hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones Note: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry […]

Read More

Categories

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