Category: Short and Sweet

I was talking to a friend of mine and they are learning some SQL and they said something that I have seen come up multiple times in learning SQL.

They said “Yeah, I need to study the join types more. They make sense to me but I want to be able to not reference my notes” and also “I don’t really get the point of a right join if your can do the same thing with a left join by just switching the table name.”

These are great points, and common questions that occur when first learning SQL.

Before I talk about LEFT/RIGHT JOINs, I wanted to mention that in my experience, idiomatic SQL does not contain RIGHT and OUTER joins, ignore them.

That might be contentious among folks, but unless you have a large amount of code written in a specific way where a RIGHT or OUTER join is the only thing that makes sense, fine, you’ve found the way, but generally you probably just want a subquery or CTE to encapsulate your logic and maintain “one way” of thinking about the problem.

The second piece of the puzzle that helped me a lot was understanding that all JOINS are just specialized versions if CROSS JOINs that help communicate your intent.

If we start with the following example, we’re making sure that every row in left and every row in right will touch. A classic CROSS JOIN.

SELECT *
FROM left AS l 
CROSS JOIN right AS r 

Now, a Cartesian product is rarely useful (unless you are making a numbers table!) but its the basis for the next step, a predicate (or WHERE clause) which allows us to restrict to only useful rows on the left hand side of the query.

SELECT *
FROM left AS l 
CROSS JOIN right AS r 
WHERE 
    l.id = r.left_id AND 
    r.left_id IS NOT NULL 

Some knowledgeable folks in the audience might be confused why I would filter on r.left_id IS NOT NULL (given the original condition) but some SQL dialects match on NULLs!

This CROSS JOIN is equivalent to a LEFT JOIN that looks like

SELECT *
FROM left AS l 
LEFT JOIN right AS r ON 
    l.id = r.left_id

The same applies to a RIGHT JOIN – just swap the main characters.

The INNER JOIN is even easier, it just makes sure there’s no unmatching values on both sides of the equation.

Now, this isn’t hugely different, but being able to call out LEFT LEFT LEFT LEFT INNER LEFT when you are reading through a long procedure is a critical part of reading SQL, swapping between RIGHT LEFT INNER all the time will absolutely make a difference in the mental model of your reviewer, and expect them to call you out if you use a RIGHT JOIN for anything but fun.

I hope you learned something from one of my early SQL insights, hopefully it can help you understand why there are multiple join types.

An interesting discussion/question in the SQL Community Slack today arose around how to implement blue/green deploys.

If you’re not familiar – blue/green refers to a deployment strategy with at least two hosts of your services, where you host in the green, deploy to the blue, and slowly drain the green to the blue until it becomes the green.

This has consequences in terms of keeping the lights on for both services, potentially rolling back the traffic to the green node (if the blue deployment fails some tests) and identifying things like dead code/data paths.

I was pondering how to answer the question in anything but the most generic way when this youtube video by Kevin Feasel was posted and it’s such a great resource I’m reposting it.

Thanks @reid and Kevin!

An interesting question asked by @danthesqlman in #sqlhelp (sqlcommunity.slack.com)

Having issues with Unicode in my XML, tried using a try_convert(varchar,fieldname) but not returning NULL.
Set it to have a test on my box, and weird results.
declare @n nvarchar(10) = N’ניקודות‎’
select try_convert(varchar(10),@n)
This doesn’t return NULL, but ?????????
I’m curious what would I be doing wrong, or how can i locate unicode within XML easily

And then when people suggested individual character shredding –

XML documents in a table over 200k rows, 2mb xml each, could take hours to parse 1 character at a time

There were a few suggestions, (my initial crap one was just dumping it to C#), but after a few jokes back and forth about how SQL Server was just returning normal question marks for TRY_CONVERT and how silly that was the idea came up… why not just:

1. Replace all question marks with something unique? (I suggested a GUID)
2. Run the conversion and then do a reverse replace, updating the data in place.
3. Profit!

For a simple code example…

DECLARE @magic_value UNIQUEIDENTIFIER = NEWID()
SELECT 
  TRY_CONVERT
  (
    VARCHAR(100),
    REPLACE(tar.name,'?',@magic_value)
  ) 
FROM target_table AS tar

Any new question marks that exist in the output would be characters that failed the conversion process.

The test ran in ten minutes instead of a few hours… great!

Also a fun followup on weird SQL Server homoglyph conversion issues in general – https://bertwagner.com/posts/how-unicode-homoglyphs-can-thwart-your-database-security/