SQL JOIN Keyword

 The SQL JOIN keyword is used to combine rows from two or more tables based on a related column between them. There are different types of joins, and each is used depending on the required result set:

Types of SQL Joins:

1. INNER JOIN:

   • Combines rows from both tables where there is a match in the related columns.
   • If there is no match, the row is not included in the result.

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

2. LEFT JOIN (or LEFT OUTER JOIN):

   • Combines all rows from the left table and the matched rows from the right table.
   • If there is no match, NULL values are returned for columns from the right table.

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

3. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Combines all rows from the right table and the matched rows from the left table.
   • If there is no match, NULL values are returned for columns from the left table.

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

4. FULL JOIN (or FULL OUTER JOIN):

   • Combines all rows from both tables.
   • If there is no match, NULL values are returned for the missing side.

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

5. CROSS JOIN:

   • Combines every row from the first table with every row from the second table, creating a Cartesian product.
   • No condition is used to combine rows.

   SELECT columns
   FROM table1
   CROSS JOIN table2;
   

6. SELF JOIN:

   • A table is joined with itself. This is typically used when a table has a relationship with itself, such as an employee manager hierarchy.

   SELECT columns
   FROM table1 t1, table1 t2
   WHERE t1.column = t2.column;
   

Example:

Consider the following two tables:

Customers

CustomerID	Name	City
1	John	New York
2	Jane	London
3	Alex	Paris

Orders

OrderID	CustomerID	Amount
101	1	200
102	2	150
103	2	300

INNER JOIN Example:

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

Result:

Name	Amount
John	200
Jane	150
Jane	300

In this case, the INNER JOIN returns only the customers who have placed orders.