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.
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.