How do you generate a random value from another table’s column in SQL?

So far I’ve been able to get this query that delivers these results:

But the problem I still have is that these results are meant to be random for EVERY ROW instead of the value is constant for all the rows.

So my question is how do you make all rows random instead of the whole batch having a fixed random value?

SELECT firstname
    ,lastname
    ,date_of_birth
    ,corp_name
    ,country_name
FROM person
CROSS JOIN corporation
WHERE corp_name IN (
        SELECT TOP 1 corp_name
        FROM corporation
        ORDER BY newid()
        )

Answer

The problem is over-optimization. The subquery is evaluated only once, even though it has non-deterministic logic. I view this as a bug but I’m guessing that Microsoft has some convoluted reasoning for thinking this is not a bug.

In any case, I find that a correlation clause usually fixes this:

select p.firstname, p.lastname, p.date_of_birth,
       c.corp_name, c.country_name
from person p cross apply
     (select top (1) c.*
      from corporation c
      where p.firstname <> c.corp_name  -- or whatever
      order by newid()
     ) c;

Note that this uses cross apply as a further simplification.