Usage and examples of SQL JOIN keyword

SQL Keyword Reference

INNER JOIN

The INNER JOIN command returns rows from two tables that have matching values.

The following SQL selects all orders with customer information:

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

Note:The INNER JOIN keyword selects all rows from both tables when there is a match between the columns. If there are records in the 'Orders' table that do not match with the 'Customers' table, these orders will not be displayed!

The following SQL statement selects all orders that contain customer and shipper information:

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID);
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

LEFT JOIN

The LEFT JOIN command returns all rows from the left table and matching rows from the right table. If there is no match, the result from the right side is NULL.

The following SQL will select all customers and any orders they may own:

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

Note:The LEFT JOIN keyword returns all records from the left table (Customers) even if there is no match in the right table (Orders).

RIGHT JOIN

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

The following SQL will return all employees and all orders they may have placed:

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

Note:The RIGHT JOIN keyword returns all records from the right table (Employees) even if there is no match in the left table (Orders).

FULL OUTER JOIN

FULL OUTER JOIN returns all rows when there is a match in either the left or right table.

The following SQL statement selects all customers and all orders:

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

Note:The FULL OUTER JOIN keyword returns all rows from the left table (Customers), as well as all rows from the right table (Orders). If some rows in 'Customers' do not have a matching item in 'Orders', or if some rows in 'Orders' do not have a matching item in 'Customers', these rows will also be listed.

SQL Keyword Reference