Re: Query about joining table

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 07:19:10 -0500

Hi,

You are missing the basic CROSS join.

I will use two simple tables, t1 and t2.

t1
f1 f2 ' fields
1 one
1 un
2 two
2 due
2 dva ' data

t2
g1 g2 ' fields
1 alpha
2 beta
3 gamma ' data

--------------------
SELECT f1, f2, g1, g2 FROM t1, t2

will create a combination of all record: each record of the first table is
"joined" with each record of the second table.

1 one 1 alpha
1 one 2 beta
1 one 3 gamma
1 un 1 alpha
1 un 2 beta
1 un 3 gamma
2 two 1 alpha
2 two 2 beta
2 two 3 gamma
2 due 1 alpha
2 due 2 beta
2 due 3 gamma
2 dva 1 alpha
2 dva 2 beta
2 dva 3 gamma

An inner join will remove, from the previous result (logically, the process
of getting the result can differ), the records that do not satisfy the ON
condition

--------------------
SELECT f1, f2, g1, g2 FROM t1 INNER JOIN t2 ON t1.f1 <= t2.g1

f1 f2 g1 g2 t1.f1<=t2.g1
1 one 1 alpha true KEEP IT
1 one 2 beta true KEEP IT
1 one 3 gamma true KEEP IT
1 un 1 alpha true KEEP IT
1 un 2 beta true KEEP IT
1 un 3 gamma true KEEP IT
2 two 1 alpha false DON'T KEEP IT
2 two 2 beta true KEEP IT
2 two 3 gamma true KEEP IT
2 due 1 alpha false DON'T KEEP IT
2 due 2 beta true KEEP IT
2 due 3 gamma true KEEP IT
2 dva 1 alpha false DON'T KEEP IT
2 dva 2 beta true KEEP IT
2 dva 3 gamma true KEEP IT

An outer join will do the same, but if a record of the "preserved" table is
completely eliminated, it is re-injected, with NULL values for the
unpreserved table.

--------------------
SELECT f1, f2, g1, g2 FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.g1

RIGHT, here, mean that t2 (the table at the RIGHT at the word JOIN) is
preserved

f1 f2 g1 g2 t1.f1=t2.g1
1 one 1 alpha true KEEP IT
1 one 2 beta false DON'T KEEP IT
1 one 3 gamma false DON'T KEEP IT
1 un 1 alpha true KEEP IT
1 un 2 beta false DON'T KEEP IT
1 un 3 gamma false DON'T KEEP IT
2 two 1 alpha false DON'T KEEP IT
2 two 2 beta true KEEP IT
2 two 3 gamma false DON'T KEEP IT
2 due 1 alpha false DON'T KEEP IT
2 due 2 beta true KEEP IT
2 due 3 gamma false DON'T KEEP IT
2 dva 1 alpha false DON'T KEEP IT
2 dva 2 beta true KEEP IT
2 dva 3 gamma false DON'T KEEP IT

you will note that table t2 does not have any of its third record left, so,
it will be re-injected:

NULL NULL 3 gamma

to get:

1 one 1 alpha
1 un 1 alpha
2 two 2 beta
2 due 2 beta
2 dva 2 beta
NULL NULL 3 gamma

LEFT and RIGHT designate just WHICH table is preserved. FULL mean to
preserve both tables. On MS SQL Server:

-----------------------
SELECT f1, f2, g1, g2 FROM t1 FULL OUTER JOIN t2 ON 1 = 2

f1 f2 g1 g2
1 one NULL NULL
1 un NULL NULL
2 two NULL NULL
2 due NULL NULL
2 dva NULL NULL
NULL NULL 1 alpha
NULL NULL 2 beta
NULL NULL 3 gamma

(since the condition, always false, would have remove all the rows of the
cross join, thus, all the rows are re-injected).

That is the technique, now, when to use them, that is another matter.

Hoping it may help,
Vanderghast, Access MVP

"Edmund" <anonymous@discussions.microsoft.com> wrote in message
news:12bd801c41649$d4149980$a601280a@phx.gbl...
> What is the difference of inner join and outer join?
> What is a left join and right join?
>
>
> If possible, please use following table to explain
>
> Table 1 Client: Primary key Client ID, Birth date,
> Gender, ethnicity, language, age
>
> Table 2 Orders: Primary key: Order ID & Client ID, Issue
> date
>
> Table 3 OFFENCE: Primary key: Order ID, Name type

Relevant Pages
Loading