Re: SQL Joins - Mailing list pgsql-sql

From Tom Lane
Subject Re: SQL Joins
Date
Msg-id 19527.1011045715@sss.pgh.pa.us
Whole thread Raw
In response to SQL Joins  (tjennette@thomasnelson.com (Trace))
List pgsql-sql
tjennette@thomasnelson.com (Trace) writes:
> Can someone please tell me what this previously-written SQL is doing? 
> I don't understand how it is structured.

Apparently whoever wrote this doesn't believe in parentheses.  I'd think
it a lot more readable with parentheses and appropriate indentation,
viz:

FROM (  (eStoreManager.estore_dept_prod LEFT OUTER JOIN eStoreManager.estore_product     ON
eStoreManager.estore_dept_prod.sku= eStoreManager.estore_product.sku)  RIGHT OUTER JOIN
(eStoreManager.tblCProductClassLEFT OUTER JOIN eStoreManager.tblCProductClassProduct     ON
eStoreManager.tblCProductClass.ProductClassID= eStoreManager.tblCProductClassProduct.ProductClassID)  ON
eStoreManager.estore_product.sku= eStoreManager.tblCProductClassProduct.sku)
 
FULL OUTER JOIN (eStoreManager.tblCProductClassCreator INNER JOIN eStoreManager.tblCCreator  ON
eStoreManager.tblCProductClassCreator.CreatorID= eStoreManager.tblCCreator.CreatorID)
 
ON eStoreManager.tblCProductClass.ProductClassID = eStoreManager.tblCProductClassCreator.ProductClassID

BTW, since Postgres doesn't have schemas you'd need to lose the
"eStoreManager." prefixes, which aren't doing anything for readability
here either:

FROM (  (estore_dept_prod LEFT OUTER JOIN estore_product     ON estore_dept_prod.sku = estore_product.sku)  RIGHT OUTER
JOIN   (tblCProductClass LEFT OUTER JOIN tblCProductClassProduct     ON tblCProductClass.ProductClassID =
tblCProductClassProduct.ProductClassID) ON estore_product.sku = tblCProductClassProduct.sku)
 
FULL OUTER JOIN (tblCProductClassCreator INNER JOIN tblCCreator  ON tblCProductClassCreator.CreatorID =
tblCCreator.CreatorID)
ON tblCProductClass.ProductClassID = tblCProductClassCreator.ProductClassID

Does that help any?

If you're wondering how I knew where to put the parentheses, it's
because there's no place else they could go and still have a
syntactically valid statement.  So I suppose the author felt they
were unnecessary.  But I think it's more readable with 'em.
        regards, tom lane


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: nested inner join help
Next
From: Jason Earl
Date:
Subject: Re: CREATE TABLE glitch -fix request for 7.2