EXPERT RESPONSE
A very interesting question, indeed. Let's
explore it a little, starting with some sample data:
tableA tableB tableC
pkA Name BfkA BfkC pkC Colour
1 tom 1 35 34 red
2 bob 1 36 35 green
3 amy 2 34 36 gray
2 35 37 blue
38 white
Your requirement to "show all the records in both tables
even if some of the records do not relate to each other"
sounds a lot like what is called a full outer join.
However, a join always operates on only two tables at a time:
tableA join tableB join tableC
How do we achieve an "outer" result when there
are three tables involved? The answer is to
use an outer join for one of the two joins, and an
inner join for the other.
"Left outer" three-way query
select pkA, Name, PkC, Colour
from tableA
left outer
join tableB
on pkA = BfkA
inner
join tableC
on BfkC = pkC
query result:
1 tom 35 green
1 tom 36 gray
2 bob 34 red
2 bob 35 green
3 amy -- -----
A few observations are in order. Every row from tableA, the left
table, is represented in the result at least once.
If a tableA row is related to more than one tableC row,
then there are that many rows in the result. If a tableA row
is related to no tableC row, then there's only one
row in the result, and its tableC columns are null.
"Right outer" three-way query
select pkA, Name, PkC, Colour
from tableA
inner
join tableB
on pkA = BfkA
right outer
join tableC
on BfkC = pkC
query result:
2 bob 34 red
1 tom 35 green
2 bob 35 green
1 tom 36 gray
- --- 37 blue
- --- 38 white
The right outer three-way query works the same as the left
(except in the other direction, as it were).
Every row of tableC, the right table,
is represented in the result set at least once.
If a tableC row is related to more than one tableA row,
then there are that many rows in the result. If a tableC row
is related to no tableA row, then there's only one
row in the result, and its tableA columns are null.
In both the examples above, one of the two joins
is an outer join, and the other is an inner.
The reason we can use an inner join
for the other join in the many-to-many relationship
is because of foreign keys.
If a row in the right or left table
has a matching row in tableB, the intersection
table, then that row in tableB must have a
matching row in the other table. It's not a question of nulls,
or anything -- it's a matter of relational integrity. If a row
of tableB exists, it must have matching primary rows
in both tables.
"Full outer" three-way query
Uh oh, trouble.
We cannot do a full outer join between, say,
tableA and tableB, because there are no rows of tableB
that don't have a match in tableA, as previously explained.
Similarly, on the other side, we cannot do a full outer join
between tableB and tableC,
because there are no rows of tableB
that don't have a match in tableC.
This response is continued.
|
