SQL join in SQL server Or Types of Join in SQL Server or Different Types of SQL Joins or SQL Server Join Example

CREATE TABLE #table1

(

  ID INT,

  Value VARCHAR(10)

)

INSERT INTO #table1 (ID, Value) VALUES(1,'One')

INSERT INTO #table1 (ID, Value) VALUES(2,'Two')

INSERT INTO #table1 (ID, Value) VALUES(3,'Three')

INSERT INTO #table1 (ID, Value) VALUES(4,'Four')

INSERT INTO #table1 (ID, Value) VALUES(5,'Five')

  

CREATE TABLE #table2

(

  ID INT,

  Value VARCHAR(10)

)

INSERT INTO #table2 (ID, Value) VALUES(1,'One')

INSERT INTO #table2 (ID, Value) VALUES(2,'Two')

INSERT INTO #table2 (ID, Value) VALUES(3,'Three')

INSERT INTO #table2 (ID, Value) VALUES(6,'Six')

INSERT INTO #table2 (ID, Value) VALUES(7,'Seven')

INSERT INTO #table2 (ID, Value) VALUES(8,'Eight')

SELECT *

FROM #table1

SELECT *

FROM #table2

/* INNER JOIN OR JOIN*/

• INNER JOIN Or JOIN returns the matching records from both the tables (#table1 and #table2).

• INNER keyword is optional instead of using the full qualified keyword you can also use JOIN keyword.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

INNER JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/* LEFT JOIN */

• LEFT JOIN returns all the records from the LEFT table (#table1) and matching record from the RIGHT table (#table2).

• Unmatched records from the RIGHT table (#table2) returns NULL.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

LEFT JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/* RIGHT JOIN */

• RIGHT JOIN returns the all records from the RIGHT table (#table2) and matching records from the LEFT table (#table1).

• Unmatched record from the LEFT table (#table1) returns NULL.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

RIGHT JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/*LEFT OUTER JOIN */

• In LEFT OUTER JOIN Outer keyword is optional.

• LEFT OUTER JOIN works like a LEFT JOIN.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

LEFT OUTER JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/*RIGHT OUTER JOIN */

• In RIGHT OUTER JOIN Outer keyword is optional.

• RIGHT OUTER JOIN works like a RIGHT JOIN.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

RIGHT OUTER JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/*FULL OUTER JOIN */

• FULL OUTER JOIN returns all the records from the both tables (#table1 and #table2).

• Unmatched record from both  table returns NULL

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

FULL OUTER JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

/*SELF JOIN */

• SELF JOIN is used to join a table to itself with difference alias.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

JOIN #table1 tbl2 ON tbl1.ID = tbl2.ID

/* CROSS JOIN */

• CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables.

• In General word each row from Left table multiplied by Number of Rows in Right table.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

CROSS JOIN #table2 tbl2

/* LEFT JOIN - WHERE NULL */

• Returns the NULL records from the RIGHT table.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

LEFT JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

WHERE tbl2.ID IS NULL

/* RIGHT JOIN - WHERE NULL */

• Returns the NULL records from the LEFT table.

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

RIGHT JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

WHERE tbl1.ID IS NULL

/* OUTER JOIN - WHERE NULL */

• Returns the NULL records from the both tables (#table1 and #table2).

SELECT tbl1.*,tbl2.*

FROM #table1 tbl1

FULL OUTER JOIN #table2 tbl2 ON tbl1.ID = tbl2.ID

WHERE tbl1.ID IS NULL OR tbl2.ID IS NULL

DROP TABLE #table1

DROP TABLE #table2