January 14, 2011 at 8:57 am
This is a theoretical question. I frequently use multiple join conditions separated by AND. Recently I had a situation that prompted me to attempt two join conditions separated by OR. I discovered in testing that the results were not as I expected.
Consider this scenario:
CREATE TABLE #AAA (Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TABLE #BBB (Col1 VARCHAR(10))
INSERT INTO #AAA (Col1, Col2) VALUES ('ABC', 'DEF')
INSERT INTO #AAA (Col1, Col2) VALUES ('ABC', 'ABC')
INSERT INTO #AAA (Col1, Col2) VALUES ('DEF', 'DEF')
INSERT INTO #BBB (Col1) VALUES ('ABC')
INSERT INTO #BBB (Col1) VALUES ('DEF')
SELECT A.*
FROM #AAA A
INNER JOIN #BBB B ON (A.Col1 = B.Col1 OR A.Col2 = B.Col1)
DROP TABLE #AAA
DROP TABLE #BBB
I expected the SELECT statement to yield three records, like this:
Col1Col2
ABCDEF2
ABCABC
ABCDEF
But instead it yielded:
Col1Col2
ABCDEF
ABCABC
ABCDEF
DEFDEF
Can anyone tell me why this query produces four records instead of three? I expected the OR to work in the same way as it would in a WHERE clause. But no.
Any wisdom on this point of curiosity would be appreciated.
January 14, 2011 at 9:37 am
A.Col1 = B.Col1 matches INSERT INTO #AAA (Col1, Col2) VALUES ('DEF', 'DEF') and INSERT INTO #BBB (Col1) VALUES ('DEF')
no?
--
Thiago Dantas
@DantHimself
January 14, 2011 at 9:43 am
I believe this is because the OR clause doesn't mean you are selecting how many rows out of tablA match, but rather you are making two comparisons. The 3 rows in TableA.col1 and the 3 rows in TableA.col2. So out of essentially 6 possible rows (same as the cross join of the tables), there are 4 combinations that match.
That's my understanding, but I'll ask a few people if they agree.
January 14, 2011 at 9:51 am
as i understood the question, he was trying to figure out how DEF DEF ended up in the results.
Steve explains it well, that what he is really doing here is cross joining the two tables, producing 6 possible results then doing the matching with each column producing final 4 results
--
Thiago Dantas
@DantHimself
January 14, 2011 at 10:00 am
I confused things a little by typing the result set wrong. I expected:
ABCDEF
ABCABC
DEFDEF
and got:
ABCDEF
ABCABC
ABCDEF
DEFDEF
In other words, (ABC, DEF) got doubled up. But the responses from Steve and Celko gave me the answer I was looking for. Thanks for your expertise, guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply