I've been avoiding ANSI joins for awhile, and am finally doing it because of this scenario -- gap checking.
I need to outer-join 3 tables, A, B, C using shared column X. The problem is to find all gaps in the 1:1:1 (???) relationships between them by listing all relationships or lack thereof. This is an oversimplification of the actual problem (which attempts several columns), but at least maybe I can get the structure and syntax right. I haven't yet found good examples online.
create table A (x int null, y int null)
create table B (x int null, y int null)
create table C (x int null, y int null)
insert A (x,y) values (1, 91)
insert A (x,y) values (2, 92)
insert A (x,y) values (3, 93)
insert B (x,y) values (1, 94)
insert B (x,y) values (2, 95)
insert C (x,y) values (1, 96)
SELECT A.y, B.y, C.y
FULL OUTER JOIN B ON B.x = A.x OR B.x = C.x
FULL OUTER JOIN C ON C.x = A.x OR C.x = B.x
This does not work:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "C.x" could not be bound.
It's a little circular, but I'm surprised sql server couldn't resolve it, unless my syntax is way off.
I'm sure this is a multi-parter. I *am* RTFM-ing in the meantime, but thanks for any quick help.
I don't understand how the optimizer can infer the exact relationship of B and C because the joins are explicity with respect to A (all cardinality), even if an outer join. Maybe it can in this case because they share key X. What if they all related differently?
SELECT A.*, B.*, C.*
FULL OUTER JOIN B ON B.x = A.x
FULL OUTER JOIN C ON C.y = A.y OR C.z = B.z
Does this property still hold because of the relationship of the rows, and not the specific equality? (I'm curious why you referred to it as "transitive propert of inequality")
(Transitive property -- that's one of those concepts they teach as rote vocabulary in school and is quickly forgotten .)
...which is why you should use an inner join to find relationships. The query optimizer isn't infering anything.
Logically, you are concluding equality by proving false inequality.
You must decide on your desired results. Can you acheive it in a single query?
Well, for the specific problem I want to identify all rows and their relationships or lack of relationships. A full outer join should reveal both the relationships and the lack of relationships at once, distinguishable by nulls. That's just considering a 1:1:1 relationship (or rather, a 0..1:0..1:0..1 relationship).
This appears to have done just that.
What I don't understand is the semantics behind this and what the syntax actually denotes. I'm also not seeing a fundamental advantage of the ANSI syntax itself, except for allowing specific query types which the previous syntax could have represented more simply (eg: A.x *=* B.x).
;with Xs as ( select x from a union select x from b union select x from c )select Xs.*,a.y,b.y,c.y from Xs left join a on a.x = Xs.x left join b on b.x = Xs.x left join c on c.x = Xs.x
- Edited by Vernard Wednesday, September 18, 2013 1:27 AM used reservered word for variable