UNION INTO

So, this appears to have worked:

SELECT *
INTO [COPD].[dbo].[COPDCombinedGlucoseResults]
FROM [COPD].[dbo].[COPDLabGlucoseResults]
UNION
SELECT *
FROM [COPD].[dbo].[COPDFlowsheetGlucoseResults];

The new table has 156558 rows

The LabGlucose table had 137417 distinct rows and the Flowsheet Glucose table had 72986 distinct rows, so I think that means there were 53845 matching rows.

Just for grins, and because this was my first real-live UNION query, I thought it would be interesting to do an INTERSECT query to see if the 53845 number holds water and to test my newbie understanding of these queries:

SELECT *
FROM [COPD].[dbo].[COPDLabGlucoseResults]
INTERSECT
SELECT *
FROM [COPD].[dbo].[COPDFlowsheetGlucoseResults];

Lo and Behold: 53845 rows. So if you add the results of a UNION query to the results of an INTERSECT query, you will get the same number of rows as both tables separately.

I read today about the UNION ALL query, and it appeared to be the version of the UNION query that included duplicate rows.

By this logic, a UNION query + an INTERSECT query = a UNION ALL query. I must test this.

The number of rows from each table add up to: 210403

SELECT *
FROM [COPD].[dbo].[COPDLabGlucoseResults]
UNIONĀ ALL
SELECT *
FROM [COPD].[dbo].[COPDFlowsheetGlucoseResults];

and we have a winner! 210403 rows.

I think I might possibly be falling in love with SQL. I’ve almost forgotten my garden.

The EXCEPT and INTERSECT clauses

I’d heard tell of the “EXCEPT clause,” but this is my first time seeing it. Isn’t it beautiful? I have wished before for such a function, but I didn’t expect it to look so clean on the page. In Access, I have jumped through a few hoops to achieve a result similar to what this appears to do.

20160615 EXCEPT and INTERSECT clauses

The INTERSECT Clause sounds pretty cool too. I think the equivalent of this definition (at least as far as data returned) in Access would be making inner joins to the fields you want and only pulling those fields down into the bottom.

20160615 Access INTERSECT equivalent

^ Here’s my attempt to represent my understanding of the INTERSECT if represented in Microsoft Access. But here’s the rub. If I were to write a SQL query for the above Access query, I think I would write it as follows:

20160615 Access INTERSECT equivalent back in SQL

This is a very different-looking construct. I went ahead and ran it both ways in SQL and got very different results. For the results of the query using the inner join, I got 72591 rows, and in the results using the INTERSECT query from the Microsoft training, I got a mere 238 rows. Looking closer, I notice that my INTERSECT query doesn’t have immediately obvious repeated values, and my Inner Join query does. I go back and add a “Group By” clause, and then my results match. In these queries using the AdventureWorks tables, both queries ran very quickly. I do not know how the dark underbelly of SQL Server may treat these two queries, so I don’t know if they are equivalent in CPU usage or just in data returned. Here are the two queries with the equivalent results, if anybody else is looking to play:

20160615 SQL Query Comparison
Both of these queries return 238 matching rows if using the AdventureWorks 2014 database.

And just to round out this post, I did go back into Microsoft Access, add the “Group By” and found 238 rows that way as well:

20160615 Access INTERSECT equivalen correctedt

All things being equal, I love the idea of the INTERSECT query.