Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr

JOINs Using Playing Cards

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 not null,
    Value as case NumericValue when 1 then 'Ace'
                               when 11 then 'Jack'
                               when 12 then 'Queen'
                               when 13 then 'King'
             else cast(NumericValue as varchar(10)) end,
    Suit varchar(10) not null,
    Color as iif(Suit in ('Hearts', 'Diamonds'), 'Red', 'Black')

    primary key clustered (NumericValue, Suit)
)

insert into #Cards (NumericValue, Suit)
select 
    NumericValue = (n % 13) + 1, 
    Suit = choose((n % 4) + 1, 'Hearts', 'Clubs', 'Diamonds', 'Spades')
from (select top 52 n = row_number() over (order by (select null)) 
      from sys.all_objects) a

if object_id('tempdb.dbo.#LeftSet') is not null drop table #LeftSet
select c.*
into #LeftSet
from #Cards c
inner join (select 1 union all select 1 union all select 3 union all select 4) b (val)
    on c.NumericValue = b.val
where c.Suit = 'Hearts'

if object_id('tempdb.dbo.#RightSet') is not null drop table #RightSet
select *
into #RightSet
from #Cards
where (NumericValue = 1 and Suit = 'Clubs')
    or (NumericValue = 2 and Suit = 'Spades')
    or (NumericValue in (11, 12) and Suit = 'Diamonds')

if object_id('tempdb.dbo.#MultiValueRightSet') is not null drop table #MultiValueRightSet
select *
into #MultiValueRightSet
from #Cards
where (NumericValue = 1 and Suit = 'Clubs')
    or (NumericValue = 2 and Suit = 'Spades')
    or (NumericValue in (3, 12) and Suit = 'Diamonds')

The Setup

The setup is pretty simple. Each card has a few properties (i.e. columns) including

  • Value
  • Suit
  • Color
Each card can be uniquely identified by its Suit and Value (i.e, the primary key fields).

Inner Join (Single Field)

This is probably the most common and easy to understand join. It returns a set of ordered pairs (L, R)  everywhere teat L.Value is the same as r.Value.

select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
inner join #RightSet r
    on l.Value = r.Value

Inner Join (Multiple Fields)

This just illustrates a join when multiple conditions are at play. You can see at least one condition matches for quite a few of the query below, but only one matches both.

select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
inner join #MultiValueRightSet r
    on l.Value = r.Value
        and l.Color = r.Color

Left Outer Join

Outer join in general can be though of in a couple ways. One way is that it alwasy return everything from the left table, whether it matches or not. If it doesn,t match, the right value is null. Alternatively, you can think of a left outer join as an inner join UNIONed with the unpaired values from the left table.

select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
left outer join #RightSet r
    on l.Value = r.Value

Right Outer Join

Right Outer Join is the same as a left outer join, just in reverse, It's all the paired values plus all the unpaired values from the right table.

select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
right outer join #RightSet r
    on l.Value = r.Value

Full Outer Join

If a left outer join is an inner join unioned with unpaired values from the left table and a right outer join is an inner join unioned with unpaired values from the right table, a Full Outer Join is a combination of both. i.e.
  • An inner join 
  • unpaired values from the left table (left outer join)
  • unpaired values from the right table (right outer join)
select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
full outer join #RightSet r
    on l.Value = r.Value

Cross Join

Cross Join, also sometimes referred to as a Cartesian Product takes no ON clause. Technically you can write one using a where clause (and in fact, back in the day, that's how joins were actually done), but in general that's not the best way to use a cross join.

A cross join pairs every element from one set with every element from another set. In the example below, we have a set with 4 elements cross joined with another set of 4 elements. You can see graphically below how that shakes out. Interestingly to note though, if you cross join a set with itself, the number of elements you'll get out are n^2 where n is the number of elements in the set.

select 
    LeftCard = l.Value + ' of ' + l.Suit,
    RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
cross join #RightSet r










Comments

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

Left-Padding Zeroes

Fixing Git "Unexpected Disconnect while reading sideband packet"