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:
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
LEFT OUTER JOIN:
Consider the same sample “Emp” table:
Then, following the sample “Dept” table:
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
RIGHT OUTER JOIN:
Consider the same sample “Emp” table:
Then, following the sample “Dept” table:
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
FULL OUTER JOIN:
Consider the samples “Emp” & “Dept” tables:
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
SELF JOIN:
Consider the sample “Emp” table:
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
CROSS JOIN:
Consider the samples “Dept” & “Dept1” tables:
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
Hope this article helps you understand the concept(s). Please feel free to leave a comment or ask questions.
Big Data and SQL 
One comment