Пишите читаемый код

Применение SQL для решения реальных задач

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Пишите команды SQL в верхнем регистре

Исходный вариант

select 
  title as film_title, 
  description as film_description
from rental as r
join inventory as i
  on r.inventory_id = i.inventory_id
join film as f
  on i.film_id = f.film_id
where f.length > 90
  and f.replacement_cost > 20;

Улучшенный вариант

SELECT 
  title AS film_title, 
  description AS film_description
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length > 90
  AND f.replacement_cost > 20;

Используйте переносы строк и отступы

Исходный вариант

SELECT title, description
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f ON i.film_id = f.film_id
WHERE f.length > 90 AND f.replacement_cost > 20;

Улучшенный вариант

SELECT title, 
       description 
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length > 90
  AND f.replacement_cost > 20;

Используйте snake_case

Исходный вариант

SELECT 
  title expensivelongtitle, 
  description expensivelongdescription
FROM rental AS r
...

Улучшенный вариант

SELECT 
  title expensive_long_title, 
  description expensive_long_description
FROM rental AS r
...

Используйте IN вместо множества операторов OR

Исходный вариант

SELECT 
    address_id,  
    district 
FROM address
WHERE district = 'Texas'
  OR district = 'Bihar'
  OR district = 'Chiba'
  OR district = 'Chiayi'
  OR district = 'Gois';

Улучшенный вариант

SELECT 
    address_id,  
    district 
FROM address
WHERE district IN ('Texas', 'Bihar', 'Chiba', 
                   'Chiayi', 'Gois');

Используйте BETWEEN, когда это возможно

Исходный вариант

SELECT 
  title, 
  description
FROM film
WHERE replacement_cost > 15
  AND replacement_cost < 25;

Улучшенный вариант

SELECT 
  title, 
  description
FROM film
WHERE replacement_cost BETWEEN 15 AND 25;

Давайте потренируемся!

Применение SQL для решения реальных задач