SQL JOIN

Introduction

In relational databases such as SQL Server, Oracle, MySQL, and others, we store our data in numerous logical tables that are linked together by a common key value. As a consequence, we are constantly required to transform data from two or more tables into the desired output based on certain criteria. Using the SQL JOIN clause, we can rapidly generate this type of data in SQL Server.

A SQL Join statement joins data or rows from two or more databases based on a shared field. This article provides a brief overview of various kinds of joins such as INNER JOIN, NATURAL JOIN, CROSS JOIN, SELF JOIN, and so on, with comprehensive examples of each.

JOINS In SQL

SQL Joins are typically used when a user is attempting to extract data from numerous tables (with one-to-many or many-to-many relationships) at the same time. The join keyword joins two or more tables together and generates a temporary image of the merged database. Then, based on the criteria specified, it extracts the necessary data from the image table, and once the data is fetched, the temporary image of the merged tables is dumped.

In SQL databases, two key variables are commonly used: Primary Key and Foreign Key. While the primary key is required for a table to qualify as a relational database table and individually identify each row of the table to which it belongs, the foreign key is responsible for connecting two tables in the database. In this case, the foreign key must be the primary value of another table. The foreign and main keys it refers to are sometimes found in the same table. SQL Self Join is used in such situations. When we use SQL Joins, we frequently use these two key fields to determine what the user requires and then construct our queries appropriately.

Let us Understand by a simple example of SQL JOIN

We have an Employee database of a Company, and Table1 (Employee) includes information about the employee such as the Employee Name. Table2 (Salary) contains Salary details, such as their total salary and Bonus of the Employee. The Primary key in Table1 is emp_id. Table 1 refers to Table 2 via emp_id. As a result, it serves as a foreign variable in Table 2.

SELECT Employee.Name, Salary.Salary, Salary.Bonus
FROM Employee
JOIN Salary
ON Employee.emp_id = Salary.emp_id;                 

There are various types of joins available depending on the requirements of the users. These connections are divided into four types: Cross Self, Inner, and Outer.

Types Of SQL JOIN

Joins are classified into four kinds.

1. INNER JOIN/Simple join

2. LEFT OUTER JOIN/Left Join

3. RIGHT OUTER JOIN/Right join

4. FULL OUTER JOIN/Full join

Crack your next tech interview with confidence!

SQL INNER JOIN

SQL Inner Join, also known as Equi Join, is the most basic join in which all rows from the intended tables are cached together if they satisfy the specified criteria. This join requires two or more columns. Inner Join can be used with SQL conditional expressions such as WHERE, GROUP BY, ORDER BY, and so on.

SYNTAX

Syntax for INNER JOIN is

SELECT column_name  
FROM table1    
INNER JOIN table2 
ON table1.column = table2.column;

Picture Representation

The SQL INNER JOIN obtains the shaded area in this given diagram, The entries where table1 and table2 intersect would be returned by the SQL INNER JOIN.

Problem

Let's look at an example of how to use the INNER JOIN in a query. In this example, we have a table1 called customers with the following data contains information about various customers, such as their customer number, name, and phone number. Customer_no is the primary key that uniquely identifies each row in this case.

And a table2 called orders_details with the following data contains information about orders purchased by customers, such as item name, amount. In Table2 Customer_no is the foreign Key

SQL Query Statement

SELECT customers.customer_no, customers.name, orders_details.amount
FROM customers 
INNER JOIN orders_details
ON customers.customer_no = orders.customer_no;            

This problem would yield all rows from the customers and orders_details datasets that have a matching customer_no value in both tables. Rows in the customers table with customer_no of 105 would be omitted because that do not appear in both tables. Because the customer_no of NULL does not appear in the customers table, the row with the order_details of Oil from the orders table would be omitted.

NATURAL JOIN

NATURAL JOIN is a type of INNER JOIN.

A SQL natural join is a type of inner join that requires columns with the same name and data type to exist in both tables being joined.

SELECT * FROM table1_name
NATURAL JOIN table2_name;

SQL LEFT JOIN

This join returns all rows from the table to the left of the join and matches rows from the table to the right of the join. The result-set will contain null for rows for which there is no matching entry on the right side. LEFT OUTER JOIN is another name for LEFT JOIN.

SYNTAX

Syntax for LEFT OUTER JOIN is :-

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

We might use LEFT OUTER JOIN in place of LEFT JOIN because the results are the same.

Picture Representation

The SQL LEFT JOIN obtains the shaded area in this given diagram, The SQL LEFT OUTER JOIN returns all records from table 2 as well as only those records from table 1 that overlap with table 2.

Problem

Let's look at an example of how to use the LEFT OUTER JOIN in a query, with same example. In this example, we have a table1 called customers with the following data contains information about various customers, such as their customer number, name, and phone number. Customer_no is the primary key that uniquely identifies each row in this case.

And a table2 called orders_details with the following data contains information about orders purchased by customers, such as item name, amount. In Table2 Customer_no is the foreign Key

SQL Query Statement

SELECT customers.customer_no, customers.name, orders_details.amount
FROM customers 
LEFT OUTER JOIN orders_details
ON customers.customer_no = orders_details.customer_no;          

This LEFT JOIN Problem would result in all rows from the customers table1 and only the orders_details table2 rows where the joined fields are equal. If a customer_no value in the customers table does not exist in the orders table, the result set will show NULL for all fields in the orders_details table. As you can see, an LEFT JOIN would include the row with customer_no 105, but the item and amount columns are NULL.

SQL RIGHT JOIN

RIGHT JOIN is synonymous with LEFT JOIN. This join gives all rows from the table on the right and matching rows from the table on the left. The result-set will contain null for rows where there is no matching entry on the left side. RIGHT OUTER JOIN is another term for RIGHT JOIN.

SYNTAX

Syntax for RIGHT OUTER JOIN is :-

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;                   

We might use RIGHT OUTER JOIN in place of RIGHT JOIN because the results are the same.

Picture Representation

The SQL RIGHT JOIN obtains the shaded area in this given diagram, The SQL RIGHT OUTER JOIN returns all records from table 1 as well as only those records from table 2 that overlap with table 1.

Problem

Let's look at an example of how to use the RIGHT OUTER JOIN in a query, with same example. In this example, we have a table1 called customers with the following data contains information about various customers, such as their customer number, name, and phone number. Customer_no is the primary key that uniquely identifies each row in this case.

And a table2 called orders_details with the following data contains information about orders purchased by customers, such as item name, amount. In Table2 Customer_no is the foreign Key

SQL Query Statement

SELECT customers.customer_no, customers.name, orders_details.amount
FROM customers 
RIGHT OUTER JOIN orders_details
ON customers.customer_no = orders_details.customer_no;          

This RIGHT JOIN Problem would result in all rows from the Order_details table2 and only the customers table1 rows where the joined fields are equal. If a customer_no value in the orders table does not exist in the customers table1, the result set will show NULL for all fields in the customers database. As you can see, a RIGHT OUTER JOIN would include the row with amount 150 of Oil but the customer_no and name columns are NULL.

SQL FULL JOIN

The result-set is created by combining the outcomes of both LEFT JOIN and RIGHT JOIN. The result collection will include all rows from both tables. The result-set will contain NULL values for entries where there is no match.

SYNTAX

Syntax for FULL OUTER JOIN is :-

SELECT columns
FROM table1
FUll JOIN table2
ON table1.common_column = table2.common_column;                   

We might use FULL OUTER JOIN in place of Full JOIN because the results are the same.

Picture Representation

The SQL FULL JOIN obtains the shaded area in this given diagram, The SQL FULL OUTER JOIN returns all records from table 1 as well as from table 2.

Problem

Let's look at an example of how to use the FULL OUTER JOIN in a query, with same example. In this example, we have a table1 called customers with the following data contains information about various customers, such as their customer number, name, and phone number. Customer_no is the primary key that uniquely identifies each row in this case.

And a table2 called orders_details with the following data contains information about orders purchased by customers, such as item name, amount. In Table2 Customer_no is the foreign Key

SQL Query Statement

SELECT customers.customer_no, customers.name, orders_details.amount
FROM customers 
FULL OUTER JOIN orders_details
ON customers.customer_no = orders_details.customer_no;          

This FULL OUTER JOIN example would yield all entries from both the orders_details and customers tables. When the joined condition is not satisfied, a NULL value is extended to the result set's colums. This means that if a customer_no value in the customers table1 does not exist in the orders_details table2, the result set will show NULL for all Columns in the orders_details table. In addition, if a customer_no value in the orders table does not exist in the customers table, the result set will show NULL for all fields in the customers database.

As you can see, the rows with customer_no 105 is included, but the item and amount columns are NULL for those records. The row with the amount 150 would also be included, but the customer_no column for that record is NULL.

Conclusions

After going through with this article, SQL JOIN is a strong concept that we can use to combine data from various tables in a relational database. There are several types of SQL JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each with its own particular use case.

When using SQL JOIN, it is essential to consider the relationships between the tables and to choose the proper type of JOIN to ensure that we are retrieving the data we need. It is also important to use table aliases and to qualify column names to avoid ambiguity and ensure that the query is easy to read and maintain.

Good luck and happy learning!