What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

Types of Joins & How to Join 3 Tables in SQL (with code)

  • Jan 09, 2024
  • 9 Minutes Read
Types of Joins & How to Join 3 Tables in SQL (with code)

Joins in SQL are a fundamental feature that allows the combination of rows from different tables based on a shared column. This enables the retrieval of comprehensive data, connecting and analyzing information stored across multiple tables within a relational database.  In this article, we will learn the different types of joins, and how to join 3 SQL Tables.

What are SQL Joins?

SQL Joins are operations that merge rows from tables by matching values in specified columns. The primary goal is to consolidate data from multiple tables into a single result set. Joins use common columns, typically defined by foreign key relationships, to establish connections between tables.

joins in SQL

By using various types of joins: such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN, SQL allows for the retrieval of diverse datasets, enabling complex data analysis and reporting.

3 Types of Joins in SQL

In SQL, joins are like bridges that help us link information from different tables. This section dives into these join types, explaining how each one helps us build effective queries for fetching the right information from the database.

1) Inner Join

Inner Join selects only the rows where there is a match in the columns being joined, effectively combining data from multiple tables. Inner Join is fundamental for connecting related information and extracting unified results, excluding unmatched rows. It forms a core component of SQL query operations, enhancing data cohesion and analysis.

Syntax:

SELECT
table1.column_name_x,
table2.column_name_y
FROM
table1
INNER JOIN
table2 ON table1.column_name_x = table2.column_name_y
GROUP BY
table1.column_name_x; table2.column_name_y;

 

Consider the tables "products" and "amount." We will explore the application of an inner join to identify customers who have not yet made payments for their purchased products.

Table: Products

table products

Table: Amounts

Table Amounts

SQL Query:

SELECT
products.customer_id,
products.customer_name,
SUM(CASE WHEN amount.transaction_status = 'Unpaid' THEN amount.transaction_amount ELSE 0 END) AS total_unpaid_amount
FROM
products
INNER JOIN
amount ON products.customer_id = amount.customer_id
GROUP BY
products.customer_id, products.customer_name;

 

Output: 

inner join

As we can see from the utilization of an inner join, we inferred that among the four customers, both "Jane Smith" and "Alice White" have not settled their full payment for the purchased products.

2) Outer Join

Outer joins in SQL are used to retrieve matching rows from at least one table and include non-matching rows from one or both tables. Unlike inner joins that only return matching data, outer joins ensure that unmatched rows are also included in the result set. This makes outer joins valuable for scenarios where we want to maintain all records from one table, even if there are no matches in the other.

Syntax:

SELECT
table1.column_name_x,
table2.column_name_y
FROM
table1
OUTER JOIN
table2 ON table1.column_name_x = table2.column_name_y
GROUP BY
table1.column_name_x; table2.column_name_y;

 

Consider the tables "products" and "amount" from the same above example as well. We will explore the application of an outer join to deduce the total amount to be paid by the customers.

SQL Query:

SELECT
p.customer_id,
p.customer_name,
SUM(a.transaction_amount) AS total_transaction_amount
FROM
products p
LEFT OUTER JOIN
amount a ON p.customer_id = a.customer_id
GROUP BY
p.customer_id, p.customer_name;


Output: 

outer join

3) Left Join

A Left Join in SQL is a type of join that returns all rows from the left table and the matching rows from the right table. If there is no match, it still includes all rows from the left table, filling in with NULL values for columns from the right table.

Syntax:

SELECT
table1.column_name_x,
table2.column_name_y
FROM
table1
LEFT JOIN
table2 ON table1.column_name_x = table2.column_name_y
GROUP BY
table1.column_name_x; table2.column_name_y;

 

Consider the tables "products" and "amount." We will explore the application of left join to retrieve the customer name, the amount to be paid, and the status of the transaction.

SQL Query:

SELECT
DISTINCT amount.customer_id,
products.customer_name,
amount.transaction_amount,
amount.transaction_status
FROM
amount
LEFT JOIN
products ON amount.customer_id = products.customer_id;

 

Output: 

left join

4) Right Join

A Right Join in SQL is like looking at a list on the right side and bringing in matching items from the left. If there's nothing on the left that matches, it still shows everything on the right but leaves those spots empty. Right joins are handy when we want to make sure we see everything on the right, whether there's a match on the left or not.

Syntax:

SELECT
table1.column_name_x,
table2.column_name_y
FROM
table1
RIGHT JOIN
table2 ON table1.column_name_x = table2.column_name_y
GROUP BY
table1.column_name_x; table2.column_name_y;

 

Consider the tables "products" and "amount." We will explore the application of Right Join to identify customers who have not yet made payments for their purchased products.

SQL Query:

SELECT
products.product,
amount.transaction_status
FROM
products
RIGHT JOIN
amount ON products.customer_id = amount.customer_id;

 

Output: 

right join

5) Self Join

A Self-join in SQL is a special case where a table is joined with itself. It's like looking at the same table twice but treating them as if they were different tables. Self-joins are useful when we need to compare rows within the same table, allowing us to establish relationships between different records in the same dataset.

Syntax:

SELECT
t1.column1,
t1.column2,
t2.column1 AS column1_from_t2,
t2.column2 AS column2_from_t2
FROM
table1 t1,
table2 t2
WHERE
t1.common_column = t2.common_column;

 

Let's take a look at the "amount" table. We will examine how a self-join can be used to discover customer IDs and their corresponding paid/unpaid amounts about those customer IDs.

SQL Query:

SELECT
a1.customer_id,
SUM(CASE WHEN a1.transaction_status = 'Paid' THEN a1.transaction_amount ELSE 0 END) AS total_paid_amount,
SUM(CASE WHEN a1.transaction_status = 'Unpaid' THEN a1.transaction_amount ELSE 0 END) AS total_unpaid_amount
FROM
amount a1
JOIN
amount a2 ON a1.customer_id = a2.customer_id
AND a2.SELECT
c.customer_id,
c.customer_name,
b.bill_id,
b.order_date,
p.product_name,
p.price
FROM
customers c
JOIN
bill b ON c.customer_id = b.customer_id_bill
JOIN
products p ON b.product_id_bill = p.product_id
WHERE
b.total_amount > 100.00;transaction_status = 'Unpaid'
GROUP BY
a1.customer_id;

 

Output: 

self join

How to Join 3 Tables in SQL?

We can use the WHERE clause to join 3 Tables in SQL. Joining three tables allows us to combine information from multiple sources. Adding a WHERE clause refines the results by specifying conditions for the combination. This technique enhances data retrieval across complex interconnected tables.

Let’s check out the syntax of joining three tables.

Syntax:

SELECT
table1.column1,
table1.column2,
table 2.column3,
table3.column4
FROM
table1
JOIN
table2 ON table1.common_column = table2.common_column
JOIN
table3 ON table2.common_column = table3.common_column
WHERE
condition;

 

Let's take a look at three given tables:  "customers", ”bill” and “products”. We will examine how a self-join can be used to discover customer IDs, names, bill_id, product name, and prices from three different tables.

Table: Customers

Table: bill

Table: products

SQL Query:

SELECT c.customer_id, c.name,b.bill_id,b.order_date,p.product_name,p.price
FROM 
    customers c
JOIN 
   bill b ON c.customer_id = b.customer_id_bill
JOIN 
    products p ON b.product_id_bill = p.product_id
WHERE
    p.price>100;

 

Output:

Join 3 Tables SQL Output

Finally, here are some best practices for applying Joins:

  • Understand the connections between tables before selecting the appropriate join.
  • Clearly state the type of join (INNER, LEFT, etc.) for better understanding and maintenance.
  • Limit the number of joins to maintain simplicity in our queries.
  • Apply WHERE conditions carefully to refine results and enhance query efficiency.

Conclusion

Overall SQL joins are powerful tools for combining data from multiple tables. We learned how to join 3 tables in SQL. Keep queries simple, and know your data relationships and performance test. With these practices, SQL joins become effective for extracting meaningful insights from interconnected data tables. Have more queries? Get our SQL Assignment help to solve your homework now.

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.