A Comprehensive Guide to SQL JOINS and Their Types

SQL Server JOINS :

• In real life, we store our data in multiple logical tables that are linked together by a common key value in relational databases like SQL Server, Oracle, MySQL, and others.

• As a result, we constantly need to get data from two or more tables into the desired output based on some conditions.

• We can quickly achieve this type of data in SQL Server using the SQL JOIN clause.

• This article gives a complete overview of JOIN and its different types with an example.

• The join clause allows us to retrieve data from two or more related tables into a meaningful result set.

• We can join the table using a SELECT statement and a join condition.

• It indicates how SQL Server can use data from one table to select rows from another table.

• In general, tables are related to each other using foreign key constraints.

• In a JOIN query, a condition indicates how two tables are related:

  • Choose columns from each table that should be used in the join. A join condition indicates a foreign key from one table and its corresponding key in the other table.

  • Specify the logical operator to compare values from the columns like =, <, or >.

Types of JOINS in SQL Server

SQL Server mainly supports four types of JOINS, and each join type defines how two tables are related in a query.

The following are types of join supports in SQL Server:

1. INNER JOIN

2. SELF JOIN

3. CROSS JOIN

4. OUTER JOIN

INNER JOIN

• This JOIN returns all records from multiple tables that satisfy the specified join condition.

• It is the simple and most popular form of join and assumes as a default join.

• If we omit the INNER keyword with the JOIN query, we will get the same output.

• The following visual representation explains how INNER JOIN returns the matching records from table1 and table2:

INNER JOIN Syntax

The following syntax illustrates the use of INNER JOIN in SQL Server:

SELECT columns    
FROM table1    
INNER JOIN table2 ON condition1    
INNER JOIN table3 ON condition2

Example:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid  
FROM Student  
INNER JOIN Fee  
ON Student.admission_no = Fee.admission_no;

SELF JOIN

• A table is joined to itself using the SELF JOIN.

• It means that each table row is combined with itself and with every other table row.

• The SELF JOIN can be thought of as a JOIN of two copies of the same tables.

• We can do this with the help of table name aliases to assign a specific name to each table's instance.

• The table aliases enable us to use the table's temporary name that we are going to use in the query.

• It's a useful way to extract hierarchical data and comparing rows inside a single table.

SELF JOIN Syntax

The following expression illustrates the syntax of SELF JOIN in SQL Server. It works the same as the syntax of joining two different tables. Here, we use aliases names for tables because both the table name are the same.

SELECT T1.col_name, T2.col_name...    
FROM table1 T1, table1 T2    
WHERE join_condition;

Example:

SELECT S1.first_name, S2.last_name, S2.city  
FROM Student S1, Student S2  
WHERE S1.id <> S2.iD AND S1.city = S2.city  
ORDER BY S2.city;

CROSS JOIN

• CROSS JOIN in SQL Server combines all of the possibilities of two or more tables and returns a result that includes every row from all contributing tables.

• It's also known as CARTESIAN JOIN because it produces the Cartesian product of all linked tables.

• The Cartesian product represents all rows present in the first table multiplied by all rows present in the second table.

• The below visual representation illustrates the CROSS JOIN.

• It will give all the records from table1 and table2 where each row is the combination of rows of both tables:

CROSS JOIN Syntax

The following syntax illustrates the use of CROSS JOIN in SQL Server:

SELECT column_lists    
FROM table1    
CROSS JOIN table2;

Example

We can demonstrate the CROSS JOIN using the following command:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid  
FROM Student  
CROSS JOIN Fee  
WHERE Student.admission_no = Fee.admission_no;

OUTER JOIN

• OUTER JOIN in SQL Server returns all records from both tables that satisfy the join condition.

• In other words, this join will not return only the matching record but also return all unmatched rows from one or both tables.

We can categories the OUTER JOIN further into three types:

• LEFT OUTER JOIN

• RIGHT OUTER JOIN

• FULL OUTER JOIN

LEFT OUTER JOIN

• The LEFT OUTER JOIN retrieves all the records from the left table and matching rows from the right table.

• It will return NULL when no matching record is found in the right side table.

• Since OUTER is an optional keyword, it is also known as LEFT JOIN.

The below visual representation illustrates the LEFT OUTER JOIN:

LEFT OUTER JOIN Syntax

The following syntax illustrates the use of LEFT OUTER JOIN in SQL Server:

SELECT column_lists  
FROM table1    
LEFT [OUTER] JOIN table2    
ON table1.column = table2.column;

Example

We can demonstrate the LEFT OUTER JOIN using the following command:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid  
FROM Student  
LEFT OUTER JOIN Fee  
ON Student.admission_no = Fee.admission_no;

RIGHT OUTER JOIN

• The RIGHT OUTER JOIN retrieves all the records from the right-hand table and matched rows from the left-hand table.

• It will return NULL when no matching record is found in the left-hand table.

• Since OUTER is an optional keyword, it is also known as RIGHT JOIN.

• The below visual representation illustrates the RIGHT OUTER JOIN:

RIGHT OUTER JOIN Syntax

The following syntax illustrates the use of RIGHT OUTER JOIN in SQL Server:

SELECT column_lists    
FROM table1    
RIGHT [OUTER] JOIN table2    
ON table1.column = table2.column;

Example

The following example explains how to use the RIGHT OUTER JOIN to get records from both tables:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid  
FROM Student  
RIGHT OUTER JOIN Fee  
ON Student.admission_no = Fee.admission_no;

FULL OUTER JOIN

• The FULL OUTER JOIN in SQL Server returns a result that includes all rows from both tables.

• The columns of the right-hand table return NULL when no matching records are found in the left-hand table.

• And if no matching records are found in the right-hand table, the left-hand table column returns NULL.

The below visual representation illustrates the FULL OUTER JOIN:

FULL OUTER JOIN Syntax

The following syntax illustrates the use of FULL OUTER JOIN in SQL Server:

SELECT column_lists    
FROM table1    
FULL [OUTER] JOIN table2    
ON table1.column = table2.column;

Example

The following example explains how to use the FULL OUTER JOIN to get records from both tables:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid  
FROM Student  
FULL OUTER JOIN Fee  
ON Student.admission_no = Fee.admission_no;