Need help with WHERE EXISTS

Database
Enthusiast

Need help with WHERE EXISTS

Hi,

I am Sreekanth. I have a situation where I need some help.

Situation: I want to get county_name, state_cd and state_name from two tables. The common field between the two tables is "state_cd". If I do an Inner Join, I have a product join in the EXPLAIN plan's output. So, I want to avoid that route. My solution would be to to use a "WHERE EXISTS". This would be like:

SELECT
-- COUNTY_TAB.COUNTY_NAME,
STATE_TAB.STATE_CODE, --- contains 50 state postal codes
STATE_TAB.STATE_CODE2, --- contains 1-digit codes for some states
STATE_TAB.STATE_NAME
FROM
STATE_TAB
WHERE EXISTS
(SELECT COUNTY_TAB.COUNTY_NAME FROM
COUNTY_TAB
WHERE
STATE_TAB.STATE_CODE2=COUNTY_TAB.STATE_CD
)

My question:
How can I get COUNTY_TAB.COUNTY_NAME column that is referenced in the "EXISTS" clause?

Is there a possibility?

Thanks to TD's SQL that I have developed good SQL queries. I want to do the same thing in this situation.

Any help is appreciated.

Thanks
2 REPLIES
Enthusiast

Re: Need help with WHERE EXISTS

No you cannot - you do it by using a normal join.
Product Join is not always bad news. If you have a few states and lots of counties, it is more efficient for Teradata to duplicate the states across all Vprocs and do a product join locally to the counties, than redistribute all the counties on the state identifier and join there. It is standard Large Table/ Small Table join strategy.
Make sure you have stats on the County PI and the State identifier on both tables - it will then always use the correct strategy for your data demographics.
Enthusiast

Re: Need help with WHERE EXISTS

Thanks Jimm