sql question using IN
-
I want to use the IN statement instead of inner join and need help:
Have three tables that I want to use the IN to build out a query
Table AA has F1
Table BB has F1 and F2
Table CC has F2
Please show me the syntax to use the IN
Thanks,
Jim
-
My advice: Don't.
Use an inner join. SQL Server 2005 always tries to convert IN to a join whenever possible, however sometimes it can't. The reason being is that how the processor evaluates IN is different from how it evaluates a join and the join is usually much, much faster. The problem is that the query processor isn't always smart enough to do so so it's better for the person who writes the code to just write it as a join.
IN is a bad habit to get into. You eventually end up with NOT IN which causes you no end to headaches.
-
But I want to use the IN with a count distinct at the top, want to learn please?
-
I'd still strongly advise against it, I haven't found the case where IN was a better move than a join (PIVOT and UNPIVOT not withstanding). But here it is (with two examples for how it's commonly used):
SELECT column1, column2
FROM table1
WHERE column3 IN ('value1', 'value2', 'value3')
AND column4 IN (SELECT value5 FROM table2 WHERE id > 5)
Use at your own risk.
-
Sounds like an assignment question to me
If it is, don't be afraid of saying so because then we won't bother offering advice such as Aaron's (which was good advice btw). -
Yeah, I had that thought but it's late in the day and I'd like to think I got at least one person helped today (too bad it's not one person at work).
-
Thanks Aaron
I am still not clear and it is a work assignment, I want to link the three tables I mentioned above using the IN, so still not certain about it.
Table AA has F1
Table BB has F1 and F2
Table CC has F2
Select F1 from AA where F1 IN (select F1 from BB where F2 IN ...
from there I don't follow how it should look?
Thanks
Jim
-
As has already been mentioned, IN is generally not a good idea.
The most efficient queries usually use either JOINs or EXISTs depending on what you want.
-- eg of IN
SELECT *
FROM AA A
WHERE A.F1 IN (
SELECT B.F1
FROM BB B
WHERE B.F2 IN (
SELECT C.F2
FROM CC C
)
)
-- eg of EXISTS - probably more efficient
SELECT *
FROM AA A
WHERE EXISTS (
SELECT *
FROM BB B
WHERE B.F1 = A.F1
AND EXISTS (
SELECT *
FROM CC C
WHERE C.F2 = B.F2
)
)
-
My 10 cents worth:
As it has already been mentioned, In is not great, NOT IN is bad. Gives you headaches.
The same goes for the above example of Exists. Using NOT Exists will give you the same headaches.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Ken
Thanks so much for your help, all is working fine for me
Jim
BTW, I like using the IN for this project, am doing a count distinct where the questions being asked are about "the number of ... " I find it easier to focus and the tables are not that large.
-
"The tables are not that large" is a poor excuse.
Fair enough, you may find it easier but rather struggle to understand the right way to do it now than suddenly get faced with a large DB and the code starts running slow.
Why get used to a not-so-great-way to do something when the right way is as easy. With time.
This is not targeted at you, but I hear it time and time again. "The data base small. performant code is not that important." Then bang, your users complain that things are slow.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I agree with the sentiment stated here...but I have one question. IN is poor, granted. But how is NOT IN any worse? You take the result of IN (true, false) and invert it (false, true).
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
It becomes a non SARGable query then. Doing a left outer and left.column is null would be better. As already stated.
http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I find this amazing the point in writing a query to me is to find the answer, the result set. And in my case accuracy is extremely important, so if I use IN statements and I use a few more cycles, oh well.
-
The point is always accuracy. No real reason behind a database if you answers are not accurate. Extra cycles used for the query are relative. If your query is taking 4 hours at it's optimum, a slight mistake on a query could and will tip it over to the plenty hours.
The point is, why not try and learn the fastest way of doing it. You learnt and understood one way, what is stopping you from learning another.
One day, in the future, when you are faced with a VLDB, you might have a bit of egg on your face when a query kills your production box. All because you never bothered.
I'll climb off my soap box now
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply