SQL JOIN

In SQL, a JOIN clause is used to combine rows from two or more tables based on a related column between them. Since relational databases often store data across multiple tables, JOINs are essential for retrieving meaningful combined results.

---

🌍 Types of SQL JOINs

INNER JOIN

• Returns rows when there is a match in both tables.

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

LEFT JOIN

• Returns all rows from the left table and matched rows from the right table.

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

RIGHT JOIN

• Returns all rows from the right table and matched rows from the left table.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

FULL OUTER JOIN

• Returns all rows when there is a match in one of the tables.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

CROSS JOIN

• Returns the Cartesian product of both tables (every possible combination).

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

SELF JOIN

• A table joins itself, useful for hierarchical data.

SELECT A.EmployeeName AS Manager, B.EmployeeName AS Employee
FROM Employees A
INNER JOIN Employees B ON A.EmployeeID = B.ManagerID;

---

📊 Comparison Table

JOIN Type	Description	Example Use Case
INNER JOIN	Matches in both tables	Orders with valid customers
LEFT JOIN	All rows from left + matches	Customers with or without orders
RIGHT JOIN	All rows from right + matches	Orders with or without customers
FULL OUTER JOIN	All rows from both tables	Complete dataset with all customers and orders
CROSS JOIN	Cartesian product	Testing combinations
SELF JOIN	Table joins itself	Employee-manager relationships

---

📖 Conclusion

SQL JOINs are the backbone of relational queries, enabling developers to combine data across multiple tables. By mastering INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF JOIN, you can handle complex queries and extract meaningful insights from relational databases.