JOIN in SQL SERVER

JOIN

Join enable to retrieve data from two or more tables based on logical relationships between the tables.

--TABLE JOIN CONCEPT--

INNER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 INNER JOIN TB2 T2 ON T1.ID=T2.ID

LEFT OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

RIGHT OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

FULL OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID

CROSS JOIN

<no picture>

SELECT T1.*, T2.*

FROM TB1 T1 CROSS JOIN TB2 T2

--(both are equivalent)--

SELECT T1.*, T2.*

FROM TB1 T1 INNER JOIN TB2 T2 ON 1=1

LEFT OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T2.ID IS NULL

RIGHT OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T1.ID IS NULL

FULL OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T1.ID IS NULL OR T2.ID IS NULL 

SELF JOIN - (A REAL EXAMPLE)

SELECT T1.id, T1.name, T2.name AS manager_name

FROM employee T1 left outer JOIN employee t2

ON T1.managerId=T2.id