What does this Sub Query mean? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

What does this Sub Query mean?

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-05-19 : 21:28:12

Hi, errr, i'm looking at this query written by someone else, and i cant seem to figure out what does the subquery check for? I'm at a bad mental block right now. Can anyone clarify?

SELECT p.policy_id, pv.version, pv.policy_number, p.polstatus_code, pv.start_datetime, P.CANCEL_DATE
FROM policy p, policy_version pv
WHERE p.policy_id = pv.policy_id
and p.current_version = pv.version
and not exists (select null
from transaction
where policy_id = p.policy_id)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-19 : 21:52:01

Are you confused about "not exists" or about "select null..."?

for EXISTS or NOT EXISTS, it doesn't matter what you select, what matters is the where clause. So the statement is selecting rows from policy and policy_version where policy_id doesn't exist in transaction. You could also say:

...and not exists(select 'bluemetal' from transaction where policy_id = p.policy_id)

Be One with the Optimizer
TG

bluemetal
Starting Member

26 Posts

Posted - 2005-05-20 : 00:47:10

Thanks! Yes thats what i was confused with :)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-05-20 : 06:55:30

quote:
Originally posted by bluemetal

Hi, errr, i'm looking at this query written by someone else, and i cant seem to figure out what does the subquery check for? I'm at a bad mental block right now. Can anyone clarify?

SELECT p.policy_id, pv.version, pv.policy_number, p.polstatus_code, pv.start_datetime, P.CANCEL_DATE
FROM policy p, policy_version pv
WHERE p.policy_id = pv.policy_id
and p.current_version = pv.version
and not exists (select null
from transaction
where policy_id = p.policy_id)

TG has already given you a good answer but something for you to remember. The EXISTS is an operator that checks for a true or false condition. What the inner query returns is irrelevant as the EXISTS operator stops as soon as the condition is true. It certainly more efficient than using the IN keyword in this instance.

Hope this helps.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources