What is JOIN? List the Types of JOIN in SQL

JOIN in SQL is a powerful operation used to combine rows from two or more tables based on a related column between them. This allows you to query and access data from multiple tables simultaneously, providing a comprehensive view of your data.  

Types of JOIN:

1. INNER JOIN:
   • Returns rows that have matching values in both tables.  
   • It's the most common type of join.
2. LEFT (OUTER) JOIN:
   • Returns all rows from the left table, and the matched rows from the right table.
   • If there's no match in the right table, NULL values are returned for those columns.  
3. RIGHT (OUTER) JOIN:
   • Returns all rows from the right table, and the matched rows from the left table.
   • If there's no match in the left table, NULL values are returned for those columns.
4. FULL (OUTER) JOIN:
   • Returns all rows when there is a match in either left or right table.
   • If there's no match in one table, NULL values are returned for that table's columns.  
5. SELF JOIN:
   • A join between a table and itself, often used to compare rows within the same table.  

Example:

Consider two tables: Customers and Orders

Customers:
+----+----------+
| ID | Name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
| 3  | Charlie  |
+----+----------+

Orders:
+----+-------+----------+
| ID | OrderID | CustomerID|
+----+-------+----------+
| 1  | O1     | 1        |
| 2  | O2     | 2        |
+----+-------+----------+

INNER JOIN:

SELECT Customers.Name, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

This will return:

+----------+-------+
| Name     | OrderID|
+----------+-------+
| Alice    | O1     |
| Bob      | O2     |
+----------+-------+

LEFT JOIN:

SELECT Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;

This will return:

+----------+-------+
| Name     | OrderID|
+----------+-------+
| Alice    | O1     |
| Bob      | O2     |
| Charlie  | NULL   |
+----------+-------+

Remember:

• The ON clause specifies the condition for joining the tables.
• The JOIN keyword can be omitted in most cases, but it's often used for clarity.
• You can use multiple JOIN operations to combine more than two tables.

By understanding these JOIN types, you can effectively retrieve and analyze data from relational databases.