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.
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: 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:
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:
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:
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:
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:
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:
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.
