Basic Join in SQL Server?

Step 1 :- Basic Join in  SQL Server

USE AdventureWorks

GO

CREATE TABLE table1

(ID INT, Value VARCHAR(10))

INSERT INTO Table1 (ID, Value)

SELECT 1,'One'

UNION ALL

SELECT 2,'Two'

UNION ALL

SELECT 3,'Three'

UNION ALL

SELECT 4,'Four'

UNION ALL

SELECT 5,'Five'

GO

CREATE TABLE table2

(ID INT, Value VARCHAR(10))

INSERT INTO Table2 (ID, Value)

SELECT 1,'One'

UNION ALL

SELECT 2,'Two'

UNION ALL

SELECT 3,'Three'

UNION ALL

SELECT 6,'Six'

UNION ALL

SELECT 7,'Seven'

UNION ALL

SELECT 8,'Eight'

GO

SELECT *

FROM Table1

SELECT *

FROM Table2

GO

USE AdventureWorks

GO

/* INNER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

INNER JOIN Table2 t2 ON t1.ID = t2.ID

GO

Note : 

Return only matching record from both the table.

/* LEFT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

GO

Note : 

Return all record from first table and matching record from second table and not matched record return null in second table.

/* RIGHT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

GO

Note : Return all record from second table and matching record from second table and not matched record return null in first table.

/* OUTER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

GO

Note : 

Return all matched and unmatched record from both the table. 

Unmatched record display null in both the table.

/* LEFT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t2.ID IS NULL

GO

Note : 

Return all record from first table and matching record from second table based on WHERE Clause where second table id value is null.

/* RIGHT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL

GO

Note : 

Return all record from second table and matching record from first table based on WHERE Clause where first table id value is null.

/* OUTER JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL OR t2.ID IS NULL

GO

Note : 

Return all record from both the table where both table id value is null.

/* CROSS JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

CROSS JOIN Table2 t2

GO

DROP TABLE table1

DROP TABLE table2

GO