Newbie Join Question Part 2
Chris Graner
outer (right outer) joins instead of *= (=*). I figured out the answer and
this morning a colleague asked a question about using the same notation in
Access.
To my dismay, the same notation didn't provide the results that I had
expected. The problem goes like this:
Using the following query in the Query Analyzer I get the expected results.
All of the results are returned from the preserved table, "girls", and the
matching results from "boys" is as well, otherwise a null is returned for
the "boys" entries.
select * from girls left outer join boys on girls.city = boys.city and
boys.city = 'New York'
When I use this type of query in Access I get back an error message stating
that the syntax isn't supported. If I change the syntax and use some
parentheses as follows it works but I don't get the desired results.
i.e.
select * from girls left outer join boys on (girls.city = boys.city and
boys.city = 'New York')
but it's the same as
select * from girls left outer join boys on girls.city = boys.city
where
boys.city = 'New York'
Please Help!
Thanks - Chris
Joe Celko
>> To my dismay, the same notation didn't provide the results that I had
expected. <<
ACCESS sucks and you need to stop using it. That is the real problem
and you know it. Oh well, back to the "real world" ...
>> Using the following query in the Query Analyzer I get the expected
results. All of the results are returned from the preserved
table, "Girls", and the matching results from "Boys" is as well,
otherwise a NULL is returned for the "boys" entries.
SELECT *
FROM Girls
LEFT OUTER JOIN
Boys
ON Girls.city = Boys.city
AND Boys.city = 'New York';
When I use this type of query in ACCESS I get back an error message
stating that the syntax isn't supported. If I change the syntax and use
some parentheses as follows it works but I don't get the desired
results.
SELECT
FROM Girls
LEFT OUYER JOIN
Boys
ON (girls.city = boys.city
AND boys.city = 'New York'); <<
Yes, ACCESS is dead wrong as usual. And they know about it. I got
called in on this one as a consultant, to provide quotes from the SQL-
92 Standard. The Jet Engine gorup wanted to fix the parser, but some
of the product groups in Microsoft have code that depends on these
bugs. You see who won.
but it's the same as
SELECT *
FROM Girls
LEFT OUTER JOIN
Boys
ON Girls.city = Boys.city
WHERE Boys.city = 'New York'
Not quite. Here is how OUTER JOINs work in SQL-92. Assume you are
given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expressio :
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @ = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
... or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-
92. For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;
--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.
Sent via Deja.com http://www.deja.com/
Before you buy.
Steve Dassin
In the server on clause the singular predicate 'boys.city='New York'
works independently of the relational predicate girls.city = boys.city.
In other words only 'girls.city = boys.city' (or the girls.city field
more correctly) drives the number of records
returned,while 'boys.city='New York' has no influence on this number.
The 'boys.city='New York' predicate only determines the values(not
null/null) of boy records based on the returned records.The Access on
clause cannot handle 'both' of these functions in the same way.The only
way it can handle it is to limit the 'returned records' using
both 'girls.city = boys.city' and 'boys.city='New York'.And the only
way it can do this is by moving the 'boys.city='New York' to a where
clause thereby undermining the outer join (ie. turning it into in
essence an inner join thereby eliminating girl cities not in boy
cities).You can achieve the server result in Access with some
workarounds.You can create a recordset of boys with 'boys.city='New
York' and use this in a left outer join with girls or use a subqueries
returning the field(s) your interested in.The value will be null like
in the server query if the predicates are not satisfied.
select * ,(select somefield from boys as a where a.city=girls.city and
boys.city = 'New York')
from girls left outer join boys on (girls.city = boys.city)
Different products,different answers:).Pretty silly huh :).
Steve
For server 7/2000 crosstabs with many options check out
RAC (replacement for Access crosstab query).
Download new version 1.15
www.angelfire.com/ny4/rac/