Cross Joining a Table Created on the Fly Using Union All

Today I was working on a report to reconcile data between two systems that will be used to validate parallel testing as we work to move the new system into production. I mention this to provide you with some context around the two techniques I employed in one of the SQL queries I wrote that is the basis of this post. Sometimes it’s a very fine line between being clever and being obtuse, especially when you are writing SQL. Since this report will become obsolete once the new system goes to production and I will most likely be the only person that has to maintain it for a short period of time, I look at these techiques as more clever than obtuse in these circumstances.

Creating and dropping temp tables in batches (not stored procs) can be a hassle in SQL Server T-SQL. In Oracle, you only have global temporary tables and you have to deal with the select limitations of PL/SQL. Sometimes, you need a table but you don’t want to explicitly create it or you can’t because you are querying another system over which you have no control. You can create the table “on the fly” using a temporary view and the UNION ALL clause. For example, I frequently need to transform a block of time into hourly blocks and I don’t have a permanent table available and I don’t want to deal with temp tables. I can create the table I need on the fly like this:

(SELECT Hour = 0 UNION ALL SELECT Hour = 1 UNION ALL ... SELECT HOUR = 23) H

In Oracle, you would have to specify the FROM clause (good ol’ DUAL). You also can’t use the = construct but you get the idea. In my experience the performance of this “table on the fly” is pretty good. Obviously, if you are working in a stored proc, you can create a temp table in T-SQL or a TABLE varable in PL/SQL to achieve the same result. This technique is nice when you can’t use a stored procedure. It’s also useful if you only need a few rows once (say four or less). With less than four rows, it’s easier to create the table using a temporary view of UNION ALLs than it is to create the temp table or table variable.

Generally, you want to avoid CROSS JOINs (aka the dreaded Cartesian Product). However, there are many legitimate uses of CROSS JOIN. First, let me state that CROSS JOIN provides one of the most compelling arguments for using the ANSI-92 join syntax. If you see “CROSS JOIN” in a query, you can be fairly certain that the author of the query wanted a Cartesian product. If you see a FROM clause with a table that has no conditions specified in the WHERE clause, how certain can you be that the author intended a Cartesian product?

I digress. In the query I was developing today I was comparing schedules in two systems. In one of the systems, the schedule only included one side of a pair of transactions that offset each other. In the other system, both rows were there. I didn’t want the missing row from the one system to show up as a variance. Luckily, the existing query was already using a UNION to pick up this special case. I used a CROSS JOIN of a “table on the fly” to create the other row for me:

...
UNION (
SELECT
CASE WHEN D.Direction = 1 THEN 'BUY' ELSE 'SELL' END TransactionType,
A.Volume * D.Direction Volume
FROM OneSided A
CROSS JOIN (SELECT Direction = 1 UNION ALL Direction = -1) D
)
...

As you can see, I created the second row that I needed to make this system’s schedule match the other system’s schedule with just one line of SQL and minor modifications to the SELECT clause.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: