SQL tables allow the user to store more than 30 types of data using different attributes. And in order to gain insight and fetch useful information data is extracted from more than just one table. Different categories of Join in SQL, allow you to combine data from multiple tables, as per your requirement.
What Is Join in SQL?
There are many categories of joins in SQL that let users combine rows from two or more tables based on different types of conditions, according to our requirement. These different types of Join in SQL are explained thoroughly below.
Different Types of Join in SQL
SQL gives you an option of choosing from the following seven types of joins:
Natural Join
Used to join tables on the basis of a common column
Cartesian Join
Returns the cartesian product of tables’ rows
Inner Join
The result table consists of only the common rows
Left Outer Join
The result table contains all the rows from the first table
Right Outer Join
The result table contains all the rows from the second table
Full Outer Join
The result table contains all the rows from both the tables
Self Join
Used to join a table to itself
Let’s get a more in-depth insight into all of these Joins in SQL.
Natural Join
This join is used to combine rows of tables based on columns having the same name and data type in both the tables. The common columns only appear once in the result of this join.
Natural join can be used to combine two or more tables, and the syntax of it is as follows:
SELECT column_1, column_2,...column_n
FROM table_1
NATURAL JOIN table_2;If you want to perform a natural join on the entire table you can use the following syntax:
SELECT * FROM table_1
NATURAL JOIN table_2;For example, if there’s a table “Employee”:
And you want to Natural Join it with the following table “Department”:
You must use the following query to join these two:
SELECT * FROM Employee
NATURAL JOIN Department;Here’s what the result looks like:
As you can see, the join was performed based on the common column “EmployeeID”, and the rows that had the same value for this column in both the tables, have been joined.
Cartesian Join
Cartesian Join also known as the Cross Join, returns the cartesian product of the tables being joined, which is the combination of every row of one table with every row of another table. For example, if table A contains 20 rows and table B consists of 30 rows, the Cross Join of these tables will result in a table will containing 20*30 (600) rows.
The syntax of this join is as follows:
SELECT table1.column_1, table1.column_2,...table2.column_1, table2.column_2…
FROM table1, table2;If you want to join all the columns from both the tables, you can use the following syntax:
SELECT * FROM table1, table2;For example, if you want to Cross Join columns “EmployeeID”, “Name”, “Dept_Name”, and “Position” from our tables “Employee” and “Department”, use the following query:
This will result in the following table:
As you can see, the resulting table consists of 30 rows as our “Employee” and “Department” tables have 6 and 5 rows, respectively.
Inner Join
Using the Inner Join, the tables are combined on the basis of a condition, also known as the join predicate. This condition is applied on the columns of both the tables on either side of the join clause. The query checks all the rows of table1 and table2. Only the rows that satisfy the join predicate are included in the result table.
This join returns all the rows with matching values in both the tables.
The most significant difference between the Inner join and the “Natural Join” is that the common columns appear more than once, whereas they appear only once in the result of Natural Join.The basic syntax of this join is:
SELECT table1.column_1, table2.column_2…
FROM table1
INNER JOIN table2
ON table1.column_1 = table2.column_2;As you can see, the condition is specified after the “ON” statement. If no condition is specified, this join behaves as a “Cross Join”.
For example, if you want to join the tables “Employee” and “Department” based on the “EmployeeID” column, you must use the following query:
This is what the final table would look like:
The resulting table contains only the three rows with the same value for the column “EmployeeID”, in both the tables.
Left Outer Join
Left Outer Join, also known as the Left Join, results in a table containing all the rows from the table on the left side of the join (the first table), and only the rows that satisfy the join condition, from the table on the right side of the join (the second table). The missing values for the rows from the right table, in the result of the join, are represented by NULL values.
The syntax of this join is as follows:
SELECT table1.column_1, table2.column_2,...
FROM table1 LEFT JOIN table2
ON table1.column_1 = table2.column_2;To perform the join on all the columns of the tables, we can use * (asterisk) in place of the column names.
For example, if you want to left join the tables “Employee” and “Department” based on the column “EmployeeID”, you should use the following query:
This will result in the following table:
As you can see, all the rows from our left table “Employee” are present in the result, and only the matching rows are present from the “Department” table, and NULL values represent the remaining rows from this right table.
Right Outer Join
Right Outer Join, also known as the Right Join, works in the opposite way as the Left Outer Join. It follows the same rules as the Left Join, and the only difference is that all the rows from the right table and only the condition satisfying rows from the left table are present in the result table.
The syntax of this join is:
SELECT table1.column_1, table2.column_2,...
FROM table1 RIGHT JOIN table2
ON table1.column_1 = table2.column_2;For example, if you want to Right Join the columns “EmployeeID”, “City”, “Position”, and “Dept_Name” from our tables “Employee” and “Department”, on the basis of the column “EmployeeID”, you should use the following query:
This will result in the following table:
As you can see, this result is the opposite of the Left Join result.
Full Outer Join
Full Outer join, also known as the Full Join, is used to ensure that no data is lost while joining two tables. In this case, all the rows from both the tables are returned.
If there are missing values for any row in either of the tables, they are represented as NULL values in the result of this join in SQL.
The syntax of this join is:
SELECT table1.column_1, table2.column_2,...
FROM table1 FULL JOIN table2
ON table1.column_1 = table2.column2;Like “left” and “right” joins, in this case too we can use * (asterix) in the place of columns if we want to join all the columns from both the tables.
Some database systems like MySQL do not support the syntax of Full Join, as the result of this join can be achieved by performing the UNION operation on the Left and Right Outer Joins.
For example, if you want to join the columns “EmployeeID” and “Position” from the tables “Employee” and “Department”, on the basis of the column “EmployeeID”, you should use the following query.
This will result in the following table:
As you can see, all the rows from both the tables are present in this result table, and the rows that contain missing values, in either of the tables, are represented as NULL values.
Self Join
A table can be joined to itself using this join. This is used when we want to compare the values of different columns of the same table.
A table can be self joined using any of the above-discussed joins like “inner”, “left”, “right”, “full”, or “cartesian” join.
The syntax of this join is:
SELECT A.column_1, B.colmn_2,...
FROM table1 A
JOIN_NAME table2 B
ON [condition]“A” and “B” are aliases for table1.
For example, in the table “Employee”, if you want to find out which employees belong to the same city, you can use the following query:
Here, we have used another condition using the “AND” clause so that the names don’t appear twice in the result table.
The query will result in the following table:
As we can see, only two employees are from the same city.
Gain expertise in the latest Business analytics tools and techniques with the Business Analytics For Strategic Decision Making. Enroll now!
Your Next Step
Almost all of the real-world data queries are performed using some join in SQL. It is a very simple yet powerful tool available to data scientists and anyone else who wishes to understand and manage data.
And now that you know about the different kinds of joins in SQL, the next step would be to learn how to perform various queries on your dataset with the help of all the different types of commands and clauses available for use.You must check out our Business Analyst Master’s Program, and learn A-Z about SQL and take a deep dive into the world of data!
In case you have any questions for us, do write them in the comment section of our “How to Combine Tables Using Join in SQL” article, and we’ll have our experts in the field answer them for you.