The Windward Studio

Windward Blog Home

Common Table Expressions Make Life Easier

by
Posted on 08/25/2014

Please Share This

 

Learning basic SQL isn’t terribly difficult. Select fields from tables where my condition is met, and display them in this order. Awesome. 30 seconds and I’m done. Maybe I need a join, or I need to summarize with a group by clause. You start to get a little tricky with “having” vs. “where,” but it’s not too rough.

Writing complex queries with nesting frustrated me for a long time, though. When you’re not good at writing SQL, figuring out the syntax so that you’re selecting from the result set of another select is painful. Figuring out parentheses, messing around with where you need aliases, which where clause goes with what… Yeah. Didn’t care for it.

Enter the Common Table Expression

And then as I started learning a bit more, I met my good friend the Common Table Expression (CTE).

Now, the biggest benefit of a CTE is that you can define it recursively. Apparently. That’s not something I have need for. All I need is to be able to read what I’m writing and find problems in it. CTEs make it so much easier.

Here’s a simple example:

becomes

Is it longer? Yeah, but (at least in my mind) it’s a lot clearer where each select is happening.

Subqueries are just messier to me. Maybe they’re fine for you, and that’s great. Fantastic. But I wish I’d known about these far, far sooner than I actually found them. It would have saved a lot of time and effort when I was nesting three levels down and trying to join across the derived tables and whatever foolish and naïve shenanigans I was getting myself into.

For the advanced SQL gurus out there, this is trivial, and I’m sure I’m not totally accurate on the benefits and drawbacks of each method. There are a lot of us out there still learning, though, and hopefully I can help one or two people get through something they’ve been stuck learning for a while.

Better yet, maybe you can help those of us still learning SQL. Got a thought on the benefits and drawbacks? Speak up on The Windward Studio Blog!

Please Share This



Author: Logan Miles

Having learned computers from “Hackers” and sales from “Thank You For Smoking,” Logan bridges the gap between the technical and non-technical. As a sales engineer, he enjoys not only solving problems but relating them in some way to the (mostly awful) movies he watches while recovering from skiing or hiking trips, depending on the season.

Other posts by