Joins

Contact Us or call 1-877-932-8228
Joins

Joins

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

Creating a report that returns the employee id and order id from the Orders table is not difficult.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-NoJoin.sql
-- Find the EmployeeID and OrderID for all orders

SELECT EmployeeID, OrderID
FROM Orders;

But this is not very useful as we cannot tell who the employee is that got this order. The next sample shows how we can use a join to make the report more useful.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-EmployeeOrders.sql
-- Create a report showing employee orders.

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

The above SELECT statement will return the following results:

Table names are used as prefixes of the column names to identify the table in which to find the column. Although this is only required when the column name exists in both tables, it is always a good idea to include the prefixes as it makes the code more efficient and easier to read.

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.

An alias can be called whatever you want. Though typically it's the first letter(s) of the table name, it can be whatever makes sense to you as the Developer. For example, the alias for a table called Courses can be c, or crs, or debbie, etc.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-Aliases.sql
-- Create a report showing employee orders using 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.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-MultiTable.sql
/*
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

Oracle
******************************/
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 > '1-Jan-1998'
ORDER BY c.CompanyName;

/******************************
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;

The above SELECT statement will return the following results:

Next