Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL style guide

sqlstyle.guide

Post reply on HN
Print view View original

111–120 of 151 posts

Re: SQL style guide

#111

Post by coldtea »

justinlardinois wrote: post #100
NickNameNick wrote: post #98

Earlier quoted context omitted.

Only if they're meaningful. SQL isn't BASIC, and we aren't limited to x (1,2,4, etc) character variable names. Why not just use the existing meaningful name? They aren't that hard to type, and you might even be able to use autocompletion, depending on environment. I've spent far too much time re-writing queries to remove obfuscating aliases to accept a blanket statement that they make it easier to get your head aroun…

In my line of work I often end up working with queries where you'll have a handful of JOINs and need to SELECT a dozen or more columns. In those cases writing out staff.foo or students.bar gets old really fast. I agree that s1 and s2 are bad though, as I said in another comment.

>In my line of work I often end up working with queries where you'll have a handful of JOINs and need to SELECT a dozen or more columns. In those cases writing out staff.foo or students.bar gets old really fast.

It's better to have it "get old" though, than to introduce subtle bugs because of similar, short, aliases.

Re: SQL style guide

#112

Post by mobiuscog »

mattmanser wrote: post #30

Try to use only standard SQL? Stopped reading right there. There is no way you can port SQL Server to MySQL to Oracle to PLSQL without rewriting virtually every single query. They're all too different. So why bother? Write idiomatic code that other programmers used to that engine will understand. Not only that but different engines like different things. That blazingly fast nested subquery in MS SQL will become a mas…

Supporting SQL Server, MySQL, Oracle, Postgres, DB2 and some others with standard SQL on a large Enterprise codebase here.

Works just fine.

Could we override anything that needs better performance with custom queries ? Sure, and we occasionally do, but for 95% of use-cases, it's irrelevant.

Of course, we'd love to just focus on one database, but don't get confused in this day of cloud-everything that 'suitable' performance for very large companies isn't possible.

Re: SQL style guide

#113

Post by jaziek »

ZenoArrow wrote: post #56
Stasis5001 wrote: post #47

Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…

Aliasing tables is very useful. Aside from giving shorter names and helping you to be explicit when selecting fields, they also end up being useful for working with IntelliSense (or the equivalent in the tool you're using). Plus, you can make them memorable enough (e.g. in the first example you shared, could use 'S' for staff and 'ST' for students) for the query you're working on and shortening the query code makes i…

> How many times are you likely to switch database engines?

Not often, but you may wish to support multiple engines at once, and it makes your code a lot easier to manage if you don't have to have a separate set of queries for each engine.

Re: SQL style guide

#114

Post by carterehsmith »

dghf wrote: post #14

Can anyone explain what this means? > 1. The key should be unique to some degree. If it's talking about primary keys (which it seems to be), it should be unique, full stop. If it's not, where does uniqueness come in? Is it actually talking about indices, which, if I understand correctly, should have a wide spread of values (without any of them necessarily being unique) if they're to be useful?

It is just the terminology.

Technically, a key is any column (or a set of columns) that you use to find records. It does not need to be unique. But it tends to be more useful if it is more selective, I guess that's why they say 'to some degree'.

So now, if a key is also unique, it is called a 'candidate key'.

In general, you can have more than one candidate key in a table. For example, a natural key (such as order#) and a surrogate key (e.g. 'id').

Among candidate keys, you pick one, and call it 'primary key'.

Re: SQL style guide

#115

Post by berntb »

wvenable wrote: post #70
mattmanser wrote: post #30

Try to use only standard SQL? Stopped reading right there. There is no way you can port SQL Server to MySQL to Oracle to PLSQL without rewriting virtually every single query. They're all too different. So why bother? Write idiomatic code that other programmers used to that engine will understand. Not only that but different engines like different things. That blazingly fast nested subquery in MS SQL will become a mas…

I completely agree with this. One should never plan to change database engines -- it just happens so rarely and it's such a big deal that planning for it is a waste of time. It would be like coding in Java in such a way to make porting to C# easier in the future (or vice-versa).

So it isn't true anymore that quickly growing companies often need to go from one of the open source DBs to Oracle, after a decade or so?

(OK OK, that is a good problem to have.)

Right now I miss Oracle. (Never thought I'd say that. :-) )

Re: SQL style guide

#116

Post by mattmanser »

mobiuscog wrote: post #112
mattmanser wrote: post #30

Try to use only standard SQL? Stopped reading right there. There is no way you can port SQL Server to MySQL to Oracle to PLSQL without rewriting virtually every single query. They're all too different. So why bother? Write idiomatic code that other programmers used to that engine will understand. Not only that but different engines like different things. That blazingly fast nested subquery in MS SQL will become a mas…

Supporting SQL Server, MySQL, Oracle, Postgres, DB2 and some others with standard SQL on a large Enterprise codebase here. Works just fine. Could we override anything that needs better performance with custom queries ? Sure, and we occasionally do, but for 95% of use-cases, it's irrelevant. Of course, we'd love to just focus on one database, but don't get confused in this day of cloud-everything that 'suitable' perfo…

It's a different matter when you're approaching from a "support everything" perspective.

And to do that, I assume you're handcrafting all your queries, have to teach every developer how to do that in a supported cross-engine way and are severely limited in the set of functionality that you can actually use.

A simple example is that both My SQL and SQL Server's tools love adding ` an [ to their auto-generated scripts, something you simply could not tolerate.

But if I want to add a particular constraint to SQL, I can do it using the GUI in management studio, click the script option, and viola, a portable script without any effort on my part to remember the arguments, syntax, etc. of a fairly obscure command that you might write a couple of times a year.

Re: SQL style guide

#117

Post by carterehsmith »

wvenable wrote: post #75
HalfwayToDice wrote: post #62

Regarding lower-case/hungarian notation etc, the membership table in my database is: tblMember --------- MemberID MemberUsername MemberDateJoined etc Is this very non-standard formatting for SQL?

Why prefix your tables with tbl? Do you prefix your functions with "fun" and your variables with "var" and your classes with "cls"? It's completely redundant.

Some people do it to avoid clashes with reserved words.

Example: in some DBMSs, you cannot name your table 'user' because it is a reserved word.

"user" (double quotes) works, but there may be gotchas as sometimes double quotes suddenly make identifiers case-sensitive... but only if the filesystem is case-sensitive... things like that.

So you can either deal with the gotchas or say fuck it, put a prefix "tbl" and call it a day.

Re: SQL style guide

#118

Post by dghf »

carterehsmith wrote: post #114
dghf wrote: post #14

Can anyone explain what this means? > 1. The key should be unique to some degree. If it's talking about primary keys (which it seems to be), it should be unique, full stop. If it's not, where does uniqueness come in? Is it actually talking about indices, which, if I understand correctly, should have a wide spread of values (without any of them necessarily being unique) if they're to be useful?

It is just the terminology. Technically, a key is any column (or a set of columns) that you use to find records. It does not need to be unique. But it tends to be more useful if it is more selective, I guess that's why they say 'to some degree'. So now, if a key is also unique, it is called a 'candidate key'. In general, you can have more than one candidate key in a table. For example, a natural key (such as order#)…

> Technically, a key is any column (or a set of columns) that you use to find records.

Isn't that an index rather than a key?

I thought 'key' meant:

- a superkey (any set of columns the values of which must be unique in any given row; in a properly relational model, any relation will have at least one, the trivial superkey, the set of all attributes in the relation)

- a candidate key (a minimal superkey, i.e. one without redundant columns -- if you remove any column from the key, it will cease to be unique)

- a primary key (the candidate key considered to be and designated as the most important)

- a foreign key.

Re: SQL style guide

#119

Post by dghf »

Treffynnon wrote: post #94
dghf wrote: post #14

Can anyone explain what this means? > 1. The key should be unique to some degree. If it's talking about primary keys (which it seems to be), it should be unique, full stop. If it's not, where does uniqueness come in? Is it actually talking about indices, which, if I understand correctly, should have a wide spread of values (without any of them necessarily being unique) if they're to be useful?

Thinking multi-column indexes here.

Yes, but it's in the 'Constraints and keys' section. It even says 'Constraints, and their subset, keys'.

As far as I'm aware, an index is not a constraint, except for a unique index, which implies a unique constraint.

Having said that, I can't think of an explanation more likely than yours.

Re: SQL style guide

#120

Post by philliphaydon »

ryeguy wrote: post #89
dahdum wrote: post #16

Earlier quoted context omitted.

I disagree with this too, using table.id is easier to type and consistent when creating joins. It also encourages (forces) to reference the fields with tables prefix.

One advantage of using user_id on both the pk of users as well as the fk side is you can do: INNER JOIN comments USING (user_id) instead of: INNER JOIN comments ON comments.user_id = user.user_id

Which is fine if you are aware of the differences between the two. Since they act differently.
Post reply on HN