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

Deferred Name Resolution In SQL Server

Kendra Little explains the concept of deferred name resolution in SQL Server: In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) – CREATE OR ALTER PROCEDURE #test AS IF 1=0 EXECUTE dbdoesnotexist.dbo.someproc; GO The database dbdoesnotexist does NOT exist, but I’m […]

Read More

T-SQL Join Delete

Steve Stedman walks us through a bit of T-SQL proprietary syntax: 1 2 3 DELETE t2 FROM [dbo].[Table1] t1 INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id; Names have been changed to protect the innocent. In the above delete statement which table will have rows deleted from it? A: Table1 B: Table2 C: Both Table1 […]

Read More

Categories

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