I was tasked to create a query for an accounting journal entry from a transaction table with only amounts indicated, so if it’s a journal entry there should be a credit and debit but most of the other values will be similar, so I thought of extracting 2 rows from a single row through SQL query. Sounds confusing? I Googled for it and I cant find any results so I will create one, most of the results I have seen is combining multiple rows into one.
To make it simple here is a sample
For example I have a Table Called Sample Table and it contains the following records
| ID | FirstName | LastName |
| 1 | Anna | Gates |
| 2 | John | Doe |
| 3 | Joe | Bloggs |
| 4 | Raj | Kumar |
Now you are tasked to create 3 lines for each record so it will show as
| ID | FirstName | LastName | ItemNumber | ItemDescription |
| 1 | Anna | Gates | 1 | Item 1 |
| 1 | Anna | Gates | 2 | Item 2 |
| 1 | Anna | Gates | 3 | Item 3 |
| 2 | John | Doe | 1 | Item 1 |
| 2 | John | Doe | 2 | Item 2 |
| 2 | John | Doe | 3 | Item 3 |
| 3 | Joe | Bloggs | 1 | Item 1 |
| 3 | Joe | Bloggs | 2 | Item 2 |
| 3 | Joe | Bloggs | 3 | Item 3 |
| 4 | Raj | Kumar | 1 | Item 1 |
| 4 | Raj | Kumar | 2 | Item 2 |
| 4 | Raj | Kumar | 3 | Item 3 |
Now how do you achieve it? There are a lot of ways you can do it but we are looking for the best way which means the most efficient query cost.
So here are some methods.
1. CROSS JOIN
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, Extender.ItemNumber, Extender.ItemDescription FROM SampleTable CROSS JOIN (SELECT 1 AS ItemNumber, 'Item 1' AS ItemDescription UNION ALL SELECT 2 AS ItemNumber, 'Item 2' AS ItemDescription UNION ALL SELECT 3 AS ItemNumber, 'Item 3' AS ItemDescription) AS Extender;
2. UNION
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 1 AS ItemNumber, 'Item 1' AS ItemDescription FROM SampleTable UNION ALL SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 2 AS ItemNumber, 'Item 2' AS ItemDescription FROM SampleTable UNION ALL SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 3 AS ItemNumber, 'Item 3' AS ItemDescription FROM SampleTable;
3. WITH
WITH ExtendedTable(ID, FirstName, LastName) AS (SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName FROM SampleTable) SELECT *, 1 AS ItemNumber, 'Item 1' AS ItemDescription from ExtendedTable UNION ALL SELECT *, 2 AS ItemNumber, 'Item 2' AS ItemDescription from ExtendedTable UNION ALL SELECT *, 3 AS ItemNumber, 'Item 3' AS ItemDescription from ExtendedTable
They all show the same results but which is better?
Here are my initial thoughts
I thought the CROSS JOIN and WITH will have the same performance initially as I thought the WITH would have stored the Query “SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName FROM SampleTable” in the memory and just reuse it when performing the bottom select but not, it will still do a clustered index scan everytime a Select is performed on the Extended Table. So CROSS JOIN will be the best as it does a clustered index scan once, and another draw back on using the WITH is that you can only use it on a SQL 2005 and above as it’s a Common Table Expressions.
Here is the execution plan for each to have a better view of what’s happening on each query
