Demystifying JOINs

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Aug-2002 >

Demystifying JOINs

 

Russell Sinclair

 

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:

Demystifying JOINs