SQL | Join (Inner, Left, Right and Full Joins)

Let's explore these four primary types of joins used in SQL:

1. INNER JOIN:

An INNER JOIN returns rows from both tables where there is a match based on the conditions specified. If there's no match, the row will not appear in the result.

Syntax:

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

Example:

If you have orders and customers tables and want to match them on the customer ID:

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

2. LEFT JOIN (or LEFT OUTER JOIN):

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there's no match, the result will contain NULL for every column from the right table.

Syntax:

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

Example:

To fetch all customers and their orders (even if they haven't placed any):

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

3. RIGHT JOIN (or RIGHT OUTER JOIN):

A RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there's no match, the result will contain NULL for every column from the left table.

Syntax:

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

Example:

To fetch all orders and the customers who placed them (even if a customer doesn't exist for an order):

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

4. FULL JOIN (or FULL OUTER JOIN):

A FULL JOIN returns rows when there's a match in either the left or right table records. If there's no match, the missing side will contain NULL.

Syntax:

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

Example:

To fetch all customers and all orders, matching where possible:

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

Note: Not all databases support all types of joins. For instance, MySQL doesn't support FULL JOIN. Always refer to the documentation for your specific database system to understand supported features.

Left Join in SQL explained with use cases:
- Description: A Left Join in SQL retrieves all rows from the left table and matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.
- Example Code:
```
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
```
Right Join vs Left Join in SQL differences:
- Description: The main difference between Right Join and Left Join lies in which table's all rows are included in the result. Right Join returns all rows from the right table and matching rows from the left table.
- Example Code:
```
-- Left Join
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- Right Join
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
```
Full Join in SQL with practical examples:
- Description: A Full Join returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the non-matching table.
- Example Code:
```
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
```
Combining multiple tables using Inner Join in SQL:
- Description: An Inner Join in SQL combines rows from two tables based on a specified condition, excluding rows that do not satisfy the condition.
- Example Code:
```
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
```
Right Outer Join in SQL queries and scenarios:
- Description: A Right Outer Join is synonymous with a Right Join. It returns all rows from the right table and matching rows from the left table. Non-matching rows from the left table contain NULL values.
- Example Code:
```
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
```
Full Outer Join in SQL for complete result sets:
- Description: A Full Outer Join returns all rows when there is a match in either the left or right table. It includes non-matching rows from both tables, filling in NULL values for columns without a match.
- Example Code:
```
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
```