Join

How can we find out…
  • Which products are provided by which suppliers?
  • Which customers placed which orders?
  • Which customers are buying which products?
Such reports require data from multiple tables. Enter joins.

Syntax
SELECT table1.column, table2.column
FROM table1 JOIN table2
 ON (table1.column=table2.column)
WHERE conditions
 
SELECT Employees.EmployeeID, Employees.FirstName,
 Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees JOIN Orders ON
 (Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate;

Table Aliases

Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases.

SELECT e.EmployeeID, e.FirstName, e.LastName,
 o.OrderID, o.OrderDate
FROM Employees e JOIN Orders o ON
 (e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate;
 
 

Multi-table Joins

Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward.
Syntax
SELECT table1.column, table2.column, table3.column
FROM table1
 JOIN table2 ON (table1.column=table2.column)
 JOIN table3 ON (table2.column=table3.column)
WHERE conditions
Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following.
SELECT table1.column, table2.column, table3.column
FROM table1
 JOIN table3  ON (table2.column=table3.column)
 JOIN table2 ON (table1.column=table2.column)
WHERE conditions
The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1.

/*
Create a report showing the Order ID, the name of the company that placed the order,
and the first and last name of the associated employee.
Only show orders placed after January 1, 1998 that shipped after they were required.
Sort by Company Name.
*/

/******************************
Both of the queries below will work in SQL Server


MySQL
******************************/
SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
 JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
 JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1998-01-01'
ORDER BY c.CompanyName;
 
 Example
--------------- 
 
USE pubs
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id JOIN titles t
   ON ta.title_id = t.title_id
WHERE t.type = 'trad_cook'
ORDER BY t.title ASC 
 
 
 

 
Labels: