SQL SERVER – Relating Unrelated Tables – A Question from Reader

I often get emails from my blog readers asking various types of questions. Here is one of the interesting questions from a reader about Relating Unrelated Tables. Let us go over it in detail.

Users have two tables Table_A and Table_B. I have shown the output of both the tables in the following image. Users want to relate both of the unrelated tables and display output in the format which is displayed right below it. The question is how to relate two unrelated tables and generate the output.

I personally do not understand why there is such a requirement to relate unrelated tables. If you look at the output, the requirement is to get all rows from the first table and have a name from the second table.

Let us generated two tables – Table_A and Table_B.

USE TEMPDB
GO
--
CREATE TABLE TABLE_A
(
A_ID INT
)
GO
INSERT INTO TABLE_A (A_ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
GO
--
CREATE TABLE TABLE_B
(
B_NAME VARCHAR(100)
)
GO
INSERT INTO TABLE_B (B_NAME)
SELECT 'Mike' UNION ALL
SELECT 'Jim' UNION ALL
SELECT 'Roger'
GO

Let us see how we can generate the requested resultset with the help of row_number() function.

SELECT T1.A_ID,COALESCE(T2.B_NAME,'') AS B_NAME FROM TABLE_A AS T1 LEFT JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO,B_NAME FROM TABLE_B
) AS T2 ON T1.A_ID=T2.SNO

The idea is to generate serial number “as is” without ordering by any column and join it with the first table. Note that if the first table does not have serial numbers and if there are gaps you need to use the row_number function on both the tables and join them.

I do not see any real-time usage for these type of questions but might be good exercise if you are learning T-SQL.

Reference: Pinal Dave (https://blog.sqlauthority.com)