Basics of Joins

Last Updated: 9th June, 2023

Overview

Joins in SQL are utilized to combine data from two or more tables. There are four essential sorts of SQL joins: inner, left, right, and full. Inner Joins return only rows with coordinating values in both tables, Left Joins return all rows from the left table, Right Joins return all rows from the proper table, and Full Joins return all rows from both tables in any case of matches.

Different Types of SQL JOINs

Krishna was a SQL engineer who had just begun and had a new tech startup job. He was energized to be part of the group but had a part to learn about the language. One of the primary subjects his boss had him learn was the essentials of joins in SQL. Krishna had a difficult time understanding the concept to begin with. He spent hours perusing articles and observing instructional exercises but still felt confounded. Let's make things less complex for him.

The four fundamental sorts of SQL JOINs are Inner Join, Left Join, Right Join, and Full Join.

1. Inner Join: An Inner Join combines records from two tables when there is a match in the specified columns.
2. Left Join: It returns all the rows from the left table (table1) and matching rows from the right table (table2).
3. Right Join: A Right Join is the mirror image of a Left Join. It returns all the rows from the right table (table2) and matching rows from the left table (table1).
4. Full Join: A Full Join returns all the rows from both tables, regardless of whether there is a match between the two.

Syntax

1. INNER JOIN:

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

In Inner join returns all the rows from both tables that match the specified criteria based on a common column value. It is also known as an equi-join.

2. LEFT JOIN:

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

In Left join, returns all the rows from the left table (table1), even if there are no matches in the right table (table2). If there are no matches, NULL values will be returned in the columns of the right table.

3. RIGHT JOIN:

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

In Right join returns all the rows from the right table (table2), even if there are no matches in the left table (table1). If there are no matches, NULL values will be returned in the columns of the left table.

4. FULL OUTER JOIN:

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

In Full outer join returns all the rows from both tables, even if there are no matches in either table. If there are no matches, NULL values will be returned in the columns of the joined tables.

Examples

• INNER JOIN: For illustration, let's say we have two tables, "Customers" and "Orders", and we need to urge all the clients who have put orders. We might utilize an inner join to join these two tables on the "CustomerID" column like this:

SELECT c.CustomerID, [c.Name](<http://c.name/>), o.OrderID
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

This query will return all the customers who have placed orders, along with the OrderID of each order.

• LEFT JOIN: For example, let's say we want to get the list of all customers, even those who have yet to place any orders. We could use a left join to join the "Customers" table with the "Orders" table on the "CustomerID" column, like this:

SELECT c.CustomerID, [c.Name](<http://c.name/>), o.OrderID
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

This query will return all the customers, along with the OrderID of each order (if any). If a customer has not placed any orders yet, NULL values will be returned in the "OrderID" column.

• RIGHT JOIN: Let's say we want to get the list of all orders, even those with no customers. We could use a right join to join the "Customers" table with the "Orders" table on the "CustomerID" column, like this:

SELECT c.CustomerID, [c.Name](<http://c.name/>), o.OrderID
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID;

This query will return all the orders, along with the CustomerID of each customer (if any). NULL values will be returned in the "CustomerID" column if an order has no customer.

• FULL OUTER JOIN: Let's say we want to get the list of all customers and orders, even those with no matches in either table. We could use a full outer join to join the "Customers" table with the "Orders" table on the "CustomerID" column like this:

SELECT c.CustomerID, [c.Name](<http://c.name/>), o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID;

This query will return all the clients and orders, besides the CustomerID and OrderID of each (in case any). In the event that a client has not set any orders, however, or in the event that an order has no client, Null values will be returned within the individual columns.

Conclusion

Krishna worked hard to ace the basics of SQL joins, which paid off. After investing hours, considering, and practicing, he finally got the hang of it. His boss was awed by his advancement and gave him some more challenging assignments to work on.

Krishna was presently sure in his capacities and delighted in the challenge of learning modern things. He felt more secure in his job and was looking forward to what the long term would bring.

Key takeaways

1. Joins are utilized to combine information from two or more tables in a relational database.
2. The three fundamental types of joins are: Inner, Left/Right Outer, and Full Outer.
3. A join clause indicates how the tables should be related and which columns should be used to connect them.
4. Joins are ordinarily utilized to recover information from numerous tables but can moreover be utilized to insert, erase, or update data.
5. When utilized accurately, joins can improve the execution of SQL queries by decreasing the sum of information that should be recovered.

Quiz

1. What type of join is used to return all rows from both tables, even those with no matching entries? 
   1. a. Inner Join 
   2. b. Outer Join 
   3. c. Left Join 
   4. d. Right Join

Answer: b. Outer Join

2. What type of join returns all rows from the left table and matching rows from the right table? 
   1. Inner Join 
   2. Outer Join  
   3. Left Join 
   4. Right Join

Answer: c. Left Join

3. What type of join returns all rows from the right table and matching rows from the left table? 
   1. Inner Join 
   2. Outer Join 
   3. Left Join  
   4. Right Join

Answer: d. Right Join

4. What type of join is used to return only matching rows from both tables? 
   1. Inner Join 
   2. Outer Join  
   3. Left Join 
   4. Right Join

Answer: a. Inner Join