CSE 344: Style Guide

Keywords

Spell keywords in ALL CAPS

select * from foo where x = 17;

SELECT * FROM foo WHERE x = 17;

Line breaks

Place each "clause" of a query or statement on its own line

SELECT * FROM foo JOIN bar ON y = z WHERE x = 17 AND y = 'hello' GROUP BY z HAVING count(*) > 0 ORDER BY y LIMIT 10;

SELECT *
FROM foo
JOIN bar ON y = z
WHERE x = 17
AND y = 'hello'
GROUP BY z
HAVING count(*) > 0
ORDER BY y
LIMIT 10;

Spacing

Place spaces around =, ,, and other operators (e.g., <, >, <=, >=, !=) for better readability

SELECT a.title,a.year,a.producer
FROM albums AS a
WHERE a.title='Unorthodox Jukebox'
OR a.title='Doo-Wops & Hooligans';

SELECT a.title, a.year, a.producer
FROM albums AS a
WHERE a.title = 'Unorthodox Jukebox'
OR a.title = 'Doo-Wops & Hooligans';

Optional: Line up last character of all keywords at the beginning of the line.

SELECT a.title, a.year, a.producer
FROM albums AS a
WHERE a.title = 'Unorthodox Jukebox'
OR a.title = 'Doo-Wops & Hooligans';

SELECT a.title, a.year, a.producer
  FROM albums AS a
 WHERE a.title = 'Unorthodox Jukebox'
    OR a.title = 'Doo-Wops & Hooligans';

Naming

Use snake case for naming variables. Opt for concise and meaningful names, while avoiding reserved keywords.

CREATE TABLE Course (
  CourseID INT PRIMARY KEY,
  NumberOfStudentsEnrolledInThisClass INT,
  BuildingLocation TEXT,
  MaximumNumberOfStudentCapacity INT
);

CREATE TABLE Course (
  id INT PRIMARY KEY,
  num_students INT,
  building TEXT,
  capacity INT
);

Subqueries

Do not use subqueries in the FROM clause. Instead, use WITH. (Subqueries outside of the FROM clause are fine though.)

SELECT * 
FROM (
  SELECT foo, count(*) AS bar
  FROM baz
  GROUP BY foo
) ahh, quux
WHERE ahh.foo = quux.foo

WITH yay AS (
  SELECT foo, count(*) AS bar
  FROM baz
  GROUP BY foo
)
SELECT * 
FROM yay, quux
WHERE yay.foo = quux.foo

More style

A more detailed and opinionated style guide can be found at sqlstyle.guide.

Automation

VSCode extensions such as Prettier SQL or SQL Formatter act as great starting points. Similarly, there are websites such as Poor SQL and SQL format.