What are SQL joins

SQL (Structured Query Language) is a programming language used to manage relational databases. One of the key features of SQL is the ability to join tables together to extract meaningful data. SQL joins combine rows from two or more tables based on a related column between them.

There are several types of SQL joins, each with its own specific use case. The most commonly used types of SQL joins include:

• Inner Join
  An inner join returns only the rows where there is a match in both tables being joined. It is the most frequently used type of join in SQL.

• Left Join
  A left join returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, then the result will contain NULL in those columns.

• Right Join
  A right join returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, then the result will contain NULL in those columns.

• Outer Join
  An outer join returns all the rows from both tables and where there is no match, the result will contain NULL in those columns.

Inner Join

Inner Join in SQL is used to combine rows from two or more tables into a single result set based on a matching condition between them. Inner join only returns rows that have a match in both tables being joined. In other words, inner join eliminates rows that do not match from both tables.

Syntax of Inner Join

The syntax for an inner join in SQL is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

In the above syntax, the column_name(s) represent the columns you want to select from the tables being joined. Table1 and table2 represent the tables you want to join, and column_name represents the common column between them.

Examples of using Inner Join

Consider two tables, Customers and Orders, where the Customers table contains information about customers and the Orders table contains information about their orders. We can use inner join to combine these tables and extract useful insights from them.

Customers table:

Orders table:

To extract the customer name, contact name and order date, we can use inner join as follows:

SELECT Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

The output of the above SQL query will be:

Left Join

Left Join in SQL is used to combine rows from two or more tables into a single result set based on a matching condition between them. A left join returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, then the result will contain NULL in those columns.

Syntax of Left Join

The syntax for a left join in SQL is as follows:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Examples of using Left Join

Customers table:

Orders table:

To extract the customer name, contact name, and order date, we can use left join as follows:

SELECT Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

The output of the above SQL query will be:

In the above output, we can see that the left join returns all the rows from the Customers table, including those where there are no corresponding rows in the Orders table. In this case, the OrderDate column will contain NULL.

Right Join

Right Join in SQL is used to combine rows from two or more tables into a single result set based on a matching condition between them. A right join returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, then the result will contain NULL in those columns.

Syntax of Right Join

The syntax for a right join in SQL is as follows:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Examples of using Right Join

Customers table:

Orders table:

To extract the customer name, contact name, and order date, we can use right join as follows:

SELECT Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

The output of the above SQL query will be:

In the above output, we can see that the right join returns all the rows from the Orders table, including those where there are no corresponding rows in the Customers table. In this case, the CustomerName and ContactName columns will contain NULL.

Outer Join

Outer Join in SQL is used to combine rows from two or more tables into a single result set based on a matching condition between them. An outer join returns all the rows from both tables and where there is no match, the result will contain NULL in those columns.

Syntax of Outer Join

The syntax for an outer join in SQL is as follows:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Examples of using Outer Join

Customers table:

Orders table:

To extract the customer name, contact name, and order date, we can use outer join as follows:

SELECT Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

The output of the above SQL query will be:

In the above output, we can see that the outer join returns all the rows from both the Customers and Orders table, including those where there are no corresponding rows in the other table. In this case, the CustomerName and ContactName columns will contain NULL for the rows where there are no corresponding orders, and the OrderDate column will contain NULL for the rows where there are no corresponding customers.

Comparison of SQL Joins

Each type of join has its own unique characteristics and serves a specific purpose in database query operations.

Inner Join

Inner join is the most commonly used join in SQL. It returns only the rows that have matching values in both tables being joined. Inner join is ideal when you want to combine data from two tables that have a common column.

For instance, if you have a customers table and an orders table, and you want to get the customer names and the orders they have placed, you can use an inner join to match the customer ID column in both tables.

Left Join

A left join returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will still show all the rows from the left table, with NULL values in the columns from the right table.

This type of join is useful when you want to retrieve all the data from one table, and only the matching data from the other table. A common use case for left join is when you want to retrieve all customers and the orders they have placed, even if they have not yet placed any orders.

Right Join

A right join is similar to a left join, but it returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, the result will still show all the rows from the right table, with NULL values in the columns from the left table.

Right join is not commonly used in SQL, as it can always be expressed as a left join. However, it may be useful in some specific scenarios, such as when you want to retrieve all the orders and the customers who have placed them, even if the customers have not yet been registered.

Outer Join

Outer join returns all the rows from both tables, along with the matching rows from the other table. If there is no match, the result will show NULL values for the missing data.

Outer join is useful when you want to retrieve all the data from both tables, regardless of whether there is a match or not. A common use case for outer join is when you want to retrieve all the customers and all the orders, even if there are some orders that have not been placed by any customer yet.