[sqlite] When to disambiguate column names in queries?

[Hick Gunter]

When all names are disambiguated, simple typos and cut-and-paste errors become 
less likely to go undetected by the SQL Parser.

See the "No diagnostic given for missing column" thread.

-Urspr?ngliche Nachricht-
Von: Keith Medcalf [mailto:kmedcalf at dessus.com]
Gesendet: Dienstag, 17. M?rz 2015 00:53
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] When to disambiguate column names in queries?


Personally, I'd format it like this to be the most readable:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH,
 SAPSystemCompanyAX.dbo.PorMasteras PM,
 SAPSystemCompanyAX.dbo.PorMasterDetail  as PD,
 SAPSystemCompanyAX.dbo.AprClients   as CL
   WHERE PD.POrder = PH.POrder
 AND PM.POrder = PH.POrder
 AND PD.LineNo > 1
 AND CL.ClientCode = PH.Client
ORDER BY ClientName, POrder, LineNo

or, if you prefer the JOIN syntax:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH
JOIN SAPSystemCompanyAX.dbo.PorMasteras PM
  ON PM.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.PorMasterDetail  as PD
  ON PD.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.AprClients   as CL
  ON CL.ClientCode = PH.Client
   WHERE PD.LineNo > 1
ORDER BY ClientName, POrder, LineNo

I find gratuitous quoting of symbols somewhat obnoxious and distasteful, as 
well as the propensity to (include (extraneous) (brackets) just) for the (sake 
(of (adding (brackets.  I see that your original obviously-ms-generated 
query didn't generate its usual crapload of () -- or perhaps you removed them 
already :)

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-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, 16 March, 2015 11:26
>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] AprCl

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

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 subj

[sqlite] When to disambiguate column names in queries?

[R.Smith]

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] When to disambiguate column names in queries?

[Keith Medcalf]

Personally, I'd format it like this to be the most readable:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH,
 SAPSystemCompanyAX.dbo.PorMasteras PM,
 SAPSystemCompanyAX.dbo.PorMasterDetail  as PD,
 SAPSystemCompanyAX.dbo.AprClients   as CL
   WHERE PD.POrder = PH.POrder
 AND PM.POrder = PH.POrder
 AND PD.LineNo > 1
 AND CL.ClientCode = PH.Client
ORDER BY ClientName, POrder, LineNo

or, if you prefer the JOIN syntax:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH
JOIN SAPSystemCompanyAX.dbo.PorMasteras PM
  ON PM.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.PorMasterDetail  as PD
  ON PD.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.AprClients   as CL
  ON CL.ClientCode = PH.Client
   WHERE PD.LineNo > 1
ORDER BY ClientName, POrder, LineNo

I find gratuitous quoting of symbols somewhat obnoxious and distasteful, as 
well as the propensity to (include (extraneous) (brackets) just) for the (sake 
(of (adding (brackets.  I see that your original obviously-ms-generated 
query didn't generate its usual crapload of () -- or perhaps you removed them 
already :)

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-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, 16 March, 2015 11:26
>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 fix

[sqlite] When to disambiguate column names in queries?

[Darren Duncan]

If you design your database schemas such that, where possible, corresponding 
columns have the same names in all tables, and you do natural joins, the 
problem 
will basically go away. -- Darren Duncan

On 2015-03-16 9:16 AM, 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.
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
> 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.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] When to disambiguate column names in queries?

[Dave Wellman]

Hi Bill,

I'm not sure about the 'sqlite world', most of my sql work is done using a
different dbms, but my 'standard' is to always use alias names anytime I
have two or more tables in a SELECT.

Yes, it can make the sql longer and sometimes it is not strictly necessary
(as you say, only needed for column names  that are not unique), but I've
found that this makes large pieces of sql much easier to read -
particularly when you have to come back to them after a few months in order
to change something.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Drago,
William @ CSG - NARDA-MITEQ
Sent: 16 March 2015 16:17
To: General Discussion of SQLite Database
Subject: [sqlite] When to disambiguate column names in queries?

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.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ<http://www.nardamicrowave.com/>
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com<mailto:William.Drago at L-3COM.com>


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.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

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.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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.