SQL Select Convention

Question

Hi,

We've encountered the following "issue". I like to use the following writing:

SELECT  Id, Name
FROM    Table1
JOIN    Table2 ON Table1.FK1 = Table2.FK1

but a colleague of mine uses:

SELECT  Id, Name
FROM    Table1 JOIN Table2 
ON   Table1.FK1 = Table2.FK1

Is there a best practice for this? I think it's more convenient if all used tables are alligned.

Kind regards, Sem

Answer 1

I prefer the first

Answer 2

We would actually do:

SELECT Id, Name
    FROM Table1
    JOIN Table2 
        ON Table2.FK1 = Table2.FK1

Answer 3

I like the first out of those two. Our format, however, has more whitespace and we make sure we use brackets.

SELECT
  x.Id
, MAX(y.Sales)
FROM
  [dbo].[Table1] x
INNER JOIN
  [dbo].[Table2] y ON Table1.Id = Table2.Id
WHERE
  x.Id = 100
AND
  x.Name = 'Foo'
GROUP BY
  x.Id
HAVING
  COUNT(*) > 1

Developers usually have a hard time with it at first (please don't down-vote! :) ), but the formatting tends to grow on most people and the visibility helps a lot. Each important clause is clearly visible.

EDITED: To alias tables, as per Justice's answer. I do that to, just forgot in this answer. As for all caps, it's a habit from my days of not having great SQL highlighting; it's optional.

Answer 4

I prefer the first, but I've found that a lot of code formatters automatically format to the second.

Answer 5

I prefer the first version but with some indentation on the JOIN clause to show that it's part of the FROM clause.

I found this article that gives some pretty good guidelines.

Answer 6

The company I work for has an app that uses an object model to generate sql. It generates it with the second syntax most of the time. So lots of joins and then the on conditions. It is extremely frustrating to try and decipher which on condition applies to which table when you have lots of tables.

So I prefer to put the ON clause with the table I'm joining. It makes it much easier to tell what join clauses you've used for a table and what conditional clauses that are a part of your join statement. And getting your joins correct is half the battle. I also prefer the parenthesis.

SELECT primarytable.whatever
FROM primarytable
     INNER JOIN secondarytable ON (primarytable.primarykey = secondarytable.foreignkey)
     INNER JOIN othertable ON (primarytable.foreignkey = othertable.primarykey AND othertable.somefield = 1)
     LEFT OUTER JOIN outertable ON (secondarytable.foreignkey = outertable.primarykey)
WHERE primarytable.somefield IS NOT NULL

Answer 7

I prefer the first - it lets me easily comment out joined tables when necessary.

Answer 8

I prefer the first form.

Answer 9

SELECT  Id, Name
FROM Table1
    JOIN Table2 ON Table2.FK1 = Table2.FK1

I prefer this one. And a beer.

Answer 10

My style:

select c.Id, c.Name
from Parent p
join Child c on c.ParentID = p.Id
where p.Id = 123

• If there were a long select-list, I would tend to write each column selected on its own line, or I would write all the columns from one table on one line and all the columns from another table on another line.

• If there were a long list of conditions in on or where, I would tend to write each condition on its own line.

Real WTFs:

• You are not aliasing your tables.
• You are writing SQL in all caps. I can't read it.
• You are not using an editor with rudimentary syntax highlighting, thus forcing you to rely on alignment to tell which words mean what.

Answer 11

I always do this:

     SELECT t1.ID, t1.Name
       FROM Table1 t1
 INNER JOIN Table1 t2 ON t1.FK1 = t2.FK1
      WHERE t1.Name = 'Bob'

I find the right-justification of the SQL commands makes it easier to read. Of course if you don't use a monospaced font in your code editor, then this won't work well at all.

I can't say I know of any "best practice" for this, though.

Answer 12

select
  Dept.COL1 as "Deptment Name"
, Emp.COL1 as "Employee Name"

from
Table1 Dept
inner join Table2 Emp on Emp.FK1 = Dept.Id

where
    Dept.COL2 = "something"
and Emp.COL2 = "another"

This is the style I use when developing query to extract data. You can remove any one of the items in the select and where clause by just commenting them out (with the exception of the first one). It is quite handy for debugging.

If you want them to look pretty and easy to recognize, then line up the "as" in select and "=" in the where clause will definitely help.

Answer 13

select
    a.Id,
    b.field2,
    b.field3
from
    table1 a
join
    table2 b
        on a.Id = b.a_id
where
    a.field1 = 'hello'
and b.field2 = 'there'

Using four spaces as a tab, allows "and " to line up in the where clause.