JOINS in SQL Server

A JOIN clause is used to combine rows from two or more tables, based on a related column between these tables. The purpose of JOINS in SQL is to access data from various tables based on logical relationships. We can use joins to fetch matched or mismatched records from more than one table.

Types of JOINs

SQL Server supports different kinds of joins named as INNER JOIN, OUTER JOIN, SELF JOIN and CROSS JOIN. Each of this join type defines the method by which two tables are related in a query. OUTER JOINS can further be divided into LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

INNER JOIN: Retrieves a result set by merging rows from two or more tables with matching values. INNER keyword is optional to use.

LEFT OUTER JOIN: Retrieves a result set displaying all rows from the left table (first table). Left table (first table) is the table that is called before the Join clause, the main table that is being SELECTED and then the matched records from the right table (the tables joined). Right table (joined table) is the table that is called after the Join clause that is being joined with the preceding table(s). OUTER keyword is optional.

RIGHT OUTER JOIN: Retrieves a result set displaying all rows from the right table and the matched records from the left table (first table). Again, OUTER keyword is optional.

FULL OUTER JOIN: Retrieves a result set of all the matching rows from both the left & right table.

SELF JOIN: Retrieves a result set by joining the table to itself, linking rows within the same table.

CROSS JOIN: Retrieves a result set combining each row of the first table with each row of the second table displaying a set of rows where each record of one table is matched with each record from the other table.

Now let’s look at some examples for each of these types of Joins.

INNER JOIN:

Consider the following sample “Emp” table:

123

IJ2

Then, look the following sample “Dept” table:

Notice that the “DeptNo” column in the “Emp” table refers to the “DeptNo” in the “Dept” table. The relationship (common column) between the two sample tables above is the “DeptNo” column.

We write the following SQL statement (that contains an INNER JOIN (commonly referred to as JOIN), that will select records from both the tables matching values (for the same column) in both tables:

Query & Result Table

SELECT Emp.EmpNo, Emp.EName, Emp.Job, Emp.Mgr, Emp.HireDate, Emp.Sal, Emp.Comm, Emp.DeptNo, Dept.DeptNo
FROM Emp WITH (NOLOCK)
JOIN Dept WITH (NOLOCK) ON Emp.DeptNo = Dept.DeptNo

IJ3

LEFT OUTER JOIN:

Consider the same sample “Emp” table:

OJ1

Then, following the sample “Dept” table:

OJ2

We write the following SQL statement (that contains a LEFT (OUTER) JOIN or LEFT JOIN that will return the matching records from both the tables along with the unmatched rows from the left table:

Query & Result Table

SELECT * FROM Emp WITH (NOLOCK)
LEFT JOIN Dept WITH (NOLOCK) ON Emp.DeptNo = Dept.DeptNo

OJ3

RIGHT OUTER JOIN:

Consider the same sample “Emp” table:

ROJ1

Then, following the sample “Dept” table:

ROJ2

We write the following SQL statement (that contains a RIGHT (OUTER) JOIN or RIGHT JOIN that will return the matching records from both the tables along with the unmatched rows from the right table:

Query & Result Table

SELECT * FROM Emp WITH (NOLOCK)
RIGHT JOIN Dept WITH (NOLOCK) ON Emp.DeptNo = Dept.DeptNo

ROJ3

FULL OUTER JOIN:

Consider the samples “Emp” & “Dept” tables:

FJ1FJ2

We write the following SQL statement (that contains a FULL (OUTER) JOIN or RIGHT JOIN that will return the matching records from both the tables along with the unmatched rows from both the tables:

Query & Result Table

SELECT * FROM Emp WITH (NOLOCK)
FULL OUTER JOIN Dept WITH (NOLOCK) ON Emp.DeptNo = Dept.DeptNo

FJ3

SELF JOIN:

Consider the sample “Emp” table:

SJ1

We write the following SQL statement that will return the matching records from the table along with the matching records from the same table:

Query & Result Table

SELECT A.EName AS EName1, B.EName AS EName2, A.Job
FROM Emp A, Emp B
WHERE A.EmpNo <> B.EmpNo
AND A.Job = B.Job
ORDER BY A.Job

SJ2

CROSS JOIN:

Consider the samples “Dept” & “Dept1” tables:

CJ1

CJ2

We write the following SQL statement that will generate a combination of each record of the first table with each record of the second table.

Query & Result Table

SELECT * FROM Dept CROSS JOIN Dept1

CJ3

Hope this article helps you understand the concept(s). Please feel free to leave a comment or ask questions.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s