FRESHERS INTERVIEW QUESTIONS IN SQL SERVER JOINS CONCEPT

Most of the database Interview part  contains JOINS

TYPES OF JOINS IN SQL SERVER  & ITS Examples

• LEFT OUTER JOIN  
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN
• INNER JOIN

/*Create two tables*/
CREATE TABLE RightTable( ID [Int], Name Varchar(50));
GO
CREATE TABLE LeftTable(ID [INT], Amount[int]);
GO

/*Insert datas to the tables*/
INSERT INTO RightTable Values
(1,'man'),(2, 'sam'),(3,'jon'),(5,'joe'),(7,'kan')
GO
INSERT INTO LeftTable Values
(1,250),(2,550),(4,390),(6,874)
GO

/*LEFT JOIN*/
It returns all the rows in the Left side table and unmatched rows in the right side table will be "NULL"

SELECT *
FROM LeftTable
LEFT JOIN RightTable
ON LeftTable.ID=RightTable.ID

Result Set

/*RIGHT JOIN*/

It returns all the rows in the Right side table and unmatched rows in the Left side table will be "NULL"

SELECT *
FROM LeftTable
RIGHT JOIN RightTable
ON LeftTable.ID=RightTable.ID

Result Set




/*INNER JOIN*/
It returns only  common rows in the both the tables.
SELECT *
FROM  LeftTable
INNER JOIN RightTable
ON LeftTable.ID = RightTable.ID

Result Set



/*FULL JOIN*/
It returns all the rows from  both the tables.
SELECT *
FROM LeftTable
FULL OUTER JOIN RightTable
ON  LeftTable.ID = RightTable.ID

Result Set

/*CROSS JOIN*/

SELECT *
FROM LeftTable
CROSS JOIN RightTable

Result Set