SQL Join

SQL Joins are an essential feature of SQL that helps to combine data from two or more tables in a database. Finding matching values shared between these tables makes it easy for programmers to access and manage data stored in the RDBMS or stream processing through Structured Query Language (SQL).

This blog post will discuss Joins in SQL, their types, and certain associated features. Examples of each topic related to SQL Joins have been provided to make the concepts easier to understand and comprehend.

What are Joins?

It is possible to extract data from multiple tables at once using SQL Joins. The join keyword combines two or more separate tables, creating an interim representation of the joined table, and then uses the conditions specified by the user to bring out relevant information from this joined table. Once all the desired data has been retrieved, this temporary version of the merged tables will be deleted.

It is common for large databases to experience data redundancy, which means that the same information gets entered more than once due to insertion, deletion, and updating. However, by implementing SQL Joins, we can work towards database normalization – this minimizes redundant data and eliminates unnecessary repetition.

In SQL databases, two essential fields are used commonly: Primary Key and Foreign Key. The primary key is needed for a table to meet the standard of being part of the relational database and recognize each row solely within that table. On top of that, foreign keys link two tables in the system so their data can be accessed. At times, both these related keys exist inside one tabular, making it necessary for us to use Self Joins on those occasions. Finally, when we formulate our queries with JOINs, these components help greatly in pointing out what information has been asked from us by users/clients, etc.

Example Of SQL Joins

Let’s take there are two table name employee and department. Table 1 is the Employees table with the employee’s information like EmployeeID, EmployeeName, and DepartmentID. We have another Department table with information on DepartmentID & Department.

The above tables show EmployeeID (Employee) and DepartmentID(Department) as the primary key. The DepartmentID attribute of the employee table is a reference to the DepartmentID attribute of the department table; hence we called that a Foreign Key.

Types of SQL Joins

It’s helpful to know that there are four types of joins in SQL Join. These are the following:

• CROSS JOIN
• INNER JOIN
• FULL JOIN
• LEFT JOIN
• RIGHT JOIN

Let’s go through each join one at a time. We’ll start with Cross Join and work up to Right Join.

SQL CROSS JOIN Or Cartesian Join

The Cartesian Join also popularly know as CROSS Join. If there are two tables and CROSS Join is applied to them, it takes all of the rows from one table and combines them with every row in another.

This means if there are X rows in one table and Y numbers in another, the resulting Cartesian Join will contain X*Y total records. It should also be noted that this joining is done when no conditions or matching columns have been specified within your SQL query statement.

Syntax Of Cross Join

SELECT column-name(s)
FROM table1 CROSS JOIN table2;

The SELECT statement chooses the columns that will appear in the resulting table. And the FROM clause indicates which tables should be looked at for those specific columns, while CROSS JOIN determines what type of join must occur between two joined tables.

Let us take an example of implementing the cross Join in the above tables.

SELECT *
FROM Customers CROSS JOIN Shopping_Details;

Self Join

The SELF JOIN allows you to join one table with itself, which means each row can be combined with other rows in the same table. This is done using SQL commands and giving each instance of the same table a different name via aliases. It can be beneficial for obtaining hierarchical data or comparing rows within a single database.

Syntax For Self Join

SELECT a.column1 , b.column2
FROM table_name a, table_name b
WHERE some_condition;

We can further explore the employee table by noting that it contains an employee id, name, phone number, and supervisor id. The supervisors are present at the employee table itself. Hence, the supervisor id acts like a foreign key, the primary key, as it references the employee id.

Here is an example of SELF JOIN

SELECT a.Name AS Supervisors
FROM Employees a, Employees b
WHERE a.ID = b.supervisor_ID;

Inner Join SQL

A SQL Inner Join, also known as an Equi Join, is the simplest type of join in which all rows from two or more tables are combined if they satisfy a specified condition. This type of join requires at least two tables, and it can be used with various conditions such as WHERE GROUP BY and ORDER BY.

The Syntax of Inner Join:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

Example:

SELECT Customers.Name, Shopping_Details.Item_Name, Shopping_Details.Quantity
FROM Customers INNER JOIN Shopping_Details
WHERE Customers.ID==Shopping_Details.ID;

Natural Join

A Natural Join is an Inner join that links two tables based on columns with the same name and data type in both. And the syntax of Natural Join

SELECT * FROM 
table-1 NATURAL JOIN table-2;

Let us take an example for a better understanding of Natural Join

From the above table, we need to find people who have visited Delhi, Mumbai & Chennai. Then the script will be something like below:

SELECT * from Table1 NATURAL JOIN Table2; 

When we have executed the above script, then we will get the result something like the below:

ID	Name	Address
1	Abhi	DELHI
2	Adam	MUMBAI
3	Alex	CHENNAI

Both the tables have an ID column with the same name and data type, so when you join them together, only records that match in both tables will be included as part of your natural join.

Outer Joins SQL

SQL Outer joins can create a combined result that includes matched and unmatched rows from the two joined tables. Depending on what you need, there are three types of outer join available:

Left Outer Join

The left outer join operation will return all records from the left table and any matching elements found in the right table. If an element is not present on the right side of the equation, NULL will be represented.

The syntax for the left outer join will be:

SELECT column-name-list FROM 
table1 LEFT OUTER JOIN table2
ON table1.column-name = table2.column-name;

Let us take a simple example to understand the Left Outer Join:

Now let us perform the left outer join on the above two tables.

ID	NAME	ID	ADDRESS
1	Abhi	1	Delhi
2	Adam	2	Mumbai
3	Alex	3	Chennai
4	Anu	null	null
5	Ashish	null	null

Right Outer Join

A right join operation will bring back all records from the table on its right and any matching elements in the left-side table. If no corresponding elements are found on the left-hand side, then NULL will be displayed for that element.

The syntax for the Right Outer Join is:

SELECT column-name-list FROM 
table1 RIGHT OUTER JOIN table2 
ON table1.column-name = table2.column-name;

If we execute a right outer join query on the above table, then the query and final result table will look like the below:

SELECT * FROM Table1 RIGHT OUTER JOIN Table2
ON (Table1.id = Table2.id);

ID	NAME	ID	ADDRESS
1	Abhi	1	Delhi
2	Adam	2	Mumbai
3	Alex	3	Chennai
null	null	7	Bangalore
null	null	8	Kolkata

Full Outer Join

The full outer join combines the results of both left and right outer joins, incorporating every row from both tables in its result set. All rows meeting the condition are added to the output table and the remaining unmatched rows from each query side. Subsequent NULL values will be included for any missing data on either side.

Once the matched rows from both tables have been added to the output table, any unmatched rows from the left-hand table will be included next with NULL values after them. Lastly, all of the unmatched rows from the right-hand table will be appended later and followed by their respective NULL values.

The syntax for the full outer join will be:

SELECT column-name(s)
FROM table1 FULL OUTER JOIN table2
ON table1.column-name = table2.column-name;

Let us go with a simple example to understand the Full outer join concept:

And the Full Outer Join Script will be something like the below:

SELECT * FROM Table1 FULL OUTER JOIN Table2 
ON (Table1.id = Table2.id);

After executing the above query, the final result table will be

ID	NAME	ID	ADDRESS
1	Abhi	1	Delhi
2	Adam	2	Mumbai
3	Alex	3	Chennai
4	Anu	null	null
5	Ashish	null	null
null	null	7	Bangalore
null	null	8	Kolkata

Conclusion:

We believe these details articles on SQL Joins will help you better understand the SQL Join topics and different types of SQL Joins. But still, if you are struggling with any questions, you can ask those in the comment section of this post.