Query Rewriting and Validating

Unit testing

SQL is code, so it needs to be tested. The general approach to unit testing applies beautifully to SQL: given a known input, a query should always return the same desired output. That allows us to change our query spelling at will and still check that the alternative still passes our tests.

Examples of query rewriting would include in-lining common table expressions as subqueries, expanding or branching in a where clause as UNION ALL branches, or maybe using a window function rather than complex juggling with subqueries to obtain the same result. What we mean here is that there are a lot of ways to rewrite a query while keeping the same semantics and obtaining the same result.

Here’s an example of a query which will be rewritten: