On Mon, 2008-12-08 at 10:19 -0500, Andrew Ballard wrote: > 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 > ) > A good example. I guess I've just never come across a need for this query. But still, not having come across a need does not indicate an inability to find such syntax when necessary. That said though, someone else mentioned earlier that maybe the job was teaching about SQL or databases, in which case I guess a more comprehensive need for the knowledge would be necessary beforehand :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
