SQL random name generator not inserting first and last name in the same row

Question

I managed to create a simple query that selects a random first and last name and inserts them into a result table. I wanted to create something I could interchange with the various tests I run where I have to manufacture a lot of data. Here is the code (I only included 5 first and last names each for simplicity purposes):

SELECT 
    FirstName, LastName
FROM
    (SELECT TOP 1 
         FirstName 
     FROM 
         (SELECT 'John' AS FirstName 
          UNION SELECT 'Tim' AS FirstName 
          UNION SELECT 'Laura' AS FirstName
          UNION SELECT 'Jeff' AS FirstName
          UNION SELECT 'Sara' AS FirstName) AS First_Names 
     ORDER BY NEWID()) n1
FULL OUTER JOIN 
    (SELECT TOP 1
         LastName 
     FROM (SELECT 'Johnson' AS LastName 
           UNION SELECT 'Hudson' AS LastName 
           UNION SELECT 'Jackson' AS LastName
           UNION SELECT 'Ranallo' AS LastName
           UNION SELECT 'Curry' AS LastName) AS Last_Names 
     ORDER BY NEWID()) n2 ON [n1].FirstName = [n2].LastName
WHERE 
    n1.FirstName IS NOT NULL OR n2.LastName IS NOT NULL

Here are the results:

FirstName LastName 
NULL      Hudson
John      NULL

I want the results to return one row with a first and last name randomly generated so that each row would have a complete name (no NULL values). I'm sure it's something simple I am overlooking.

Answer 1

The problem is your join. This is how you can do it:

SELECT FirstName, LastName
FROM
(SELECT TOP 1 FirstName 
FROM (SELECT 'John' AS FirstName 
UNION SELECT 'Tim' AS FirstName 
UNION SELECT 'Laura' AS FirstName
UNION SELECT 'Jeff' AS FirstName
UNION SELECT 'Sara' AS FirstName) AS First_Names ORDER BY NEWID())n1
CROSS JOIN 
(SELECT TOP 1 LastName 
FROM (SELECT 'Johnson' AS LastName 
UNION SELECT 'Hudson' AS LastName 
UNION SELECT 'Jackson' AS LastName
UNION SELECT 'Ranallo' AS LastName
UNION SELECT 'Curry' AS LastName) AS Last_Names ORDER BY NEWID())n2