Article Preview

COLUMN

SQL CTE

Using Common Table Expressions

Issue: 12.6 (November/December 2014)
Author: Seth Verrinder
Author Bio: Seth is a software engineer who works on big data analytics. He worked with Xojo full-time for seven years as a consultant at BKeeney Software, Inc.
Article Description: No description available.
Article Length (in bytes): 5,535
Starting Page Number: 77
Article Number: 12609
Related Link(s): None

Excerpt of article text...

This column is about Common Table Expressions in SQL. The name sounds a bit intimidating but they're actually very easy to use and can dramatically simplify complex SQL code.

One of the things I love about SQL is that it gives you a lot of leverage. Once your information is in a database you can use SQL to pull out data and re-assemble it in ways that the original database designer never imagined.

A couple of months ago, I started on a project that involved a lot of exploratory data analysis. There was a large a amount of data in a record-keeping system and that data needed to be transformed so that we could calculate metrics we were after. The source database had hundreds of tables and we weren't sure at the start which tables would have the data that we needed. Using SQL was the obvious choice, but when you begin nesting joins and combining data from a dozen different tables, things start getting very hard to follow.

CTE

That's where CTEs come in. Basically a CTE is a temporary view that's only used for one query. First, a bit of review: in a SQL database you can create a view based on a select statement. To someone querying it, the view looks like an ordinary table. So, a view is like a saved query or you could even think of it as a function that you can call that runs the SELECT statement that you used to define it.

A CTE is the same thing but the view that it defines is temporary and can only be used in the same statement that defines it.

...End of Excerpt. Please purchase the magazine to read the full article.