Understanding Joins in SQL
Tags: mysql, database, sql
Published: 2023-09-23 06:54:02


In the world of databases, the ability to combine data from multiple tables is a fundamental skill. This is where joins come into play. Joins allow you to merge rows from different tables based on a related column, enabling you to retrieve comprehensive datasets that provide deeper insights into your data. In this blog post, we'll explore joins, including different types of joins, and provide practical examples using SQL. We'll also visualize the tables to help you understand the concepts better.


**Table of Contents**
1. Introduction to Joins
2. Types of Joins
  - INNER JOIN
  - LEFT JOIN
  - RIGHT JOIN
  - FULL OUTER JOIN
3. Practical Examples
4. Visualizing Tables
5. Conclusion

**1. Introduction to Joins**

In a relational database, data is often distributed across multiple tables to maintain data integrity and prevent data duplication. However, to retrieve meaningful insights, you often need to combine data from these tables. This is where joins come into play.

A join operation combines rows from two or more tables based on a related column between them. The related column acts as a bridge, connecting the tables and allowing you to create a unified dataset.

**2. Types of Joins**

**_INNER JOIN_**
An INNER JOIN returns only the rows that have matching values in both tables being joined. It's like finding the intersection of two sets.

```
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

```

**_LEFT JOIN_**
A LEFT JOIN returns all rows from the left (or first) table and the matching rows from the right (or second) table. If there's no match in the right table, NULL values are returned.

```
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

```

**_RIGHT JOIN_**
A RIGHT JOIN is similar to a LEFT JOIN, but it returns all rows from the right table and the matching rows from the left table.

```
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;

```
**_FULL OUTER JOIN_**
MySQL doesn't directly support FULL OUTER JOIN, but you can simulate it using a combination of LEFT, RIGHT JOIN and UNION.

```
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
UNION
SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

```

**3. Practical Examples**

Let's use practical examples with SQL and PostgreSQL to understand how joins work.

_SQL Example - INNER JOIN_
Suppose we have two tables: orders and customers.

_Table: orders and customer_

```
+---------+------------+-------------+
| order_id | customer_id | order_total |
+---------+------------+-------------+
| 1       | 101        | 50.00       |
| 2       | 102        | 30.00       |
| 3       | 103        | 20.00       |
+---------+------------+-------------+

```

```
+------------+--------------+
| customer_id | customer_name |
+------------+--------------+
| 101        | Alice        |
| 103        | Bob          |
| 104        | Carol        |
+------------+--------------+

```
Let's retrieve a list of orders along with customer names using an INNER JOIN:

```
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

```

_Result_
```
+---------+--------------+
| order_id | customer_name|
+---------+--------------+
| 1       | Alice        |
| 3       | Bob          |
+---------+--------------+

```
**4. Visualizing Tables**

Visualizing tables can help you understand how joins work. Here's a visual representation of my Github document [click here](https://github.com/aj7tt/placement-22/tree/main/Rdbms/SQL%20joins%20%26%20Subquery)

**5. Conclusion**

In conclusion, joins are essential for merging data from different tables in databases like MySQL. They enable us to perform complex queries and gain deeper insights into our data.

If you have questions, need help, or want to discuss databases, feel free to connect with me on my [Twitter](https://twitter.com/aj7ttt). I regularly share MySQL-related code, tutorials, and resources.

GitHub Repository: [Link](https://github.com/aj7tt)

Let's continue our database journey together!