# SQL Joins

## Introduction

In SQL, a join is used to combine rows from two or more tables based on a related column between them. Joins are essential for retrieving data that is spread across multiple tables in a relational database. They allow us to establish relationships between tables and retrieve meaningful information by combining data from different sources.

## Types of Joins

### Implicit Join

Same as inner join only syntax is different. Example

select *
from customers c , orders o 
where c.customer_id = o.customer_id 

### Inner Join

The inner join returns only the rows that have matching values in both tables. It filters out the unmatched rows from both tables, leaving behind only the common records.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

### Left Join

The left join returns all the rows from the left table and the matching rows from the right table. If there are no matches in the right table, NULL values are returned for the right table's columns.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

### Right Join

The right join returns all the rows from the right table and the matching rows from the left table. If there are no matches in the left table, NULL values are returned for the left table's columns.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

### Full Outer Join

The full outer join returns all the rows from both tables, including unmatched rows from each table. If there are no matches, NULL values are returned for the columns of the other table.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

## Example

Let's consider two tables, `Customers` and `Orders`, to demonstrate the different types of joins.

Table: Customers

| customer_id | customer_name | email |
|-------------|---------------|---------------------|
| 1 | John Smith | [e-mail] |
| 2 | Lisa Johnson | [e-mail] |
| 3 | Mark Davis | [e-mail] |

Table: Orders

| order_id | customer_id | product | quantity |
|----------|-------------|---------------|----------|
| 1 | 1 | Laptop | 2 |
| 2 | 1 | Smartphone | 1 |
| 3 | 3 | Headphones | 3 |
| 4 | 2 | Smartwatch | 1 |

### Inner Join Example

Retrieve the customer name and the product they ordered:

SELECT Customers.customer_name, Orders.product
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

### Left Join Example

Retrieve all customers and their orders (if any):

SELECT Customers.customer_name, Orders.product
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

### Right Join Example

Retrieve all orders and the corresponding customer (if any):

SELECT Customers.customer_name, Orders.product
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

### Full Outer Join Example

Retrieve all customers and their orders, including unmatched records:

SELECT Customers.customer_name, Orders.product
FROM Customers
FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;

## Conclusion

SQL joins are powerful tools for combining data from multiple tables. By understanding the different types of joins and their syntax, you can efficiently retrieve meaningful information from a relational database.