SQL Select Convention

Question

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.

Answer 1

I prefer the first:

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

Answer 2

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

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 3

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.

Answer 4

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 5

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 6

We would actually do:

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

Answer 7

I use something similar to John Price's style:

with Foo as (
    select Id, Name
        from Table1
           join Table2 on 
               Table2.FK = Table1.PK and
               Table2.Foo = Table1.Bar
           join Table3 on 
               Table3.FK = Table1.PK and
               Table3.Foo = Table1.Bar
        where
            Table1.Foo > 1 and
            Table1.Bar < 100
),
Bar as (
    ...
)
select * from Bar;

Some comments:

proper indentation of logical blocks: the join goes inside the from, the from inside the select

on hangs at the end of the line for proper K&R style. same goes for the with parentheses.

and also

given code colouring, I like lower case keywords much better than UPPER CASE EVERYWHERE.

Upvotes: 2

Answer 8

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 9

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

I prefer this one. And a beer.

Answer 10

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

Answer 11

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 12

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 13

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