Do you use RIGHT JOINs? I myself rarely use a RIGHT JOIN, I think in the last 17 years or so I have only used a RIGHT JOIN once or twice. I think that RIGHT JOINs confuse people who are new to databases, everything that you can do with a RIGHT JOIN, you can also do with a LEFT JOIN, you just have to flip the query around
So why did I use a RIGHT JOIN then?
Well the answer is in the image below
That is right..it was pure laziness, if you ever met a programmer, you will find out pretty quickly that programmers are pretty lazy. I guess lazy is not the right word to use here, it is better to call programmers efficient or people who don't want to reinvent the wheel.
So what actually happened? I was checking some data in a table, I already had written the following query
SELECT number
FROM SomeTableWithIDs c
Now I just needed to see how many numbers were not used
So what did I do, I just continued below the query I already had, I ended up with this
SELECT number
FROM SomeTableWithIDs c
RIGHT JOIN(SELECT number
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 0 and 255) x on c.SomeID = x.number
WHERE c.SomeID is null
Let's take a look at this again with some DDL and DML you can run
First create the following table, it has only one column, this column has a tinyint data type (don't ask why it is tinyint, this is the reason I had to take a look at how many unused IDs we had to begin with)
CREATE TABLE SomeTableWithIDs (SomeID tinyint not null )
ALTER TABLE SomeTableWithIDs
ADD CONSTRAINT PK_SomeTableWithIDs PRIMARY KEY CLUSTERED (SomeID)
Now that we have the table created, let's populate it with 250 rows
INSERT SomeTableWithIDs
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY s.ID)
FROM sys.sysobjects s
CROSS JOIN sys.sysobjects s2
Let's delete a couple of rows
DELETE SomeTableWithIDs WHERE SomeID IN (2,4,6,11)
Running the RIGHT JOIN query will give us the missing rows
SELECT number
FROM SomeTableWithIDs c
RIGHT JOIN(SELECT number
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 0 and 255) x on c.SomeID = x.number
WHERE c.SomeID is null
Here is the output
0
2
4
6
11
251
252
253
254
255
Now you have to realize that this was just an ad-hoc query, if I were to put this in a proc or script, I would have done it like this
SELECT number
FROM master..spt_values s
WHERE type = 'p'
AND number BETWEEN 0 and 255
AND NOT EXISTS (SELECT SomeID
FROM SomeTableWithIDs c
WHERE c.SomeID = s.number)
That query is less confusing for beginners than a RIGHT JOIN
