Towering Queries

A few co-workers asked me to explain some of the nuances of different join types (outer joins in particular). I've always reached for Venn diagrams in the past, but I was trying to think of a better way to get the information across. Whether or not I succeeded, I'll let you be the judge. I'm not going to do much in the way of explaining each of the diagrams, but I will label each diagram. I also included a diagram at the end of a common trick for inserting only rows which don't already exist in the target table (using a left outer join, and checking where the right values are null). Please feel free to use this code and the diagrams if you think they're useful. If you do, it would be nice to credit me with them, but I'm hardly going to get mad if you don't. I'd much rather there just be good training materials out there. The Code if object_id('tempdb.dbo.#Cards') is not null drop table #Cards create table #Cards ( NumericValue tinyint n...

Momma sed don't name constraints on temp tables. But why? How SQL Makes Temp Tables Globally Unique   When you first build a temp table, SQL stores the definition of that temp table away  and every subsequent time you create it, it's actually a clone of that one, original table definition. To allow each individual connection to create a clone of the table, it has to do something about the name so that it won't collide with itself. Let's see what that looks like if object_id ( 'tempdb.dbo.#test' ) is not null drop table #test create table #test (     Id int primary key clustered ) Here, I've created a pretty basic temp table, with an unnamed, primary key on the table. Lets look at the meta data in information_schema (you can use system catalogs also, but for illustration purposes, it's a little easier for me to use information_schema here).     select table_name     from tempdb . information_s...