Hi all,
I am confused where to use "exists" and where to use "in" clause...they sound similar...i am totally confused..will you please make me clear ?

Thank you

use EXISTS when you need to ensure something exists

use IN when you need something to be in a list of things

examples

SELECT name FROM students
WHERE EXISTS (SELECT * FROM awards WHERE name = students.name)

SELECT name FROM students
WHERE teacher IN ( 'Smith','Jones' )

As a rule of thumb, the subquery of an "IN" will almost never be correlated, while the subquery for an "EXISTS" will always be correlated.
"Correlation" means that the subquery contains a condition that refers to (one of the) tables in the outer query.

EXISTS does not always have to be correlated

SELECT apology FROM excuses WHERE sincerity='high'
AND EXISTS (SELECT game FROM nfl WHERE gamedate=current_date)

i.e. i can't talk to you tonight if there's a game on

Agreed. But that would be an "atypical" case. That's why I'm calling it just a "rule of thumb"; the exceptions prove the rule...

ah, i see

so when you say "always", you really mean "always, except when it isn't"

Let's say that I mean "always, unless you are 100% aware of what you are doing and why you're doing it that way and not differently".

Good lord - a single game takes all day?

pootle flump ur codings are working excelent.

no, you're thinking of sticket, um, wicket, er, ah, picket, um, oh yeah, cricket

"a hunnert and fifty two over and under with nobody out and a man on third after the fifth day of the test"

nfl games are scheduled on a single day only

Anyway correlation means that the subquery will be executed for every produced row in the main query, where with 'IN' first the subquery wil be executed once and after that the main query... Can make a big difference in performance if both querytypes are interchangeable. (And to my knowledge is the EXISTS construct designed for the subquery to contain a reference to a column in the main query. If it can evaluate to TRUE without this reference doesn't imply something meaningful will be produced)

Regards

actually "subquery will be executed for every row in the main query" is a popular misconception

subqueries are usually executed as joins

but you are right, a non-correlated subquery can be evaluated once only, so it is much more efficient

however, please do not suggest that a non-correlated subquery isn't meaningful