Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов.

Вначале было слово, а точнее запрос. Скажем

select name from user where id = 1

Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.

Так что же произошло? Проблемы начались, когда таблиц стало две:

select u.name from user u, rest r where u.id = 1 and u.id = r.user_id

Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще  id = user_id, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 - всё рассыпалось. Взглянув на запрос, я не мог сразу понять как соединены таблицы и не пропущена ли какая-то связка. И с этим все прекрасно жили, но я не мог. Однажды мне, молодому джуну, на глаза попался ANSI синтаксис.

select u.name from user u inner join rest r on u.id = r.user_id where u.id = 1

букв стало немного больше, но я намного лучше стал понимать, как связаны таблицы в моих SQL выражениях. Мир запросов расцвёл для меня новыми красками, и я больше не писал запросы как-то иначе. А ещё распространял эту весть среди других джунов. Это был мой первый шаг в эволюции SQL. Я вырвался от привычных шаблонов легаси кода и сделал что-то своё. Но была одна проблема. Когда используется скажем левостороннее соединение ANSI синтаксис заставляет переносить в связки, и все прочие ограничения для таблицы.

select u.name, r.resp_name 
from user u 
left join resp r on u.id = r.user_id  and r.end_date > sysdate 
where id = 1

Это меня жутко бесило, так как опять связи и параметры сливались в одну кучу.  Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым.  К тому времени я уже дорос до мидла и хотел рассказывать истории своими селектами.  И это подтолкнуло меня на второй шаг эволюции.  И имя ему with.

with resp_q as (
  select resp_name, userid  
  from resp where r.end_date > sysdate)
 ,main_q as (
   select u.name, r.respname
   from user u 
   left join resp_q r on u.id = r.userid
   where id = 1)
 select * from main_q

Кода стало опять больше, но запросы в with позволили мне разбить монолитный запрос и группировать разные кусочки запроса по “историям”, а потом сплетать их вместе. Я мог рассказать про свой запрос так: “Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки.”  И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом WET, т.к. одни и те же истории я мог использовать в разных частях своего запроса, не дублируя код.  Ко всему прочему, упростилась отладка. Знай в блок from подставляй разные именованные запросы и отлаживай их по отдельности.  А ещё, как выяснилось позже, с помощью with можно оптимизировать запросы, используя hint MATERIALIZE. Он материализует именованный подзапрос и данные при запросе из него берутся из темпового пространства. До этого я использовал обычные темповые таблицы. Это было более грубое решение, т.к. создавались лишние объекты БД + надо было помнить про очистку. Как итог, теперь, если запрос сложнее 10 строк, я почти всегда использую with.

Но чего-то не хватало. По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает. Как итог, я часто отдавал не до конца протестированный код. Мне регулярно приходилось слышать про unit тесты, автотесты и прочее. Но сложно было это применить к БД. Сегодня сумма за период равна 100р, а завтра 120р. И как ты тут напишешь тест? Так и жил… Но, уже став тимлидом, мне попалась задача, в которой надо было найти отмененные документы. Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию).

select * from document where xxstorno(id) = 'Y'

У меня было порядка 10 примеров документов. И завершая условие для одного документа, что-то ломалось в другом. А так как тестировал руками и глазами, времени уходило просто море.  Я уже думал этому не будет конца. Пока не понял, что вокруг моего запроса можно написать обертку, которая будет за меня проверять все мои кейсы и говорить какие документы прошли проверку, а какие нет. Потратив на обертку несколько минут, я сократил время тестирования с 5-7 минут, до нескольких секунд.

with test_case as (
  select 10 id, 'Y' storno from dual 
  union all 
  select 5 id, 'N' storno from dual)
  , run_test as (
    select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result
    from test_case  tc
    left join document d on d.id = tc.id)
 select * from run_test

После правки функции, я просто запускал тест-запрос и смотрел сколько документов прошло тестирование, а сколько нет. В процессе тестирования я накидывал туда ещё кейсов, при том что про старые тесты тоже не забывались. И тогда я понял, как же это здорово! Как можно легко тестировать свой запрос, повышать надёжность и при этом не нужно ничего делать руками. Это может показаться элементарным, но до этого мне не встречались подобные конструкции. Обычно я видел конструкции типа and id = 5--6 7 10 135 1345  в которой просто перебором подставлялись разные значения и руками смотрелось что и как оно должно возвращать. С того дня я написал несколько разработок, и к каждой из них я уже готовил свой тестовый скрипт.  Данный стиль мне очень понравился и теперь я пытаюсь привить его и своим разработчикам.   Чтобы им не пришлось проделать путь в 12 лет, чтобы писать красивые SQL запросы.

По итогу в мире SQL не происходит почти ничего нового уже много лет, тем не менее всегда приятно найти возможность улучшить свои запросы.