A Comprehensive Guide to SQL JOINS and Their Types
Day 50 of 100: introduction to SQl Joins
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:
INNER JOIN
SELF JOIN
CROSS JOIN
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;