JOINS IN SQL SERVER

create tables and insert data into tables

CREATE TABLE dbo.Table1
(ID INT, Value VARCHAR(10))
INSERT INTO dbo.Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'


CREATE TABLE dbo.Table2
(ID INT, Value VARCHAR(10))
INSERT INTO dbo.Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'



Basic of joins:

INNER JOIN

This join returns rows when there is at least one match in both the tables.


Select * from Table1 t1 INNER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 inner join dbo.Table2 t2 on t1.ID=t2.ID


OUTER JOIN:

There are three different Outer Join methods.

LEFT OUTER JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

select * from Tabel1 t1 LEFT OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 left join dbo.Table2 t2 on t1.ID=t2.ID



RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

Select * from Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 right join dbo.Table2 t2 on t1.ID=t2.ID

FULL OUTER JOIN

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

select * from Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 Full outer join dbo.Table2 t2 on t1.ID=t2.ID

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

select * from Table1 t1 cross JOIN Table2 t2

Ex: select t1.*,t2.* from dbo.Table1 t1 cross join dbo.Table2 t2

Additional Notes related to JOIN:

/* LEFT JOIN - WHERE NULL */
This query using Outer Join and WHERE clause in join.
select * from Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.col1=t2.col1 WHERE t2.Col1 IS NULL

 Ex: select * from dbo.Table1 t1 left join dbo.Table2 t2 on t1.ID=t2.ID where t2.ID is null

/* RIGHT JOIN - WHERE NULL */

This query also using Outer Join and WHERE clause in join.

select * from Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.col1=t2.col2 WHERE t1.col1 IS NULL

Ex:select t1.*,t2.* from dbo.Table1 t1 right join dbo.Table2 t2 on t1.ID=t2.ID where t1.ID is null


/* OUTER JOIN - WHERE NULL */

This join will give all the results that were not present in Inner Join.

select * from Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.col1=t2.col1 WHERE t1.col1 IS NULL OR t2.col1 IS NULL

Ex:select t1.*,t2.* from dbo.Table1 t1 full outer join dbo.Table2 t2 on t1.ID=t2.ID where t1.ID is null or t2.ID is null