Ryan & Dave, Thank you for the replies. It's nice to know my thinking is on the right track.
Regards, -- Bill Drago Senior Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith > Sent: Monday, March 16, 2015 1:26 PM > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] When to disambiguate column names in queries? > > > On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote: > > All, > > > > Some of my Select statements are pretty long and I'm starting to > think it'd be a good idea to always include table names of columns > instead of just when they are not unique. This would make the Select > statements longer, but perhaps easier to understand if the reader knows > where each column is from. > > > > Any thoughts on this? I realize something like this can be highly > subjective, but I'm wondering if there's a generally accepted practice > in the SQLite world. > > I won't try to imagine this applies to the SQLite or any other SQL > world, but I've had good success (In terms of always understanding > queries that needs to be referred back to after some months and > legibility to others plus zero SQL engine confusion or incorrect > queries) when using very short aliasing as a standard. > > I've started the practice out of necessity using MS SQL where automated > queries by default append the table-name to every column which made it > almost illegible and fills up the screen with nonsense text making the > query hard to read from among the riffraff. (People using MS Query via > Excel might be familiar with this example). > > A typical Query offered by some designer might start out like this: > > SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate, > PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines, > PorMasterDetail.LineNo, PorMasterDetail.StockCode, > PorMasterDetail.Price, PorMasterDetail.OrderQty, > (PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost > FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader, > [SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster, > [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail, > [SAPSystemCompanyAX].[dbo].[AprClients] AprClients > WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND > PorMaster.POrder=PorMasterHeader.POrder AND > PorMasterDetail.LineNo>1AND > AprClients.ClientCode=PorMasterHeader.Client > ORDER BY AprClients.ClientName, PorMasterHeader.POrder, > PorMasterDetail.LineNo > > > > which is a wall-of-text mess... > Making use of nothing more than short clear aliases and better > indentation fixes it to read like this: > > SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName, > PM.Lines, PD.LineNo, > PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price) > AS TotCost > FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PH, > [SAPSystemCompanyAX].[dbo].[PorMaster] PM, > [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PD, > [SAPSystemCompanyAX].[dbo].[AprClients] CL > WHERE PD.POrder=PH.POrder AND PM.POrder=PH.POrder AND > PD.LineNo>1 > AND CL.ClientCode=PH.Client > ORDER BY CL.ClientName, PM.POrder, PD.LineNo > > > which is legible to all, works always exactly (no column name is left > to > ambiguity) and easy to understand and is DB-Engine independent. > > The best part being, the more convoluted the query, the larger the > space-saving. > > Note: It also helps to have a bit of convention in place, such as > ALWAYS aliasing [AprClients] to CL which will make you "see" the > meaning of a query faster over time - but be careful, it may happen > that someone else aliases some other table to CL and if you don't check > it, might end up spending hours chasing obscure bugs. > > Good luck, > Ryan > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
