INNER JOIN
The INNER JOIN operation is fundamental in SQL, combining rows from two or more tables based on a related column. Consider the following:
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example: Suppose you have a ‘Customers’ table and an ‘Orders’ table. An INNER JOIN retrieves rows where there is a match in the ‘CustomerID’ column:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
LEFT JOIN / RIGHT JOIN
LEFT JOIN and RIGHT JOIN retrieve unmatched rows from one table while combining matched rows from the other.
Syntax:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
Example: A LEFT JOIN retrieves all customers and their orders, including those with no matching orders:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
OUTER JOIN
The OUTER JOIN operation combines rows when there is a match in one of the tables. It includes both matched and unmatched rows from the specified tables.
Syntax:
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
Example: Suppose you want a list of all customers and their orders, including unmatched customers and orders:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;