Select from two
tables: Example
Run the Orders Query
(Orders Qry on the Query list): It lists
all orders for all customers, without going into line items (order
details), by retrieving related data from the Orders and Customers
tables.
Note the number
of rows and columns; several columns are repeated more often than
strictly necessary.
SELECT
Orders.OrderID,
Orders.CustomerID, Orders.EmployeeID,
Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia,
Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
Customers.CompanyName,
Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Note: The table
names need not be repeated unless the same column names exist in both
tables. The table names are only required in the FROM, JOIN, and
ON clauses, and in the latter, only because the relating column,
CustomerID, has the same name in both tables.
The query syntax shown above follows ANSI (American National Standards
Institute) rules and should work in the latest versions of all
relational databases. Older
syntax includes the join condition in the WHERE clause (theta style).
Note the number of rows and columns in the
result set for the Orders Query and try the same example (with fewer
columns), using the older style and table aliases,
as
follows:
SELECT
o.OrderID, o.EmployeeID, o.OrderDate, o.RequiredDate,
o.ShippedDate, o.ShipVia, o.Freight, c.CompanyName,
c.Address,
c.City, c.Region, c.PostalCode, c.Country
FROM
Customers c, Orders o
WHERE
c.CustomerID = o.CustomerID;