articles

Home / DeveloperSection / Articles / JOIN in SQL Server

JOIN in SQL Server

AVADHESH PATEL4220 18-Aug-2012

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. JOIN keyword used in an SQL statement to query data more than one table.

Type of Join

1.      Self Join

2.      Join or Equi Join or Inner Join

3.      Outer Join

4.      Cross Join

For Demonstration I’m created two tables.

-- CTREATED TABLE 1
CREATE TABLE table1
(
[ID] INT,
[Value] VARCHAR(10)
)
--INSERT VALUES INTO TABLE 1
INSERT INTO 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'

 Screen Shot of Table1

JOIN in SQL Server

-- CREATE TABLE 2
CREATE TABLE table2
(
[ID] INT,
[Value] VARCHAR(10)
)
 
--INSERT VALUES INTO TABLE 2
INSERT INTO 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'

 Screen Shot of Table1

JOIN in SQL Server

SELF JOIN

A SELF JOIN is a query in which a table is joined (compared) to itself.  Self-joins are used to compare values in a column with other values in the same column in the same table.

Syntax

SELECT [COLUMN_NAME] || [ALL] FROM <TABLE_NAME_ 1> <TABLE_NAME_ALIAS_1>, <TABLE_NAME_ 1> <TABLE_NAME_ALIAS_2> WHERE <condition to match common column name> 
Example
/* Self Join*/
SELECT t1.ID, t1.[Value] FROM Table1 t1, Table1 t2 WHERE t1.ID = t2.ID

 Screen Shot of Self Join

JOIN in SQL Server

INNER JOIN

INNER JOIN OR EQUI JOIN OR JOIN returns rows when there is at least one match in both the tables.

Syntax
SELECT [COLUMN_NAME] || [ALL] FROM <TABLE_NAME_ 1> <TABLE_NAME_ALIAS>
INNER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <condition to match common column name>

Example
/* JOIN OR EQUI JOIN OR INNER JOIN */
SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID

Screen Shot of Inner Join

JOIN in SQL Server

OUTER JOIN
There are three different Outer Join methods.

       I.             LEFT OUTER JOIN

    II.             RIGHT OUTER JOIN

 III.             FULL OUTER JOIN

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.

Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
LEFT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >

 Example
/* LEFT OUTER JOIN */
SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID

 Screen Shot of Left Outer Join

JOIN in SQL Server

RIGHT OUTER JOIN:

This join returns all the rows from the right 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.

Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
RIGHT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >

 Example
/* RIGHT OUTER JOIN */
SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
  Screen Shot of Right Outer Join

JOIN in SQL Server

FULL OUTER JOIN:

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

Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
FULL OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >

 Example
/* FULL OUTER JOIN */
SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

 Screen Shot of Full Outer Join

JOIN in SQL Server

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. That is every element of column of first table is associated with every element of column of second table.

Syntax:
SELECT [COLUMN NAME | ALL COLUMN] FROM <FIRST_TABLENAME> <FIRST_TABLENAME_ALIAS>
CROSS JOIN <SECOND_TABLENAME> <SECOND_TABLENAME_ALIAS> ON <SQL CLAUSE|KEYWORD|CONDITION>
  Example
/* CROSS JOIN */
SELECT * FROM Table1 t1 CROSS JOIN Table2 t2

 Screen Shot of Cross Join

JOIN in SQL Server

Using OUTER JOIN and WHERE clause in Join

This is return NULL column values.

LEFT OUTER JOIN with WHERE NULL

Return left table’s NULL values which not common in right table.

/* LEFT OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL
  Screen Shot

JOIN in SQL Server

 RIGHT OUTER JOIN with WHERE NULL

Return right table’s NULL values which not common in left table.

/* RIGHT OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL

 Screen Shot

JOIN in SQL Server

FULL OUTER JOIN with WHERE NULL

Return NULL values from left and right table which not common.

/* FULL OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL OR t2.ID IS NULL

 Screen Shot

JOIN in SQL Server



Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By