On Sun, Dec 7, 2008 at 12:38 PM, Robert Cummings <robert@xxxxxxxxxxxxx> wrote:
> On Sun, 2008-12-07 at 11:01 -0500, Eric Butera wrote:
>> Sounds like someone thinks they're pretty clever. I'll never
>> understand why interviewers want to ask really odd edge case questions
>> instead of ones that really show practical knowledge. I know that I
>> don't know the syntax to everything. What I do know is where to find
>> it in seconds if I need it. There's better ways of weeding out resume
>> fibbers. :) I've never actually used EXIST before, but maybe now that
>> I've looked at it I'll find a use.
>
> Oh you'll find a use alright... on stupid esoteric interview
> questions :)
>
> Cheers,
> Rob.
I use EXISTS quite a bit for regular queries. These queries are all
roughly equivalent in what they return, but depending on the database
engine and optimizer, they may have different execution plans that
make one perform better than another:
SELECT a.*
FROM sometable AS a, someothertable AS b
WHERE a.keyid = b.keyid
SELECT a.*
FROM sometable AS a
INNER JOIN
someothertable AS b
ON a.keyid = b.keyid
SELECT a.*
FROM sometable AS a
WHERE a.keyid IN (
SELECT b.keyid
FROM someothertable AS b
)
SELECT a.*
FROM sometable AS a
WHERE EXISTS (
SELECT *
FROM someothertable AS b
WHERE a.keyid = b.keyid
)
Now, I realize that in a simple contrived example like this, it is
pointless to quibble much about one over another. But in more complex
queries, especially with nested subqueries, each may have benefits in
different situations. Where I've seen EXISTS shine more is in the
reverse case, where you want to find records in one table that have no
match in another:
SELECT a.*
FROM sometable AS a
LEFT OUTER JOIN
someothertable AS b
ON a.keyid = b.keyid
WHERE b.keyid IS NULL
SELECT a.*
FROM sometable AS a
WHERE a.keyid NOT IN (
SELECT b.keyid
FROM someothertable AS b
)
SELECT a.*
FROM sometable AS a
WHERE NOT EXISTS (
SELECT *
FROM someothertable AS b
WHERE a.keyid = b.keyid
)
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
