April 6, 2009 at 12:29 am
Hi,
I have doubt in a query.
Structure of table
CREATE TABLE [Table1] (
[varchar] (3) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Table2] (
[varchar] (3) NOT NULL
) ON [PRIMARY]
GO
Table1 contain E1,E2,E3,E4,E5,E6,E7,E8,E9,10
Table2 contain E2,E5,E7,E9
I want distinct code from table1 which is not present in table2.
Require result should be E1,E3,E4,E6,E8,E10.
iF i WRITE SOMETHING like this
SELECT Table1.code
FROM Table1, Table2
WHERE Table1.code not in(select code from Table2)
GROUP BY Table1.code;
I get the result,but i do not want to use group by or distinct clause.
Can I acheive the desired result without using group by or distinct.
If i don't use group by or distinct,i get lot of records.
Discuss.
Thanks
[font="Verdana"]Regards
Kumar Harsh[/font]
April 6, 2009 at 12:46 am
pandeharsh (4/6/2009)
If i don't use group by or distinct,i get lot of records.
You've got duplicate rows because you've got a cross join. You have both table1 and table2 in the from with no join between them. Furthermore, you have no need of table2 there.
Remove Table2 from the from clause of the outer query, and you won't need distinct or group by.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 12:46 am
pandeharsh (4/6/2009)
SELECT Table1.codeFROM Table1, Table2
WHERE Table1.code not in(select code from Table2)
GROUP BY Table1.code;
I get the result,but i do not want to use group by or distinct clause.
Can I acheive the desired result without using group by or distinct.
If i don't use group by or distinct,i get lot of records.
Hello
Read the section in BOL (Books Online) about joins. This query has no join criteria so is a CROSS JOIN.
You can modify it slightly to get the result you want - table2 doesn't need to be in the FROM list in your query:
SELECT Table1.code
FROM Table1
WHERE Table1.code not in(select code from Table2)
If you wish to experiment with the join syntax, then you could get the same result from this query:
SELECT Table1.code
FROM Table1
LEFT JOIN Table2 ON Table2.code = Table1.code
WHERE Table2.code IS NULL
There are several different ways of writing this query to get the results you want - it's a special case because neither table has duplicate values. A useful exercise for you would be to figure out some of those ways - using for example IN and NOT EXISTS.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2009 at 1:42 am
Hi,
Thanks a lot.
It has also help me clear my concept.
[font="Verdana"]Regards
Kumar Harsh[/font]
April 6, 2009 at 1:50 am
Also check out Except and ALL keywords.
"Keep Trying"
April 6, 2009 at 9:09 am
Hi
Simple tip about WHERE Condition, If you have "n" tables in FROM Clause then you need to have minimum n-1 where conditions to avoid any cross joins.
April 6, 2009 at 9:20 am
Vijaya Kadiyala (4/6/2009)
Simple tip about WHERE Condition, If you have "n" tables in FROM Clause then you need to have minimum n-1 where conditions to avoid any cross joins.
Better suggestion, do the joins in the from clause and it'll be much harder (not impossible) to get a cross join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 7:52 am
Is there any way that we can find out the Cross-Join can happen? with out looking at the data just by lookin at the SQL?
April 7, 2009 at 7:59 am
Yeah, just check the execution plan on every query you make, and it will say you have "no join predicates" between 2 tables. That's a cross join, and the execution plan warns you about this.
These 2 queries give you exact results, and both plans warn you about no join predicates:
select * from Tbl1
inner join Tbl1 on 1 = 1
select * from Tbl1, Tbl2
Cheers,
J-F
April 7, 2009 at 8:04 am
J-F Bergeron (4/7/2009)
Yeah, just check the execution plan on every query you make, and it will say you have "no join predicates" between 2 tables. That's a cross join, and the execution plan warns you about this.These 2 queries give you exact results, and both plans warn you about no join predicates:
select * from Tbl1
inner join Tbl1 on 1 = 1
select * from Tbl1, Tbl2
Thanks J-F...gr8..something new to learn 🙂
April 7, 2009 at 8:08 am
Sure. Look at the join conditions and look for cases where two (or more) tables aren't related to each other in any way. Take this as an example
SELECT * FROM
A, B, C, D
WHERE
A.col1 = B.col1
And C.col1 = D.Col1
A and B are related to each other, C and D are, but there's no relationship between them. This is a cross join.
Trivial to see if you change it so that the joins are done in the from
SELECT *
FROM A
INNER JOIN B ON A.col1 = B.col1
INNER JOIN C ... but there was no criteria specified in the earlier query....
INNER JOIN D ON C.col1 = D.Col1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 8:32 am
Thank you Gail.... 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply