The Theta Joins
-
Hi,
Please consider the following code
CREATE TABLE Table1( Id TINYINT,Description VARCHAR(5), Indicator CHAR(1))
CREATE TABLE Table2( Id TINYINT,SomeOtherID INT, Indicator CHAR(1))
INSERT INTO Table1 Values(1,'me','A')
INSERT INTO Table1 Values(2,'you','I')
INSERT INTO Table1 Values(3,'he','A')
INSERT INTO Table1 Values(4,'she','I')
INSERT INTO Table2 Values(2,123,'I')
INSERT INTO Table2 Values(3,456,'A')
SELECT T.Decsription,T.ID,AT.SomeOtherID
FROM Table1 AS T JOIN Table2 AS AT
ON T.ID <> AT.ID
The Result that i get is confusing ...I thot only two rows will be selected with the ID 1 , 4 as the same records donot exist in the Table2. PFB The result.
Can anyone explain why these many rows are getting explained and why not only two rows ...Also please explain why the SomeotherID is getting selected in this fashion.(incase of 123, it corresponds to ID 2 but is selected with the ID 1 3 4....same with someotherid 456
Des.. Id SomeotherID
me1123
he3123
she4123
me1456
you2456
she4456
The following query
SELECT distinct T.ID
FROM Table1 AS T , Table2 AS AT
where T.ID <> AT.ID
gives result as 1,2,3,4 where as i expected only 1,4 to be the reusult
-
It doesn't work like that. What you're asking is for every row in Table1, join it to every row in Table2 where the ID is different, so ID 1 and 4 will be joined to both rows in Table 2, ID's 2 and 3 will be joined to one row each.
Are you just trying to select from Table1 where a corresponding value does not exist in Table2?
In which case, you could use either a LEFT JOIN or a NOT EXISTS:
SELECT T.DeSCription,T.ID --,AT.SomeOtherID
FROM Table1 AS T
LEFT JOIN Table2 AS AT
ON T.ID = AT.ID
WHERE AT.ID IS NULL
SELECT T.DeSCription,T.ID --,AT.SomeOtherID
FROM Table1 AS T
WHERE NOT EXISTS (SELECT 1 FROM Table2 AT WHERE AT.Id=T.Id)
-
Because that is how Joins work with NOT EQUALS operator
When u use a JOIN, SQL expands the the entries in the following manner:
Now as u have used NOT EQUALS for the IDs
FROM Table1 AS TJOIN Table2 AS AT
on T.ID <> AT.ID
only the red-colored ones will be chosen because of the fact that the SQL Sever Engine will enforce the ON clause filter only after forming the table as in the picture!
Sounds logical, isnt it?? 🙂
Now, as u could have guessed, if u had used EQUALS operator, then only the following will be chosen:
Now if u want to return the values that exists in only Table1 but not in Table2, use LEFT/RIGHT OUTER Joins. Like :
SELECT DISTINCT T.ID , T.Description,AT.SomeOtherIDFROM Table1 AS T
LEFT JOIN Table2 AS AT
ON T.ID = AT.ID
WHERE AT.Id IS NULL
Hope this helps
-
thanks for postinng thw replies..my doubts are cleared
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply