Formatting Clear and readable SQL queries

Question

I'm writing some SQL queries with several subqueries and lots of joins everywhere, both inside the subquery and the resulting table from the subquery.

We're not using views so that's out of the question.

After writing it I'm looking at it and scratching my head wondering what it's even doing cause I can't follow it.

What kind of formatting do you use to make an attempt to clean up such a mess? Indents perhaps?

Answer 1

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

Example:

WITH    cubed_data AS (      SELECT          dimension1_id,         dimension2_id,         dimension3_id,         measure_id,         SUM(value) value      FROM         source_data      GROUP BY         CUBE(dimension1, dimension2, dimension3),         measure   ),    dimension1_label AS(      SELECT          dimension1_id,         dimension1_label      FROM          labels       WHERE          object = 'dimension1'   ), ... SELECT    * FROM     cubed_data   JOIN dimension1_label USING (dimension1_id)   JOIN dimension2_label USING (dimension2_id)   JOIN dimension3_label USING (dimension3_id)   JOIN measure_label USING (measure_id) 

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH works at least on current versions of Postgres, Oracle and SQL Server

Answer 2

Boy is this a loaded question. :) There are as many ways to do it right as there are smart people on this site. That said, here is how I keep myself sane when building complex sql statements:

select     c.customer_id    ,c.customer_name    ,o.order_id    ,o.order_date    ,o.amount_taxable    ,od.order_detail_id    ,p.product_name    ,pt.product_type_name from     customer c inner join     order o     on c.customer_id = o.customer_id inner join     order_detail od     on o.order_id = od.order_id inner join     product p     on od.product_id = p.product_id inner join     product_type pt     on p.product_type_id = pt.product_type_id where     o.order_date between '1/1/2011' and '1/5/2011' and     (         pt.product_type_name = 'toys'      or         pt.product_type_name like '%kids%'     ) order by     o.order_date    ,pt.product_type_name    ,p.product_name 

If you're interested, I can post/send layouts for inserts, updates and deletes as well as correlated subqueries and complex join predicates.

Does this answer your question?