A SQL JOIN operation is used to combine rows from two or more tables based on a related column between them. There are different types of JOINs in SQL:
1. INNER JOIN: Returns records that have matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.
4. FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left (table1) or right (table2) table records.
5. CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows.
The basic syntax for a JOIN operation is as follows:
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;
Example: Suppose Orders and Customers are the two tables with possible fields:
1. INNER JOIN:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
2. LEFT JOIN:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
3. RIGHT JOIN:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
4. FULL JOIN:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
5. CROSS JOIN:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;
MYSQL join Operations
