Welcome to Lesson 9 of the TSQL Tutorial. In this lesson we will cover inner joins. I hope you enjoy the lesson.
Select Statements
INNER JOIN
For this tutorial, we will need to introduce another table, the
Orders table in the Northwind database. Below is a snapshot of
the Orders table. (we will use only the first 9 records)
|
OrderID
|
CustomerID
|
EmployeeID
|
OrderDate
|
RequiredDate
|
ShippedDate
|
ShipVia
|
Freight
|
|
10248
|
VINET
|
5
|
7/4/1996
|
8/1/1996
|
7/16/1996
|
3
|
32.38
|
|
10249
|
TOMSP
|
6
|
7/5/1996
|
8/16/1996
|
7/10/1996
|
1
|
11.61
|
|
10250
|
HANAR
|
4
|
7/8/1996
|
8/5/1996
|
7/12/1996
|
2
|
65.83
|
|
10251
|
VICTE
|
3
|
7/8/1996
|
8/5/1996
|
7/15/1996
|
1
|
41.34
|
|
10252
|
SUPRD
|
4
|
7/9/1996
|
8/6/1996
|
7/11/1996
|
2
|
51.3
|
|
10253
|
HANAR
|
3
|
7/10/1996
|
7/24/1996
|
7/16/1996
|
2
|
58.17
|
|
10254
|
CHOPS
|
5
|
7/11/1996
|
8/8/1996
|
7/23/1996
|
2
|
22.98
|
|
10255
|
RICSU
|
9
|
7/12/1996
|
8/9/1996
|
7/15/1996
|
3
|
148.33
|
|
10256
|
WELLI
|
3
|
7/15/1996
|
8/12/1996
|
7/17/1996
|
2
|
13.97
|
|
10257
|
HILAA
|
4
|
7/16/1996
|
8/13/1996
|
7/22/1996
|
3
|
81.91
|
|
10258
|
VINET
|
99
|
7/1/1996
|
2/1/1996
|
4/2/1996
|
1
|
77.1
|
Looking at the Orders table above, notice that in the third
column, EmployeeID is used.
While it wouldn't be tough to lookup who employeeID 5 is in the
employees table, this will become tedious and inefficient over time.
What we need is a way to link the two tables together, this is where
we would use a join.
In the SQL-92 standard, inner joins can be specified in either
the FROM or WHERE clause. This is the only type of join that SQL-92
supports in the WHERE clause. Inner joins
specified in the WHERE clause are known as old-style inner joins.
INNER JOIN (definition): A
join that displays only the rows that have a match in both joined
tables.
SELECT field1, field2, field3
FROM table1
INNER JOIN table2
ON
table1.field= table2.field
Example: We want to show which employee these orders belong to.
(Note that in the SELECT section we must specify which table a field
comes from.)
SELECT Employees.LastName,
Employees.FirstName, Orders.OrderID
FROM Employees
INNER JOIN Orders
ON
Employees.EmployeeID = Orders.EmployeeID
Result: (Notice that OrderID 10258 does not show up in the results.
This is due to no
matching EmployeeID 99 in the employees table.)
|
LastName
|
FirstName
|
OrderID
|
|
Buchanan
|
Steven
|
10248
|
|
Suyama
|
Michael
|
10249
|
|
Peacock
|
Margaret
|
10250
|
|
Leverling
|
Janet
|
10251
|
|
Peacock
|
Margaret
|
10252
|
|
Leverling
|
Janet
|
10253
|
|
Buchanan
|
Steven
|
10254
|
|
Dodsworth
|
Anne
|
10255
|
|
Leverling
|
Janet
|
10256
|
|
Peacock
|
Margaret
|
10257
|
Looking at the last SQL statement, it was somewhat long to type,
especially if we want to select more columns. One way to
shorten the typing and make the script more readable is by using a
table alias.
You define a table alias in the FROM clause. We used
e as the alias for employee and
o as the alias for Orders.
Rewriting the above script using a table alias, we get:
SELECT e.LastName, e.FirstName,
o.OrderID
FROM Employees
e INNER
JOIN Orders o
ON e.EmployeeID = o.EmployeeID
Running the script again, we should get the same results.
|
LastName
|
FirstName
|
OrderID
|
|
Buchanan
|
Steven
|
10248
|
|
Suyama
|
Michael
|
10249
|
|
Peacock
|
Margaret
|
10250
|
|
Leverling
|
Janet
|
10251
|
|
Peacock
|
Margaret
|
10252
|
|
Leverling
|
Janet
|
10253
|
|
Buchanan
|
Steven
|
10254
|
|
Dodsworth
|
Anne
|
10255
|
|
Leverling
|
Janet
|
10256
|
|
Peacock
|
Margaret
|
10257
|