I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time (the expected result). However, when I run it with an INNER JOIN, I get a varying number of rows -- sometimes two, sometimes eight.

Logically, there shouldn't be any difference because I know rows with database_ids 1-4 exist in sys.databases. And because we're selecting from the random number table with four rows (as opposed to joining to it), there should never be any more than four rows returned.

This happens in both SQL Server 2012 and 2014. What is causing the INNER JOIN to return varying numbers of rows?

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT rando.RandomNumber, d.database_id
FROM rando
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id

I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time (the expected result). However, when I run it with an INNER JOIN, I get a varying number of rows -- sometimes two, sometimes eight.

Logically, there shouldn't be any difference because I know rows with database_ids 1-4 exist in sys.databases. And because we're selecting from the random number table with four rows (as opposed to joining to it), there should never be any more than four rows returned.

This happens in both SQL Server 2012 and 2014. What is causing the INNER JOIN to return varying numbers of rows?

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id;


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT r.RandomNumber, d.database_id
FROM rando AS r
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id;