| Author |
Topic |
|
matej
Starting Member
2 Posts |
 Posted - 2004-09-03 : 10:54:19
|
| Hello,I have two tables (I'll use some sample data to show my problem):Table Users, with fields UserId and UserNameUserId ... UserName1 ... John2 ... Jim3 ... Tom4 ... TimTable ProjectsUsers, with fields UserId and ProjectIdUserId ... ProjectId1 ... 111 ... 122 ... 102 ... 123 ... 103 ... 124 ... 10So, at one point I would like to see who is working on project with ProjectId 10. I write:SELECT * FROM Users, ProjectsUsers WHERE Users.UserId = ProjectsUsers.UserId AND ProjectsUsers.ProjectId=10That works ok. I get:UserId ... UserName2 ... Jim3 ... Tom4 ... TimBut now for the problem: How do I get everybody who is in table Users, but do not work on project with ProjectId 10?The expected result would be:UserId ... UserName1 ... JohnI realize it's probably dead simple, but I'm having trouble with it so please help. Thanks :) |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-03 : 10:59:02
|
| SELECT * FROM Users as AWHERE not exists(Select 1 From ProjectUsers Where UserId = A.UserId AND ProjectId=10)Corey |
 |
|
|
matej
Starting Member
2 Posts |
Posted - 2004-09-03 : 12:20:22
|
| Thanks! |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-09-03 : 15:19:55
|
| Corey,Wouldn't this statement perform better?[CODE]SELECT * FROM dbo.Users ALEFT JOIN dbo.ProjectUsers PUON a.UserID = PU.UserIDAND PU.ProjectID = 10WHERE PU.UserID IS NULL[/CODE] |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-03 : 16:38:57
|
| I'm not sure. Easiest thing to do would be to check the execution plans for both.Although, in your suggestion, the query is trying to return all of the columns, not just the Users columns (very minor difference). It seems like any difference would depend on the comparison of exists to a join.I think in some cases exist can be better as you may not need to process through multiple matches, you only need to find the first one. I don't know that it would make a difference here.matej - what can you tell us? is the second one faster?Corey |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-03 : 17:01:50
|
| Hey what's with the "SELECT 1" in the NOT EXISTS clause Corey ?Isn't "SELECT *" the ANSI way ?"SELECT *" in the from clause is bad practice matej,but maybe it was just to save typing in the post...rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-03 : 17:04:51
|
| "select 1" b/c there is no need to return column info. We just want to know about existence... I read it here somewhere...Corey |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-03 : 17:11:09
|
| For exists and not exists, SELECT * is the fastest way apparently. It's the only time SELECT * should be used.Tara |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-03 : 17:16:33
|
quote:
Originally posted by Seventhnight
"select 1" b/c there is no need to return column info. We just want to know about existence... I read it here somewhere...Corey
Doesn't need to return columninfo with select *, it's just an existance test.Maybe this is a myth that was true for some other dbms rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-04 : 00:40:36
|
Oh well... i suppose you can not only learn here, but also unlearn...  Corey |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-09-04 : 09:46:11
|
| I read somewhere that the reason SELECT * is quicker for EXISTS is that SQL Server can determine the best index to use for subquery, and that certainly helps when you have clustered index on the table.OS |
|
|
|
|
newbie: "invert" query results problem