Meera's : Joining Two Tables without any Common Column between them

[ALSO READ] Joins In Sql Server

We can achieve this by a script like below:

CREATE DATABASE SqlHintsDemo

GO

USE SqlHintsDemo

GO

--- Create Mentor Table and populate Mentor's data

CREATE TABLE Mentor (MentorId int, MentorName Varchar(40))

INSERT INTO Mentor (MentorId, MentorName) VALUES (100, 'Prabhu')

INSERT INTO Mentor (MentorId, MentorName) VALUES (200, 'Basavaraj')

INSERT INTO Mentor (MentorId, MentorName) VALUES (300, 'Sharan')

INSERT INTO Mentor (MentorId, MentorName) VALUES (400, 'Keerthi')

--- Create Mentee Table and populate Mentor's data

CREATE TABLE Mentee (MenteeId int, MenteeName Varchar(40))

INSERT INTO Mentee (MenteeId, MenteeName) VALUES (1, 'Anna')

INSERT INTO Mentee (MenteeId, MenteeName) VALUES (2, 'ShreeGanesh')

INSERT INTO Mentee (MenteeId, MenteeName) VALUES (3, 'Shashank')

--- USE CTE to get the expected results

;WITH MentorTable (CommonId, MentorId, MentorName) AS

(

    SELECT ROW_NUMBER() OVER(ORDER BY MentorId) AS CommonId,MentorId, MentorName 

    FROM Mentor

)

,MenteeTable (CommonId, MenteeId, MenteeName) AS

(

    SELECT ROW_NUMBER() OVER(ORDER BY MenteeId) AS CommonId,MenteeId, MenteeName 

    FROM Mentee

)

SELECT MR.MentorId,MR.MentorName,ME.MenteeId,ME.MenteeName

FROM MentorTable MR

    LEFT OUTER JOIN MenteeTable ME

        ON MR.CommonId = ME.CommonId

Meera's