Query about joining table

  • Thread starter Thread starter Edmund
  • Start date Start date
E

Edmund

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
 
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
 
Back
Top