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 |
LEFT OUTER JOIN
LEFT OUTER JOIN (definition): All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the
right table do not appear.
An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:
A LEFT JOIN is the same as a LEFT OUTER JOIN depending on the database.
LEFT OUTER JOIN (usage)
SELECT field1, field2, field3
FROM table1 LEFT OUTER JOIN table2
ON table1.field= table2.field
Example: This is basically the same query we used for the INNER JOIN example.
SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e LEFT OUTER JOIN Orders o
ON e.EmployeeID = o.EmployeeID
Result: Notice that the LEFT OUTER JOIN vs the INNER JOIN
LEFT OUTER JOIN | INNER JOIN | |||||
LastName | FirstName | OrderID | ||||
Davolio | Nancy | |||||
Fuller | Andrew | |||||
Callahan | Laura | |||||
Smith | John | LastName | FirstName | OrderID | ||
Buchanan | Steven | 10248 | Buchanan | Steven | 10248 | |
Suyama | Michael | 10249 | Suyama | Michael | 10249 | |
Peacock | Margaret | 10250 | Peacock | Margaret | 10250 | |
Leverling | Janet | 10251 | Leverling | Janet | 10251 | |
Peacock | Margaret | 10252 | Peacock | Margaret | 10252 | |
Leverling | Janet | 10253 | Leverling | Janet | 10253 | |
Buchanan | Steven | 10254 | Buchanan | Steven | 10254 | |
Dodsworth | Anne | 10255 | Dodsworth | Anne | 10255 | |
Leverling | Janet | 10256 | Leverling | Janet | 10256 | |
Peacock | Margaret | 10257 | Peacock | Margaret | 10257 |
The LEFT OUTER JOIN returned results for ALL employees, even one without orders.
RIGHT OUTER JOIN
RIGHT OUTER JOIN (definition): All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included.
An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:
A RIGHT JOIN is the same as a RIGHT OUTER JOIN depending on the database.
RIGHT OUTER JOIN (usage)
SELECT field1, field2, field3
FROM table1 RIGHT OUTER JOIN table2
ON table1.field= table2.field
Example: This is basically the same query we used for the INNER JOIN example.
SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e RIGHT OUTER JOIN Orders o
ON e.EmployeeID = o.EmployeeID
Result: Notice that the RIGHT OUTER JOIN vs the INNER JOIN
RIGHT OUTER JOIN | INNER JOIN | |||||
LastName | FirstName | OrderID | ||||
Buchanan | Steven | 10248 | LastName | FirstName | OrderID | |
Suyama | Michael | 10249 | Buchanan | Steven | 10248 | |
Peacock | Margaret | 10250 | Suyama | Michael | 10249 | |
Leverling | Janet | 10251 | Peacock | Margaret | 10250 | |
Peacock | Margaret | 10252 | Leverling | Janet | 10251 | |
Leverling | Janet | 10253 | Peacock | Margaret | 10252 | |
Buchanan | Steven | 10254 | Leverling | Janet | 10253 | |
Dodsworth | Anne | 10255 | Buchanan | Steven | 10254 | |
Leverling | Janet | 10256 | Dodsworth | Anne | 10255 | |
Peacock | Margaret | 10257 | Leverling | Janet | 10256 | |
10258 | Peacock | Margaret | 10257 |
The Right OUTER JOIN returned results for OrderID 10258 even though it did not have a corresponding employee.
The RIGHT and LEFT Joins are basically the same, the only thing matters is the order of the tables.
FULL OUTER JOIN
FULL OUTER JOIN (definition): All rows in all joined tables are included, whether they
are matched or not.
An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows
to be included:
A FULL JOIN is the same as a FULL OUTER JOIN depending on the database.
FULL OUTER JOIN (usage)
SELECT field1, field2, field3
FROM table1 FULL OUTER JOIN table2
ON table1.field= table2.field
Example: This is basically the same query we used for the INNER JOIN example.
SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e FULL OUTER JOIN Orders o
ON e.EmployeeID = o.EmployeeID
Result: Notice that the FULL OUTER JOIN vs the INNER JOIN
FULL OUTER JOIN | INNER JOIN | |||||
LastName | FirstName | OrderID | ||||
Davolio | Nancy | |||||
Fuller | Andrew | |||||
Callahan | Laura | |||||
Smith | John | |||||
Buchanan | Steven | 10248 | LastName | FirstName | OrderID | |
Suyama | Michael | 10249 | Buchanan | Steven | 10248 | |
Peacock | Margaret | 10250 | Suyama | Michael | 10249 | |
Leverling | Janet | 10251 | Peacock | Margaret | 10250 | |
Peacock | Margaret | 10252 | Leverling | Janet | 10251 | |
Leverling | Janet | 10253 | Peacock | Margaret | 10252 | |
Buchanan | Steven | 10254 | Leverling | Janet | 10253 | |
Dodsworth | Anne | 10255 | Buchanan | Steven | 10254 | |
Leverling | Janet | 10256 | Dodsworth | Anne | 10255 | |
Peacock | Margaret | 10257 | Leverling | Janet | 10256 | |
10258 | Peacock | Margaret | 10257 |
The FULL OUTER JOIN returned unmatched results for both tables. Depending on the database unmatched records will be displayed with a "blank" or a <null>.
CROSS JOIN
CROSS JOIN (definition): A cross join returns the Cartesian product of the sets of rows from the joined tables. Basically all the possible combinations from table1 and table2.
Take for example Table1,
EmployeeID | FirstName |
1 | John |
2 | Mary |
3 | Bill |
4 | Larry |
CROSS JOIN with Table2.
DeptID | DeptName |
100 | Finance |
200 | Manufacturing |
300 | Customer Service |
The result would be:
EmployeeID | FirstName | DeptID | DeptName |
1 | John | 100 | Finance |
2 | Mary | 100 | Finance |
3 | Bill | 100 | Finance |
4 | Larry | 100 | Finance |
1 | John | 200 | Manufacturing |
2 | Mary | 200 | Manufacturing |
3 | Bill | 200 | Manufacturing |
4 | Larry | 200 | Manufacturing |
1 | John | 300 | Customer Service |
2 | Mary | 300 | Customer Service |
3 | Bill | 300 | Customer Service |
4 | Larry | 300 | Customer Service |
Cross Joins are used to generate all possible combinations of records from tables that do not share a common element. If Table1 and Table2 are two sets then cross join = Table1 X Table2.
Just remember that a CROSS JOIN with large tables could cause your database to come to a screeching halt. Notice that table1 has 4 records and table2 has 3 records. The cross join produced 12 rows (3x4=12). Imagine if both tables had thousands or millions of records... The result would be very large.