In this month’s installment of Working SQL, Russell Sinclair
explains the mysteries of the JOIN statement in SQL and how
to use it with both Jet and SQL Server.
IF you use Access enough that you’re reading this
publication, the chances are that you’ve spent a
good deal of time designing queries. You’ve used
the Access or SQL designers to create queries that
link tables together and retrieve data from multiple
tables. Chances are that you’ve also spent at least some
time writing SQL statements yourself to execute from
code or to use in your own queries. But if you want to
get the real power out of any database system, you
need to understand SQL. And the first step in
understanding how to write SQL is understanding
how to create JOINs.
JOINs seem to be a hurdle that users have to jump
when writing SQL statements. The biggest problem
with them is that the documentation on how to write a
JOIN statement in Access is very limited. Also, the
JOIN syntax in Access is totally different from the
JOIN syntax in SQL Server (although you can use
Access syntax in SQL Server—you just don’t want to).
So that everyone is starting from the same page, I’ll
first explain what the different types of JOINs are. If
you already know them (including the one Access
doesn’t support), feel free to skip ahead.
Types of JOINs
There are three basic types of JOIN: INNER, OUTER,
and CROSS. In order to illustrate the effects of the
different types of JOINs, I’ll use the data from the
tables shown in Figure 1, Figure 2, and Figure 3.
INNER JOINs are the most commonly used type
of JOIN. An INNER JOIN combines the data in two or
more tables and returns the data from both tables
where the linked fields match. Any records in either
of the tables that don’t have a corresponding value in
the other table won’t be returned. Using my example
data, an INNER JOIN of tlkpCompanyType with
tblCustomer would yield the results shown in Figure 4.
As you can see, the customer name “Friendly
Building Management” isn’t returned in the results.
Neither are the “GIS” or “Network Support” company
types. This is because INNER JOINs only return data
where the joined fields match. All other data is ...
Read More Here: