|
Database -- Inner Joins by Aaron
Alexander
In this demonstration I will explain to you how joins between tables work in SQL. I
will use the verbose SQL rather than the shortcuts due to the fact that the shortcuts
differ between databases.
I would first like to define some terms that I will be using:
Primary Key(PK): This is the unique field in your table that is used to identify
each record. (Ex: RecID)
Foreign Key(FK): This is a column that references a primary key of another table.
It can have duplicate values.
We have 2 tables defined:
Customer
CustomerID(PK) |
CustomerName |
1 |
Joe
Schmoe |
2 |
Fred
Flintstone |
Sales
ID(PK) |
CustomerID(FK) |
SalesAmount |
3 |
1 |
$1.00 |
4 |
1 |
$22.00 |
5 |
1 |
$3.00 |
6 |
20 |
$22.00 |
Inner Joins
When joining two tables there are two ways to do it. The most common way is the inner
join.
The inner join will return all data where all joined data exists.
Lets look at this example of an inner-join:
SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer INNER JOIN Sales ON
customer.CustomerID = Sales.CustomerID
In our example above, we are selecting all the Customer Names and amount of the sale
where the customer numbers exist in both tables. The result of the query is this:
CustomerName |
SalesAmount |
| Joe
Schmoe |
$1.00 |
| Joe
Schmoe |
$22.00 |
| Joe
Schmoe |
$3.00 |
Note that record ID 6 in the sales table with customer ID of 20 is not in our result.
Since that joined data does not exist we do not see the data.
Note: The SQL above can be written a lot simpler, doing it this way will avoid
confusion when other tables are added:
SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer , Sales where
customer.CustomerID = Sales.CustomerID
Outer Joins
The outer join is useful when we want to return all data from one table, and also
return linked data from another, when it exists, but here is where we differ from the
inner join, we want to return all data from table 1 no matter what.
In our example we want to return all sales, even if there isn’t a valid customer
associated with it.
Here is what our SQL will look like:
SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer RIGHT JOIN Sales ON
Customer.CustomerID = Sales.CustomerID
Our results will look like this:
CustomerName |
SalesAmount |
| Joe
Schmoe |
$1.00 |
| Joe
Schmoe |
$22.00 |
| Joe
Schmoe |
$3.00 |
|
$22.00 |
Notice we did a right join. We chose to select all the data from the right table in our
join statement. What would it look like if we changed to this:
SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer LEFT JOIN Sales ON
Customer.CustomerID = Sales.CustomerID;
Our results:
CustomerName |
SalesAmount |
| Joe
Schmoe |
$1.00 |
| Joe
Schmoe |
$22.00 |
| Joe
Schmoe |
$3.00 |
| Fred
Flintstone |
|
The results gave us all the records from the left table (Customer) and the linked data
from the right.
As with the inner join there are shortcuts for joins, but that depends on which
database you are using. Writing the verbose SQL statement will work on all databases.
I hope this helps clear things up.
Aaron Alexander
|