Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Thursday January 13, 2005
04:57 PM

Which SQL Style is Better?

[ #22704 ]

We have views that reference more than one table. Our views have a primary table they are based upon and fields from "non-primary tables." To make it very clear which fields are which, there's been a bit of discussion regarding how to make it visually distinct that a particular field is from a table that is not the primary table of the view. Which of the following is clearer?

Using double underscores to separate the other table name from its field name:

SELECT id,
       first_name,
       last_name,
       other_table__id,
       other_table__name
FROM   some_view

Quoting column names and using periods as separators:

SELECT "id",
       "first_name",
       "last_name",
       "other_table.id",
       "other_table.name"
FROM   some_view

Is the latter portable? Is the former too confusing? Which is easier to read? Are there reasonable alternatives?


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Which SQL Style is Better? 12 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • How come you can't just say:

    SELECT id,
                  first_name,
                  last_name,
                  other_table.id,
                  other_table.name
    FROM some_view

    - Jason

    • You have to quote them because it's invalid syntax if you don't them (unless you actually specify the other table in your "FROM" clause.) You see, the dot is standard syntax for indicating that you're pulling the data from another table:

      SELECT name, this.data
      FROM   bar, this
      WHERE  bar.this_id = this.id
  • I don't like calling things ugly, but double underscores are ugly. IMO they're subject to the whims of fonts/editors, so it's hard to tell if there's a single or double underscore.

    My initial reaction was the same as Jason's -- why do you need the quotes at all?

    • See my response above. It's illegal syntax to have a dot in the field name unless you're actually joining on that other table.

    • If two underscores are considred ugly, is there any reason you can't just type:

      SELECT id,
             first_name,
             last_name,
             other_table_id,
             other_table_name
      FROM   some_view

      It's probably pretty obvious that the prefix actually is a table name prefix. At least that's my experience when naming constraints in our databases. They follow that naming convention.

      Ovid, if you can post it, what would real life ex

  • It took me a bit to realise how it was that you had a choice in the matter. Like Purdy I just wondered why you didn't use un-quoted (normal) SQL.

    But of course you're using a view and you're talking about how to name columns in that view in a meaningful way. It's clear once I made that minor mental twist.

    In my opinion don't use the latter. Anything that suggests you can use dots, as you do with normal selects from tables, will probably have people making the mistake of deleting or omitting the quot

    • I agree about not using dots. If you're going to use weird characters in the column names that require quoting, then why use dots, which will only confuse things because of their normal use? You'd be better off using % or $ or / or anything other than dots.

      But really, rather than weird characters or double underscores, if you have to have something why not a prefix like 'NP_' for 'nonprimary' or something? Or maybe you can distinguish by using uppercase?

  • Seriously, don't use either approach.

    What's not clear to me is why you're using the views in the first place. If you're trying to hide or limit access to the underlying tables, then either approach goes counter to the goal. If you're using the views to encapsulate logic or something like that, then choose meaningful names for the columns. There must be some reason why that column is visible in the view -- choose a name that describes its function.

    In any case, letting the underlying tables "show thru" i

    • While this is not my design, in this particular case it works, though it's a long story why. The database, while still being used as a database, is being used in a most unusual fashion. We are not trying to hide or limit access and, because of the way things are structured, these names really are meaningful. It's a long story why but when Bricolage 2 comes out, it will be more clear what is going on.

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.