syntax_sql_styleguide.sql

-- Bad: no capitalization
select
  products.id,
  case
    when products.sold_by = 0 then 'item'
    when products.sold_by = 1 then 'weight'
    when products.sold_by = 2 then 'volume'
  end as sold_by,
  product_categories.category_name
from products
left join product_categories
       on product_categories.product_id = products.id


-- Bad: indentation
SELECT
  products.id,
  CASE WHEN products.sold_by = 0 THEN 'item'
    WHEN products.sold_by = 1 THEN 'weight'
    WHEN products.sold_by = 2 THEN 'volume' END AS sold_by,
  product_categories.category_name
FROM products
LEFT JOIN product_categories
       ON product_categories.product_id = products.id

-- Bad: missing table references in some columns
SELECT
  products.id,
  CASE
    WHEN sold_by = 0 THEN 'item'
    WHEN sold_by = 1 THEN 'weight'
    WHEN sold_by = 2 THEN 'volume'
  END AS sold_by,
  category_name
FROM products
LEFT JOIN product_categories
ON product_categories.product_id = products.id

-- Bad: unecessarily used alias, bad column naming convention
SELECT
  p.id,
  CASE
    WHEN p.sold_by = 0 THEN 'item'
    WHEN p.sold_by = 1 THEN 'weight'
    WHEN p.sold_by = 2 THEN 'volume'
  END AS soldby,
  pc.category_name AS catn
FROM products p
LEFT JOIN product_categories pc
       ON pc.product_id = p.id

-- Good
SELECT
  products.id,
  CASE
    WHEN products.sold_by = 0 THEN 'item'
    WHEN products.sold_by = 1 THEN 'weight'
    WHEN products.sold_by = 2 THEN 'volume'
  END AS sold_by,
  product_categories.category_name
FROM products
LEFT JOIN product_categories
       ON product_categories.product_id = products.id