Pieter Wijnen wrote:
> It is safer however to allways prefix Fields, and stay away from "dangerous"
> ie SELECT A.NAME FROM MYTABLE A
In you example, A is a 'correlation name' or 'alias' to use the
vernacular. I think of 'prefix' as being something quite different e.g.
a representative term or qualifier in a data element name (e.g. the
'last' in 'last_name') or possibly less salubrious connotations e.g.
Hungarian notation to indicate aspects the physical implementation or
the Access UI. Semantic, yes, but then we are discussing SQL syntax
I agree that if you consider a name to be dangerous then you should
avoid it, as would I. I consider 'Name' to be vague (e.g. missing a
representative term or qualifier) rather than dangerous.
I do not consider that using a table correlation name makes a dangerous
column name less dangerous.
I don't agree that a correlation name should *always* be used. For
FROM MYTABLE AS A;
According to the SQL-92 standard, this should materialize a new table,
remove all its rows then disappear, leaving MYTABLE untouched. As we
know, Access/Jet violates the standards, i.e. all rows will be removed
from MYTABLE, but that's no excuse to write non-standard SQL when the
standard syntax is also supported.
However, for vanilla SELECT queries I agree that always using aReference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
correlation name is a good habit to get into, even for one-table
queries (e.g. makes life easier when you need to change it to a
two-table query). I like the idea of specifying each table's
correlation name in the data dictionary for consistency but admit I
usually just use a single letter (not always consistent between
queries) with an incremental number based on nesting e.g. T1, T2, etc.