Re: Q on views and performance

On 2008-02-23 08:49, Dean Gibson (DB Administrator) wrote:

Why 10,000 views???  What's wrong with the ONE view above?  You DON'T want to be defining VIEWs based on actual tables VALUES;  leave that to the SELECT.  For that matter, what's wrong with the final SELECT I listed (below)?

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND a2.type = <int2>;

-- Dean

Amendment:  I forgot, that if it's a LEFT JOIN you have to write it as:

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND (a2.type = <int2> OR a2.type IS NULL);

-- Dean

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.