Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > SQL Join On: Selecting Data From More Than One Table

---

SELECT can be used to return information from more than one table.

The FROM clause in a SELECT statement can have an expression that identifies one or more tables from which data is retrieved.

The expression can be a single table name, a saved query name, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.

An SQL JOIN ... ON operation has these parts:

SELECT columnItems FROM table1 
   [ INNER | LEFT | RIGHT ] JOIN table2 
   ON table1.column1 comparator table2.column2

If you do not use a JOIN clause to perform SQL join operations on multiple tables, the resulting data source object will not be updateable.

When joining tables, the columnItems must be specified as

SELECT tableName.columnName . . .

to avoid errors when two or more columns from different tables use the same column name.

NOTE: Columns containing Memo or OLE Object data cannot be joined.

INNER JOIN combines records from two tables whenever there are matching values in a column COMMON to both tables.

SELECT columnItems FROM table1 INNER JOIN table2 
   ON table1.column1 comparator table2.column2

Consider the following:

• you need to display all orders listed in the [Order Details] table
• you want to include the ProductName of each order, which is listed in the Products table

Use INNER JOIN to relate the [Order Details] and Products tables based ON the ProductID column, and get the ProductName values from the Products table.

SELECT [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity,
   [Order Details].UnitPrice 
   FROM [Order Details] INNER JOIN Products 
   ON [Order Details].ProductID = Products.ProductID

Run Sample | View Source

You can join any two numeric columns of like types. For example, you can join on AutoNumber and Long columns because they are like types. However, you cannot join Single and Double types of columns.

You can also link several ON clauses in a JOIN statement, using the following syntax:

SELECT columnItems FROM table1 INNER JOIN table2 
   ( ON table1.column1 comparator table2.column1 ) 
   AND ( ON table1.column2 comparator table2.column2 ) 
   OR ( ON table1.column3 comparator table2.column3 )

You can also nest INNER JOIN statements.

LEFT JOIN and RIGHT JOIN also combine records from multiple tables, but in contrast to INNER JOIN, you can create an outer join even if there are no matching values in a column common to the joined tables.

SELECT columnItems FROM table1 [ LEFT | RIGHT ] JOIN table2 
   ON table1.column1 comparator table2.column2

Use a LEFT JOIN operation to create a left outer join, which includes all of the records from the first ( left ) of two tables, even if there are no matching values for records in the second ( right ) table.

Consider the following:

• you need to determine the total number of orders for each ProductCode
• the Products table lists each ProductCode, while the quantities ordered for each ProductCode are listed in the [Order Details] table

Use LEFT JOIN to link the Products and [Order Details] tables ON ProductID as shown below.

SELECT DISTINCT ProductType, ProductCode, 
   Sum( [Order Details].Quantity ) AS SumOfQuantity, 
   ProductName, Products.UnitPrice 
   FROM Products LEFT JOIN [Order Details] 
   ON Products.ProductID=[Order Details].ProductID 
   GROUP BY ProductType, ProductCode, ProductName,
      Products.UnitPrice 
   ORDER BY ProductType, Sum( [Order Details].Quantity ) DESC,
      ProductCode

Run Sample | View Source

Use a RIGHT JOIN operation to create a right outer join, which includes all of the records from the second ( right ) of two tables, even if there are no matching values for records in the first ( left ) table.

Consider the following:

• you need to determine the Total Sales by employee, sorted on Total Sales from highest to lowest
• the employees are listed in the Employees table
• the orders by each employee are listed in the Orders table
• the details of each order are listed in the [Order Details] table

The following demonstrates use of a RIGHT JOIN nested inside an INNER JOIN. The RIGHT JOIN relates the Employees and Orders tables ON EmployeeID, while the INNER JOIN relates these joined tables with the [Order Details] table based ON OrderID.

SELECT DISTINCT LastName, FirstName, Title, 
   Sum( Quantity ) AS [Total Units], 
   Sum( UnitPrice*Quantity ) AS [Total Sales] 
   FROM ( Employees RIGHT JOIN Orders 
      ON Employees.EmployeeID=Orders.EmployeeID ) 
   INNER JOIN [Order Details]
      ON Orders.OrderID=[Order Details].OrderID 
   GROUP BY LastName, FirstName, Title 
   ORDER BY Sum( UnitPrice*Quantity ) DESC

Run Sample | View Source

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.

SQL GROUP BY: Grouping Data In Query Results