BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
A blog setting out an approach to coding in SQL to ensure consistently high standards across your organisation.
- Coding standards for writing SQL in Management Studio
- Coding conventions for selecting data in SQL (this blog)
- Coding conventions for programming in SQL
Posted by Andy Brown on 01 February 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Coding conventions for selecting data in SQL
Here is how to write a SELECT statement:
USE WorldEvents
GO
-- show events in date order
SELECT
-- use quotes for aliases containing spaces
e.EventName AS 'Name of event'
, e.EventDetails AS 'Event details'
, e.EventDate AS EventDate
FROM
tblEvent AS e
WHERE
-- show just events in country 5 (the USA)
e.CountryID = 5
ORDER BY
e.EventDate ASC
Read on for specific notes on comments, indentation, capitalisation, aliases, etc.
You should include commas before each column that you want to display, not after, although many Wise Owl blogs, exercises and manual chapters put the commas at the end of each line. Sorry!
Indentation
You should indent any code following any one of the main SQL keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY):
SELECT
-- what to select
FROM
-- table
WHERE
-- any criteria
GROUP BY
-- columns to group by
HAVING
-- aggregated data to filter by
ORDER BY
-- how to sort
Comments
You should comment frequently (a good guideline is that if you've written 3 lines of SQL without a comment, it's probably time to include one). Most comments should use -- as a prefix, but you can use comment blocks when you have a lot to say:
/*
If you have a procedure which needs a lot of explanation, it's
OK to encase the comments (indented, as here) like this.
*/
Comments should be indented to the same level as the line of code which immediately follows, and should always be preceded by a blank line:
ORDER BY
-- show in date order
e.EventDate ASC
Capitalisation
You should put keywords (these normally appear in blue) in upper case:
USE WorldEvents
GO
-- list events
SELECT
*
FROM
tblEvent AS e
Table Aliases
You should give all table names aliases, even for simple queries:
-- list events
SELECT
e.EventID
,e.EventName
FROM
tblEvent AS e
Use short alias names in lower case (in most cases the first letter of the main part of the table name will suffice, as for the above example).
Column Aliases
Column aliases are optional. Where you use them, you should encase them in quotation marks if the alias includes spaces, as below:
SELECT
-- use quotes for aliases containing spaces
e.EventName AS 'Name of event'
, e.EventDetails AS 'Event details'
, e.EventDate AS WhenHappened
FROM
tblEvent AS e
Joins
The syntax for inner joins is shown below:
SELECT
-- show columns from the 2 tables
c.CountryName
, e.EventName
FROM
tblCountry AS c
-- join countries to events
INNER JOIN tblEvent AS e
ON c.CountryID = e.CountryID
Notes:
- You should include both words INNER JOIN.
- The comment before the join is a good idea, but optional.
For outer joins, you should use left outer joins only, not right ones:
-- join countries to events
LEFT OUTER JOIN tblEvent AS e
ON c.CountryID = e.CountryID
The CASE Expression
You should indent the clauses of a CASE expression as below:
-- events by century
SELECT
e.EventName
-- show century that events occur in
, CASE
WHEN YEAR(e.EventDate) < 1900
THEN 'Pre 19th'
WHEN YEAR(e.EventDate) < 2000
THEN '20th'
ELSE '21st'
END AS Century
FROM
tblEvent AS e
It's also acceptable to put the WHEN and THEN on the same line:
-- show century events occur in
, CASE
WHEN YEAR(e.EventDate) < 1900 THEN 'Pre 19th'
WHEN YEAR(e.EventDate) < 2000 THEN '20th'
ELSE '21st'
END AS Century
You should always have at least one comment for a CASE expression, and always give it the resulting column an alias (the one above is called Century).
Dates
You can use either the FORMAT or the CONVERT function to present dates (although be aware that the FORMAT function can be slow):
-- events by century
SELECT
e.EventName
-- two ways to show dates
, FORMAT(e.EventDate,'dd/MM/yyyy') AS 'Event date 1'
, CONVERT(char(10),e.EventDate,103) AS 'Event date 2'
FROM
tblEvent AS e
WHERE
-- show events from 2010 onwards
e.EventDate > '2009-12-31'
As the above examples shows, use the ISO standard date format of YYYY-MM-DD to refer to any date (so 2009-12-31 represents 31st December 2009).
Combining criteria
Where you use and/or, put these at the beginning of the lines they apply to:
SELECT
e.EventName
, e.EventDetails
, e.EventDate
FROM
tblEvent AS e
WHERE
-- show events from 2000 on
YEAR(e.EventDate) >= 2000
-- name starts with s
AND e.EventName LIKE 's%'
If you need to combie criteria using parentheses, indent between them:
WHERE
-- show events from 2000 on
year(e.EventDate) >= 2000
-- name starts or ends with S
AND (
e.EventName LIKE 's%'
OR e.EventName LIKE '%s'
)
The next and final part of this blog looks at coding conventions for SQL programming, rather than just for selecting data.
- Coding standards for writing SQL in Management Studio
- Coding conventions for selecting data in SQL (this blog)
- Coding conventions for programming in SQL