Inner Join Vs Outer Join

INNER JOIN and OUTER JOIN are two types of joins used in SQL to combine rows from two or more tables based on a related column between them. However, they produce different results and are used for different purposes. Let's dive into the details:

INNER JOIN

Result: Returns only the rows when there is a match in both tables.
Usage: Used when you only want to get the results where there's a relationship between the tables.
Example: If you have two tables, Customers and Orders, and you want to find all customers who have made at least one order:
```
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
```
This will only show customers who have made an order.

OUTER JOIN

There are three types of outer joins: LEFT (or LEFT OUTER), RIGHT (or RIGHT OUTER), and FULL (or FULL OUTER).

LEFT OUTER JOIN (or LEFT JOIN)
- Result: Returns all the rows from the left table, and the matched rows from the right table. If there's no match, the result will contain NULL.
- Example: Using the same Customers and Orders tables, if you want to find all customers and their orders, including customers who haven't made any orders:
```
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
```
  This will show all customers, and any orders they might have made. Customers without orders will have NULL in the OrderID column.
RIGHT OUTER JOIN (or RIGHT JOIN)
- Result: Returns all the rows from the right table, and the matched rows from the left table. If there's no match, the result will contain NULL.
- Usage: Less common than LEFT JOIN since you can usually rearrange table order to use a LEFT JOIN instead.
FULL OUTER JOIN (or FULL JOIN)
- Result: Returns all rows when there's a match in one of the tables. Meaning, it combines the results of both LEFT and RIGHT OUTER JOINS.
- Usage: This type of join is not supported in all databases (e.g., MySQL doesn't support it).
- Example: If you want all customers and all orders, and match them where applicable:
```
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
```
  This would list all customers and all orders, matching them where possible.

Conclusion

Use INNER JOIN when you only want the rows that have a matching row in the other table.
Use OUTER JOIN (LEFT, RIGHT, or FULL) when you want to also include rows that don't have matching rows in the other table. The specific type of OUTER JOIN you use depends on which table(s) you want to include all rows from.

SQL Inner Join Examples:

Inner join retrieves rows where there is a match in both tables.

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Left Outer Join vs. Right Outer Join:

Left Outer Join: Returns all rows from the left table and the matched rows from the right table.
Right Outer Join: Returns all rows from the right table and the matched rows from the left table.

-- Left Outer Join Example
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

-- Right Outer Join Example
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;

SQL Multiple Joins with Inner and Outer Joins:

SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
LEFT OUTER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

Examples of Complex Joins with Inner and Outer Joins:

Combining multiple tables with various join types for complex queries.

SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table1.id = table3.id
RIGHT OUTER JOIN table4 ON table3.id = table4.id;