Blog

You are currently viewing Introduction to joins in SQL  

Introduction to joins in SQL  

Loading

There are vast volumes of data in our database, which is made up of thousands of tables. When integrating a large amount of data, we encounter problems since we must look at a particular group of data that meets a number of requirements.
We need to use a variety of joins to bring the data from each table together into one because it is spread across several tables. We can merge the rows, columns, and other elements of several tables using SQL join commands so that we can view them as one table. We may quickly aggregate and display data into a single table by using joins.

This facilitates our ability to conduct inquiries and transactions on the data we require. Joins are carried out using one or more shared fields between two or more tables. This improves our capacity to conduct inquiries and operations on the data we require. Joins are carried out using one or more shared fields between multiple tables.

Types of join along with SQL codes

There are four types of join which are given down below

  • Inner join
  • Outer join
  • cross join
  • Multiple joins

Inner join

An inner join in SQL is a way to combine data from two different tables into one table.

The rows with matching values across both tables are the only ones that are returned. It is used to retrieve data as though it were just in one table, even though it is actually retrieved from numerous tables.

Inner Join

The basic syntax for an inner join is as follows:

SELECT *
FROM table1
JOIN table2
ON table1.column = table2.column;

Consider having two tables with the names “Customers” and “Orders,” which are connected by the “CustomerID” column. While the Orders table contains information about the orders, such as the date, product, and amount, the Customers table contains information about the customers, such as their name, address, and phone number.

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of the inner join

Outer join

It returns all rows from both tables, including the matched and unmatched rows. If there is no match, the result will contain NULL values for the non-matching columns.

Outer Join
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

There are two types of outer joins:

  • left outer join or Left join
  • right outer join or Right join

A left outer join returns all the rows from the left table (also known as the first table) and the matching rows from the right table (also known as the second table). If there is no match, the result will be filled with NULL values.

The basic syntax for a left outer join is as follows:

Left Join
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
SELECT Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
The output of the left outer join

A right outer join returns all the rows from the right table (also known as the second table), and the matching rows from the left table (also known as the first table). If there is no match, the result will be filled with NULL values.

Right join

The basic syntax for a right outer join is as follows:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
SELECT Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
The output of the right outer join

Cross join

The result is the combination of each row from the first table and each row from the second table, or the Cartesian product of the two tables.

Two tables
SELECT *
FROM table1
CROSS JOIN table2;
The output of the cross join

Multiple joins

To merge data from several tables, it is feasible to utilize multiple JOIN queries in a single SQL query. This is frequently referred to as a “multi-join” or “multi-table join.” A multi-table join’s basic syntax is as follows:

SELECT *
FROM table1
JOIN table2
ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column

Based on the join criteria supplied, each JOIN statement combines the rows from the preceding table with the rows from the following table. Because each join is conducted one at a time, the sequence of the JOIN statements is important.

In a multi-table join query, you can also utilize multiple JOIN types, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Each join clause has the option of specifying the join type.

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
RIGHT JOIN table3
ON table2.column = table3.column
INNER JOIN table4
ON table3.column = table4.column

Conclusion

Finally, joins are a core component of SQL that let you combine data from several tables based on related columns. The several types of joins accessible are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN, each with a specific use case. It is crucial to know when and how to utilize each form of join when searching and working with data in a relational database. A single query can also contain many JOIN statements, a practice known as “multi-table join” or “multi-join.” It’s important to remember that these statements may have somewhat different syntax in different DBMS, and it’s crucial to pay attention to the JOIN order and query performance.

If you like the article and would like to support me, make sure to: