What is the JOIN operator in SQL and how is it used to combine data from different tables?

The JOIN operator in SQL is used to combine rows from two or more tables based on a related column between them.

Types of JOIN:

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If there is no match, the result for the right table will be NULL.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there is no match, the result for the left table will be NULL.
• FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in one of the tables. Combines the results of LEFT and RIGHT JOIN.

Example of INNER JOIN:

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

Example of LEFT JOIN:

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

Example of RIGHT JOIN:

SELECT Employees.LastName, Orders.OrderID
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

Example of FULL JOIN:

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