The JOIN keyword is used in an INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN to return matching rows from two tables.

The INNER JOIN keywords returns rows that have matching values in both tables.

The following returns all orders that have customer information.

SELECT Orders.order_id, Customers.last_name, Customers.first_name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;

The LEFT JOIN keywords return all rows from the left table, and the matching rows from the right table. The result is NULL from the right side if there is no match.

The following returns all customers and any orders they may have.

SELECT Customers.last_name, Customers.first_name, Orders.order_id
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id
ORDER BY Customers.last_name;

The RIGHT JOIN keywords returns all rows from the right table, and the matching rows from the left table. The result is NULL from the left side if there is no match.

The following returns all employees and any orders they may have.

SELECT Orders.order_id, Employees.last_name, Employees.first_name
FROM Orders
RIGHT JOIN Employees ON Orders.employee_id = Employees.employee_id
ORDER BY Orders.order_id;

The FULL OUTER JOIN keyword returns all rows when there is a match in either the left table or right table. The returns selects all customers and all orders.

SELECT Customers.last_name, Customers.first_name, Orders.order_id
FROM Customers
FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id
ORDER BY Customers.last_name;

---