Комментарии 166
Статья для новичков, наверное, не плохая. Однако, большим опытом здесь не пахнет. Сразу в глаза бросается использование
members.firstname, members.lastname
Я сам так пишу, если нет необходимости в алиасе.
Необходимость в алиасе есть практически всегда: не сразу так потом понадобится. Опять же алиас упрощает использование код-ассиста.
Зачем нужны алиасы? В смысле, в чём именно необходимость?
Необходимость возникает в тот момент, когда запрос надо доработать:
Необходимость возникает в тот момент, когда запрос надо доработать.
Варианты:
1) надо добавить еще одно поле и я не помню, как оно точно называется. Набираю короткий алиас, и список полей таблицы у меня перед глазами. Нет алиаса — я вынужден идти смотреть описание таблицы.
2) надо приджойнить таблицу в которой есть совпадающее поле — тут же становится необходимо завести алиас
3) надо просто понять, что делает запрос, а в нем идет соединение таблиц без алиасов и не ясно, какое поле к какой таблице относится.
В общем я считаю, что обязательное использование алиасов — хороший стиль.
Варианты:
1) надо добавить еще одно поле и я не помню, как оно точно называется. Набираю короткий алиас, и список полей таблицы у меня перед глазами. Нет алиаса — я вынужден идти смотреть описание таблицы.
2) надо приджойнить таблицу в которой есть совпадающее поле — тут же становится необходимо завести алиас
3) надо просто понять, что делает запрос, а в нем идет соединение таблиц без алиасов и не ясно, какое поле к какой таблице относится.
В общем я считаю, что обязательное использование алиасов — хороший стиль.
Я практически всегда юзаю алиасы по двум причинам:
А так, да, если таблица не джоинится сама с собой (и в запросе к таблице нету подзапросов к той же таблице), то критической необходимости использования алиасов, вроде бы, нет.
P.S.: К этому следует добавить, что я всегда префиксирую название поля названием или алиасом таблицы. Потому что если сейчас название поля уникальное, то не факт, что оно таким останется. Ну и плюс сразу ясно откуда поле.
- Таблицы, к сожалению, принято называть во множественном числе. Но
students.surnameиbooks.title— как-то не комильфо. Т.е. названию таблицы во множественном делаю алиас в единственном числе (или вообще аббревиатурой), чтобы множественное число не задевало моё чувство прекрасного. - На практике названия таблиц часто длинные.
А так, да, если таблица не джоинится сама с собой (и в запросе к таблице нету подзапросов к той же таблице), то критической необходимости использования алиасов, вроде бы, нет.
P.S.: К этому следует добавить, что я всегда префиксирую название поля названием или алиасом таблицы. Потому что если сейчас название поля уникальное, то не факт, что оно таким останется. Ну и плюс сразу ясно откуда поле.
Например, обращаться к той же самой таблице в подзапросе. Простейший пример, у нас есть таблица-журнал операций:
create table entity(
entity_id number(10, 0) not null
, unit_id number(10, 0) not null
, some_state varchar2(100)
-- ...прочие полезные поля
, constraint primary key(op_id)
);
comment on table entity is 'Журнал состояний некоторой сущности';
comment on column entity.entity_id is 'Уникальный идентификатор состояния сущности';
comment on column entity.unit_id is 'Уникальный идентификатор самой сущности';
comment on column entity.some_state is 'Некоторое значимое состояние';и мы хотим выбрать самую последнюю запись по истории:
select *
from entity e
where not exists(
select 1
from entity
where unit_id = e.unit_id
-- Если же мы хотим выбрать не просто последнюю запись в истории,
-- а последнюю запись с определённым состоянием сущности,
-- добавляем подобное условие
--and some_state = e.some_state
and entity_id > e.entity_id
)
order by entity_id
;Без псевдонима это было бы сделать невозможно, поскольку таблица.и в основном запросе, и в подзапросе одна и та же.
Но вообще, лично я для себя решил, что там, где псевдонимы для однозначного и чёткого определения принадлежности колонок не требуются, их не указывать. В примере выше такими местами являются:
- выражение
order by entity_id— поскольку в выборке участвует всего одна таблица, псевдонимы при перечислении колонок указывать нет необходимости. Если бы в запросе участвовало бы две таблицы (соединённые черезjoin, к примеру), то имя каждой колонки в обязательном порядке указывалось бы вместе с псевдонимом таблицы; - поля из таблицы подзапроса —
and entity_id > e.entity_id— аналогично, в подзапросе таблица всего одна, нет необходимости указывать псевдоним, движок СУБД сам определит, к какой таблице относятся поля — к самой ближайшей.
Так запросы становится значительно легче воспринимать.
НЛО прилетело и опубликовало эту надпись здесь
с SQL надо аккуратнее, а то потом подсаживаешься и начинается что то подобное:
INSERT INTO stats_search_engine_hot
SELECT
CURRENT_DATE as date,
visitors.user_id,
visitors.host_id,
visitors.search_engine_id,
visitors.visitorquant,
IFNULL(dialogs.dialogquant, 0) as dialogquant
FROM (
SELECT
sess.user_id,
sess.host_id,
sess.search_engine_id,
count(sess.id) as visitorquant
FROM (
SELECT s.id FROM session as s WHERE s.date = current_date
UNION
SELECT sh.id FROM session_hot as sh
) as subquery
INNER JOIN session as sess ON sess.id = subquery.id
WHERE sess.search_engine_id > 0
GROUP BY
sess.user_id,
sess.host_id,
sess.search_engine_id
) as visitors
LEFT JOIN (
SELECT
s.user_id,
s.host_id,
s.search_engine_id,
count(s.id) as dialogquant
FROM(
SELECT
m.session_id,
min(m.id) as mid
FROM message as m
WHERE m.date >= DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00")
GROUP BY
DATE(m.date),
m.session_id
) as a
LEFT JOIN message as b ON b.id = a.mid
LEFT JOIN session as s ON s.id = a.session_id
WHERE b.direct = 0
AND s.search_engine_id > 0
GROUP BY
s.user_id,
s.host_id,
s.search_engine_id
) as dialogs
ON visitors.user_id = dialogs.user_id
AND visitors.host_id = dialogs.host_id
AND visitors.search_engine_id = dialogs.search_engine_id
Это еще нормально выглядит. Хотя бы не в одну строчку, как тот запрос, который я отлаживал на прошлой неделе...
По-моему тот кто написал это не понял структуры таблиц и условий:
visitors легко упростить до
SELECT
sess.user_id,
sess.host_id,
sess.search_engine_id,
count(sess.id) as visitorquant
FROM session as sess
WHERE sess.search_engine_id > 0
AND (s.date = current_date OR sess.id IN (SELECT sh.id FROM session_hot as sh))
GROUP BY
sess.user_id,
sess.host_id,
sess.search_engine_id
dialogs можно заинлайнить тогда там уйдет куча группировок, а min(m.id) и последующий джойн заменить на TOP 1.
Так же бросается в глаза DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00") который вероятнее всего можно заменить на уже имеющийся CURRENT_DATE
visitors легко упростить до
SELECT
sess.user_id,
sess.host_id,
sess.search_engine_id,
count(sess.id) as visitorquant
FROM session as sess
WHERE sess.search_engine_id > 0
AND (s.date = current_date OR sess.id IN (SELECT sh.id FROM session_hot as sh))
GROUP BY
sess.user_id,
sess.host_id,
sess.search_engine_id
dialogs можно заинлайнить тогда там уйдет куча группировок, а min(m.id) и последующий джойн заменить на TOP 1.
Так же бросается в глаза DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00") который вероятнее всего можно заменить на уже имеющийся CURRENT_DATE
Это вы еще запросы на 1С в конфигурации «Зарплата и управление персоналом».
Есть запросы на тысячу с лишним строк!!!
Есть запросы на тысячу с лишним строк!!!
SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
GROUP BY members.firstname, members.lastname;SELECT
members.firstname || ' ' || members.lastname AS "Full Name"
FROM
borrowings, members, books
WHERE
members.memberid=borrowings.memberid AND
books.bookid=borrowings.bookid AND
books.stock > (SELECT avg(stock) FROM books)
GROUP BY
members.firstname,
members.lastname;
Нужно осознать, что SQL — это язык описания запроса, а не язык «почти естественного» общения человека с компьютером.
Все те же претензии «на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить» — справедливы для того же Си или любого другого языка. Английские термины в качестве ключевых слов — облегчают их мнемоническое запоминание, но не более того. Они не являются эквивалентом конструкций естественного языка.
Все те же претензии «на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить» — справедливы для того же Си или любого другого языка. Английские термины в качестве ключевых слов — облегчают их мнемоническое запоминание, но не более того. Они не являются эквивалентом конструкций естественного языка.
FROM borrowings, members, booksЭто ужасно. Настолько же ужасно, как
FROM borrowings
INNER JOIN members
INNER JOIN books
ON members.memberid=borrowings.memberid
ON books.bookid=borrowings.bookidВ чём суть «ужасности»? Список источников в своей секции, список условий — в своей.
Так же как по секциям расположены описание полей на выходе, сортировка, группировка.
Так же как по секциям расположены описание полей на выходе, сортировка, группировка.
Непонятно как об'единены эти три таблицы меж собой — заставляет держать в уме разрозненные таблицы и потом дорисовывать меж ними связи (хорошо что запрос короткий и всё видно). Ну и такая форма подразумевает что там скорее всего inner join и тем печальнее вдруг обнаружить что там left или right join.
Удивительно что мой пример у вас вызвал отторжение, хотя по структуре он точно такой же, как и первоначальный.
Удивительно что мой пример у вас вызвал отторжение, хотя по структуре он точно такой же, как и первоначальный.
Так все три одинаковы. Разная сложность чтения. Все три источника в любом случае нужно держать в уме, а визуально искать легче, когда искать нужно в одном месте, а не разбросанные по запросу. Перечисленные через запятую они «собираются» — «одним взглядом», растянутые на несколько строчек — пока найдёшь одно, можешь уже забыть другое (особенно в сложном запросе).
В монстро-запросе будет другая ситуация. Если планировщик базы хорошо оптимизирует подобное — возможно имеет смысл разбивать сложный запрос на более простые и оформлять их в виде функций или WITH выражений, хотя это не всегда полезно.
Я не претендую на истину в последней инстанции и всегда можно найти обратный пример. Просто привёл пример для сравнения, как можно упростить простой запрос и сделать его легко читаемым для человека. Фанатизм же до добра не доведёт.
В монстро-запросе будет другая ситуация. Если планировщик базы хорошо оптимизирует подобное — возможно имеет смысл разбивать сложный запрос на более простые и оформлять их в виде функций или WITH выражений, хотя это не всегда полезно.
Я не претендую на истину в последней инстанции и всегда можно найти обратный пример. Просто привёл пример для сравнения, как можно упростить простой запрос и сделать его легко читаемым для человека. Фанатизм же до добра не доведёт.
Вот именно что сложность разная. У меня слишком маленький об'ём ОЗУ для сложных запросов. В вашем случае я вынужден целиком (целиком — это имя + об'ём) держать в уме ворох таблиц и я даже не догадываюсь что с ними собираются делать и каков масштаб беды (единственное в чём уверен — он должен уменьшиться), пока не найду where. Да, я видел список результирующих колонок, но я пока даже близко не предполагаю откуда они и какую смысловую нагрузку несут. Найдя where, я вынужден сортировать куски условий (а они ещё будут делиться на условия соединения и фильтрации) меж этими таблицами попарно и выстраивать связи между ними (хорошо если разработчик эти куски написал в том же порядке, что и таблицы в from, тогда я просто буду по одной таблице к общей куче пристыковывать, а не держать в уме кучу пар, которые потом будут об'единяться в бОльшие группы) попутно фильтруя весь об'ём соединения.
В моём же случае я сразу вижу как две таблицы об'единяются (from и первый join), осознаю их смысловую нагрузку, лишнее я уже отбросил и предполагаю об'ём результата. Если в join'ах ещё есть таблицы, то к результату предыдущего шага добавляю ещё одну таблицу и проделываю то же самое дальше.
В моём же случае я сразу вижу как две таблицы об'единяются (from и первый join), осознаю их смысловую нагрузку, лишнее я уже отбросил и предполагаю об'ём результата. Если в join'ах ещё есть таблицы, то к результату предыдущего шага добавляю ещё одну таблицу и проделываю то же самое дальше.
То, что ужасно
не могу не согласиться — здесь просто месиво операторов вместо лаконичного и визуально структурированного перечисления.
FROM borrowings
INNER JOIN members
INNER JOIN books
ON members.memberid=borrowings.memberid
ON books.bookid=borrowings.bookid
не могу не согласиться — здесь просто месиво операторов вместо лаконичного и визуально структурированного перечисления.
В принципе, учитывая структуру наименований, можно также вспомнить и такую форму JOIN'ов:
Но, имхо, это тоже сложнее читается, чем лаконичная секция FROM и подробная WHERE.
FROM borrowings
INNER JOIN members USING (memberid)
INNER JOIN books USING (bookid)
Но, имхо, это тоже сложнее читается, чем лаконичная секция FROM и подробная WHERE.
Вы уверены? Oracle такого не позволяет. Но позволяет.
И хотя на выходе получается тоже самое, что и в исходном запросе, логика у них разная, это не просто стиль оформления.
SELECT *
FROM books
INNER JOIN members
INNER JOIN borrowings
ON members.memberid=borrowings.memberid
ON books.bookid=borrowings.bookid;
И хотя на выходе получается тоже самое, что и в исходном запросе, логика у них разная, это не просто стиль оформления.
Ну я не проверял конкретно этот запрос, конечно, но в том, что все ON можно в конце JOIN'ов писать — уверен.
Хотя не, не уверен. В одном из открытых запросов передвинул ON в конец к другому и там сработало, а сейчас попытался написать похожий на пример запрос и оно не заработало, пришлось колдовать. Планы у запросов одинаковые, так что логику я пока не до конца познал и где такое применять я тоже не знаю.
Логика тут простая:
SELECT *
FROM books INNER JOIN (
members INNER JOIN borrowings ON members.memberid=borrowings.memberid
) ON books.bookid=borrowings.bookid;Все станет совсем понятно если в мой запрос добавить скобочки.
И это будет тоже самое, что выше. А если чуть переписать запрос и добавить, столь не любимое вами внешнее соединение, то разница станет наглядной.
В первом случае это список всех взятых книг, второе внутренне соединение убирает не взятые книги.
Во втором это список всех книг без исключения и информация о их взятии исключая те взятия у которых не определены читатели.
SELECT *
FROM books
INNER JOIN (members
INNER JOIN borrowings
ON members.memberid=borrowings.memberid)
ON books.bookid=borrowings.bookid;
И это будет тоже самое, что выше. А если чуть переписать запрос и добавить, столь не любимое вами внешнее соединение, то разница станет наглядной.
SQL> CREATE TABLE borrowings
2 (
3 memberid INTEGER,
4 bookid INTEGER
5 );
Table created.
SQL>
SQL> CREATE TABLE books (bookid INTEGER);
Table created.
SQL>
SQL> CREATE TABLE members (memberid INTEGER);
Table created.
SQL>
SQL> INSERT INTO borrowings
2 SELECT LEVEL, LEVEL + 1
3 FROM DUAL
4 CONNECT BY LEVEL < 10;
9 rows created.
SQL>
SQL> INSERT INTO books
2 SELECT LEVEL + 2
3 FROM DUAL
4 CONNECT BY LEVEL < 10;
9 rows created.
SQL>
SQL> INSERT INTO members
2 SELECT LEVEL + 3
3 FROM DUAL
4 CONNECT BY LEVEL < 10;
9 rows created.
SQL>
SQL> SELECT members.memberid,
2 borrowings.memberid,
3 books.bookid,
4 borrowings.bookid
5 FROM books
6 LEFT JOIN borrowings ON books.bookid = borrowings.bookid
7 INNER JOIN members ON members.memberid = borrowings.memberid
8 ORDER BY members.memberid,
9 borrowings.memberid,
10 books.bookid,
11 borrowings.bookid;
MEMBERID MEMBERID BOOKID BOOKID
---------- ---------- ---------- ----------
4 4 5 5
5 5 6 6
6 6 7 7
7 7 8 8
8 8 9 9
9 9 10 10
6 rows selected.
SQL>
SQL> SELECT members.memberid,
2 borrowings.memberid,
3 books.bookid,
4 borrowings.bookid
5 FROM books
6 LEFT JOIN borrowings
7 INNER JOIN members
8 ON members.memberid = borrowings.memberid
9 ON books.bookid = borrowings.bookid
10 ORDER BY members.memberid,
11 borrowings.memberid,
12 books.bookid,
13 borrowings.bookid;
MEMBERID MEMBERID BOOKID BOOKID
---------- ---------- ---------- ----------
4 4 5 5
5 5 6 6
6 6 7 7
7 7 8 8
8 8 9 9
9 9 10 10
3
4
11
9 rows selected.
В первом случае это список всех взятых книг, второе внутренне соединение убирает не взятые книги.
Во втором это список всех книг без исключения и информация о их взятии исключая те взятия у которых не определены читатели.
Запятая в перечне WHERE — это всего лишь alias к CROSS JOIN.
Т.е. в Вашем варианте Вы сначала CROSS JOIN'ите все 3 таблицы, а потом фильтруете получившееся месиво с помощью WHERE.
(Да, DBMS оптимизируют CROSS JOIN + WHERE до того же, что получается в результате INNER JOIN… ON. Только вот мои мозги не оптимизируют. В смысле, что человеку понимать INNER JOIN… ON гораздо проще.)
Т.е. в Вашем варианте Вы сначала CROSS JOIN'ите все 3 таблицы, а потом фильтруете получившееся месиво с помощью WHERE.
(Да, DBMS оптимизируют CROSS JOIN + WHERE до того же, что получается в результате INNER JOIN… ON. Только вот мои мозги не оптимизируют. В смысле, что человеку понимать INNER JOIN… ON гораздо проще.)
Так и есть — это полное пересечение и современые DBMS его хорошо «понимают».
Я не знаю, возможно это отпечаток какой-то привычки, но не могу подтвердить описанного эффекта — моё сознание просто воспринимает «берём то, что нам нужно». Я в реальной жизни не «джойню» в уме сущности, чтобы осознать такие вещи как «все читатели, которые держат хоть одну книгу, которой в наличии больше среднего». Само описание на русском языке в данном случае строится через взятие полного множества всех читателей и последовательного исключения из него через перечисление правил — это, прямо скажем, легко и естественно. И это хорошо ложится на структуру FROM [источники данных] WHERE с перечислением вышеуказанных ограничений. По другому мне наверно нужно было бы думать «возьмём все комбинации книг, читателей и записей в формулярах, теперь начнём проверять, что id читателя указан в одной из записей о чтении и id книги указан в этой же записи, при этом в наличии этой книги больше среднего». Это слишком непривычно, чтобы читать подобным образом, хотя технически именно это и написано.
Секция FROM — техническая, в ней просто перечислены сущности, секция WHERE логическая — в ней перечислены правила, идущие из естественного языка и при их естественном чтении легко искать ошибки.
Вот так это выглядит для меня:
Подобная логическая интерпретация сразу позволяет обратить внимание на «странности», которые были бы неочевидны в разбросанных JOIN… ON — сравните исходный запрос и реорганизованный и обратите внимание на тезис, с которого начиналась статья — «SQL очень далёк от естественного языка». Получается, что он на самом деле не так уж и далёк, хотя тезис справедлив, поскольку речь шла немного о другом. Можно с помощью ORM нагенерировать ужас, понятный только машине и SQL это с радостью позволит. Но можно адаптировать для человека — у нас есть для этого достаточно свободы.
Одна проблема: человеки сразу займутся сравнением вкуса фломастеров и этого у нас не отнять :-)
Я не знаю, возможно это отпечаток какой-то привычки, но не могу подтвердить описанного эффекта — моё сознание просто воспринимает «берём то, что нам нужно». Я в реальной жизни не «джойню» в уме сущности, чтобы осознать такие вещи как «все читатели, которые держат хоть одну книгу, которой в наличии больше среднего». Само описание на русском языке в данном случае строится через взятие полного множества всех читателей и последовательного исключения из него через перечисление правил — это, прямо скажем, легко и естественно. И это хорошо ложится на структуру FROM [источники данных] WHERE с перечислением вышеуказанных ограничений. По другому мне наверно нужно было бы думать «возьмём все комбинации книг, читателей и записей в формулярах, теперь начнём проверять, что id читателя указан в одной из записей о чтении и id книги указан в этой же записи, при этом в наличии этой книги больше среднего». Это слишком непривычно, чтобы читать подобным образом, хотя технически именно это и написано.
Секция FROM — техническая, в ней просто перечислены сущности, секция WHERE логическая — в ней перечислены правила, идущие из естественного языка и при их естественном чтении легко искать ошибки.
Вот так это выглядит для меня:
SELECT -- возьмём:
member.first_name || ' ' || member.last_name AS "full_name" -- имя и фамилию читателя, объединив их в одну строку
FROM -- будем смотреть источники:
member, book, borrowing -- «читатель», «книга», «задолженность»
WHERE -- ожидая получить:
member.id = borrowing.member_id AND -- «все читатели, которые держат ...»
book.id = borrowing.book_id AND -- «... хоть одну книгу, ...»
book.stock > (SELECT avg(stock) FROM book) -- «... которой в запасе больше среднего»
Подобная логическая интерпретация сразу позволяет обратить внимание на «странности», которые были бы неочевидны в разбросанных JOIN… ON — сравните исходный запрос и реорганизованный и обратите внимание на тезис, с которого начиналась статья — «SQL очень далёк от естественного языка». Получается, что он на самом деле не так уж и далёк, хотя тезис справедлив, поскольку речь шла немного о другом. Можно с помощью ORM нагенерировать ужас, понятный только машине и SQL это с радостью позволит. Но можно адаптировать для человека — у нас есть для этого достаточно свободы.
Одна проблема: человеки сразу займутся сравнением вкуса фломастеров и этого у нас не отнять :-)
Для меня FROM — не техническая секция. Для меня FROM — основная секция.
Выбираю я всегда из одной (основной) таблицы:
Хотя к ней могут быть прицеплены цепочки других:
Также таблица, из которой идёт запрос, может быть динамически сконструированной — подзапросом. Правда, join — это тоже де-юре динамически сконструированная таблица.
Join де-юре всегда left (выше не указывал для простоты). Я надеюсь, СУБД автоматически оптимизирует left join при foreign key'е с not null constraint'ом (а также при foreign key'е без not null constraint'а, но с WHERE t.key_field is not null или WHERE t.* is not null) до inner join'а. Хотя де-факто я часто пишу inner join и вручную (если точно уверен, что здесь not null и оно не поменяется).
Как по мне, технической является скорее секция с набором выражений (до FROM). Логичнее было бы, если бы выражения в запросе не указывались и СУБД всегда возвращала набор handle'ов строк. Хотя, может быть, это действительно фломастеры.
Выбираю я всегда из одной (основной) таблицы:
SELECT s.* FROM students s;
Хотя к ней могут быть прицеплены цепочки других:
- по связям многие-к-одному (или один-к-одному):
SELECT s.*, f.* FROM students s join faculties f on s.faculty_id=f.id; -- цепочка длиной 1 SELECT s.*, f.*, u.* FROM students s join faculties f on s.faculty_id=f.id join universities u on f.university_id=u.id; -- цепочка длиной 2 SELECT s.*, f.*, t.* FROM students s join faculties f on s.faculty_id=f.id join towns t on s.home_town_id=t.id; -- две цепочки длиной 1
- реже, по связям один-ко-многим (или многие-ко-многим) для агрегирования:
SELECT f.*, some_aggs(s.*) FROM faculties f join students s on f.id=s.faculty_id GROUP BY f.*; SELECT u.*, some_aggs(s.*) FROM universities u join faculties f on u.id=f.university_id join students s on f.id=s.faculty_id GROUP BY u.*; -- цепочка длиной 2
А реже, потому что в таком случае гораздо изящнее смотрятся подзапросы (в смысле, указывать в FROM только саму основную таблицу, а статистику по таблицам, которые прицеплены связями -ко-многим, выбирать подзапросами).
К сожалению, и со способом «указать -ко-многим в join'е и group by», и со способом «использовать подзапрос для -ко-многим» есть проблемы (в первом случае мы не можем получить статистику по нескольким цепочкам, а во втором случае нам неудобно (приходится через ROW()) получать несколько статистик из одной цепочки).
Также таблица, из которой идёт запрос, может быть динамически сконструированной — подзапросом. Правда, join — это тоже де-юре динамически сконструированная таблица.
Join де-юре всегда left (выше не указывал для простоты). Я надеюсь, СУБД автоматически оптимизирует left join при foreign key'е с not null constraint'ом (а также при foreign key'е без not null constraint'а, но с WHERE t.key_field is not null или WHERE t.* is not null) до inner join'а. Хотя де-факто я часто пишу inner join и вручную (если точно уверен, что здесь not null и оно не поменяется).
Как по мне, технической является скорее секция с набором выражений (до FROM). Логичнее было бы, если бы выражения в запросе не указывались и СУБД всегда возвращала набор handle'ов строк. Хотя, может быть, это действительно фломастеры.
То что вы сделали в запросе называется old-style-join и является устаревшим со времен ввода стандарта SQL-92. Более 20 лет прошло может быть пора уже перейти на «новый» стандарт?
Есть несколько причин использовать «новый» стиль (если стандарта по вашему мнению не достаточно) могу привести еще несколько аргументов в пользу нового стиля. Во-первых разделения объединения и фильтраци в разные блоки. Во-вторых в случае если вы забыли указать условие объединения старый стиль приведет к расчету огроного объема данных и неверного результата (который может быть не так легко обнаружить), в то время как новый выдаст синтаксическую ошибку (http://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/). Можно продолжить гуглить и находить «за» и «против», а можно просто перейти на использование стандартов и выбросить дурное из головы. Ваш код могут читать люди которые родились после введения стандарта SQL-92 и ваш код будет выглядеть для них так, как будто его писали мамонты.
Есть несколько причин использовать «новый» стиль (если стандарта по вашему мнению не достаточно) могу привести еще несколько аргументов в пользу нового стиля. Во-первых разделения объединения и фильтраци в разные блоки. Во-вторых в случае если вы забыли указать условие объединения старый стиль приведет к расчету огроного объема данных и неверного результата (который может быть не так легко обнаружить), в то время как новый выдаст синтаксическую ошибку (http://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/). Можно продолжить гуглить и находить «за» и «против», а можно просто перейти на использование стандартов и выбросить дурное из головы. Ваш код могут читать люди которые родились после введения стандарта SQL-92 и ваш код будет выглядеть для них так, как будто его писали мамонты.
Как раз хотел что-то в таком духе написать. Когда я вижу 10 INNER JOIN'ов, я всегда знаю, какое количество записей будет: в реальной жизни получается такое же, как в «главной» табличке либо чуть меньше в случае NULL-ов. В случае вот таких перемножений множества нить сразу теряется, непонятно, какой порядок записей вернет запрос — N? N*M? N*M*K? Нужно разбираться с условиями, смотреть что там прописанно. Видешь LEFT JOIN'ы — гарантированно знаешь, сколько записей. видешь INNER JOIN — понимаешь порядок. Видишь CROSS JOIN — предполагаешь M*N записей. А когда оказывается, что это нифига не CROSS, а самый что ни на есть INNER, чувствуешь себя обманутым — пообещали одно, а на деле совсем другое.
freetonik можно ли указать ссылку на исходную учебную таблицу? Чтобы новички могли скопировать её и потренироваться с запросами локально.
Сделал свою копию, на случай если авторский файл удалят или изменят. Вот → https://docs.google.com/spreadsheets/d/1ELGKU-pjgyMlCib1O3Tn6H27VmJkxhUmCJtQ9-IpJFo/edit?usp=sharing
А как же INSERT c использованием SELECT вместо VALUES?
Главное чтобы потом тому кто это всё прочитал рассказали про HAVING до того как он начнёт лепить вложенные запросы везде где это нужно и не нужно.
ЕМНИП, HAVING не дает возможности использовать назначенное имя для колонки, а потому вложенные запросы могут быть предпочтительнее даже когда достаточно HAVING:
SELECT foo, длинное-длинное-выражение as bar
GROUP BY foo
HAVING длинное-длинное-выражение > 42
SELECT *
FROM (
SELECT foo, длинное-длинное-выражение as bar
GROUP BY foo
) AS temp
WHERE bar > 42Начиная с некоторой длины выражения, второй вариант смотрится лучше. И редактировать его проще.
А также, если в качестве длинного-длинного-выражения используется вызов функции, которое во фразу group by не засунешь.
За все СУБД не скажу, но в MySQL можно использовать алиасы из SELECT в HAVING.
SELECT foo, длинное-длинное-выражение as bar
GROUP BY foo
HAVING bar > 42
Начиная с некоторой длины стоит вместо подзапросов использовать CTE, который суть те же подзапросы, только расположенные в человекопонятном порядке.
А чего это Вы member'ов так уважаете (имя и фамилия отдельно), а авторов — нет?
И вообще, авторов в отдельную таблицу бы. И многие ко многим авторы-книги.
А то мало ли, вдруг кто-то напишет книгу в соавторстве с кем-то?
Что потом делать с запросом
А еще таблицу поступлений книг, и триггеры на обновление поля stock на приход/уход.
Вот тогда уже будет интереснее.
И вообще, авторов в отдельную таблицу бы. И многие ко многим авторы-книги.
А то мало ли, вдруг кто-то напишет книгу в соавторстве с кем-то?
Что потом делать с запросом
SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';
А еще таблицу поступлений книг, и триггеры на обновление поля stock на приход/уход.
Вот тогда уже будет интереснее.
Ещё стоит заметить, что
читается легче, чем
Это кажется незначительным различием в простом запросе. Но когда запрос сложный и нужно посидеть, «распарсить» его и понять — каждая подобная мелочь сильно облегчает работу.
member_id, book_id, first_name, last_nameчитается легче, чем
memberid, bookid, firstname, lastnameЭто кажется незначительным различием в простом запросе. Но когда запрос сложный и нужно посидеть, «распарсить» его и понять — каждая подобная мелочь сильно облегчает работу.
Зачем в 6.1 подзапрос? :/
Не хватает оператора HAVING (для выборки по результатам группировки)
По поводу конструкции:
WHERE borrowings.bookid IN (SELECT bookid
FROM books…
Не знаю, как сегодня, а лет десять назад на файрбёрде она «оптимизировалась» в список сравнений через OR, что при большой выборке делало мегависюк на стороне сервера. MS SQL кушал такое с лёгкостью.
Есть мнение, что это «джойн для бедных» и вместо него надо использовать именно джойн:
Вместо
Select name from authors
Where id in (select author_id from bests)
Надо:
Select a.name
From authors a
Inner join bests b
On a.id=b.author_id
Where b.id is null
По поводу конструкции:
WHERE borrowings.bookid IN (SELECT bookid
FROM books…
Не знаю, как сегодня, а лет десять назад на файрбёрде она «оптимизировалась» в список сравнений через OR, что при большой выборке делало мегависюк на стороне сервера. MS SQL кушал такое с лёгкостью.
Есть мнение, что это «джойн для бедных» и вместо него надо использовать именно джойн:
Вместо
Select name from authors
Where id in (select author_id from bests)
Надо:
Select a.name
From authors a
Inner join bests b
On a.id=b.author_id
Where b.id is null
Про WHERE… IN (...) полностью поддерживаю. Скажу за MySQL. До версии 5.6 такие подзапросы в секции WHERE сервер вообще никак не оптимизировал. Позже была добавлена оптимизация простых подзапросов без JOIN'ов.
Так что лучше избегать подобных конструкций и выносить их в секцию с JOIN'ами как, например, показано в комментарии выше.
Так что лучше избегать подобных конструкций и выносить их в секцию с JOIN'ами как, например, показано в комментарии выше.
Помнится, в IBM DB/2 был инструмент, позволяющий увидеть, как именно (в какой последовательности и как быстро) запрос будет выполняться в реальности.
Как показала реальная практика, результат оптимизации и скорость выполнения запроса в большей степени зависели от наличия индексов, чем от формы записи запроса.
И при некоторых комбинациях индексов наличие подзапроса во WHERE существенно ускорало выполнение запроса, в то время как использование JOIN приводило к игнорированию индексов, что приводило к замедлению.
Например, при наличии индексов по полям, используемым в WHERE для вложенного запроса и отсутствии индексов для таблицы из FROM, если не путаю за давностью лет, конечно.
Отсюда, кстати, вывод. SQL-оводу имеет смысл знать об индексах, используемых в его СУБД и их особенностях.
Как показала реальная практика, результат оптимизации и скорость выполнения запроса в большей степени зависели от наличия индексов, чем от формы записи запроса.
И при некоторых комбинациях индексов наличие подзапроса во WHERE существенно ускорало выполнение запроса, в то время как использование JOIN приводило к игнорированию индексов, что приводило к замедлению.
Например, при наличии индексов по полям, используемым в WHERE для вложенного запроса и отсутствии индексов для таблицы из FROM, если не путаю за давностью лет, конечно.
Отсюда, кстати, вывод. SQL-оводу имеет смысл знать об индексах, используемых в его СУБД и их особенностях.
«Надо» будет всегда возвращать 0 строк.
Если в подзапросе есть дубликаты — то JOIN продублирует строки основного запроса, в отличии от IN. Избавляться от дублей придется с помощью группировки — а она может сказаться на оптимальности плана, поскольку требует упорядоченности.
MS SQL и сейчас кушает такие подзапросы с легкостью (более того, в плане выполнения IN и JOIN превращаются в одинаковые операции).
Поэтому при использовании СУБД с мощным оптимизатором я бы напротив, советовал в подобных случаях предпочитать IN джойнам.
> Если в подзапросе есть дубликаты — то JOIN продублирует строки основного запроса, в отличии от IN. Избавляться от дублей придется с помощью группировки — а она может сказаться на оптимальности плана, поскольку требует упорядоченности.
Мне ли учить монстров SQL удалять дубликаты?
… JOIN (SELECT DISTINCT author_id FROM bests) b
По поводу неоптимальности. Оптимальность плана — штука такая, её по месту надо смотреть. Выше я писал про «оптимизацию» в файрбёрде (в середине нулевых) именно при использовании where...in. Буквально запрос превращался в что-то вроде WERE (a.OKP='11356432525' or a.OKP='9808958635'… <и так несколько тысяч раз>) и заново разбирался. Эпичненько?
Я не навязываю решение, но говорю об альтернативном мнении. Так что мнения — отдельно, факты — отдельно. А факты для каждой системы могут быть разные. Я всегда использовал в MSSQL Where...in и не парился, а файрбёрд тогда выкинул (именно потому, что имел много таких запросов). Но если у кого-то где-то запрос where...in не оптимизируется или влечёт проблемы — есть ещё вариант с джойном!!!
Мне ли учить монстров SQL удалять дубликаты?
… JOIN (SELECT DISTINCT author_id FROM bests) b
По поводу неоптимальности. Оптимальность плана — штука такая, её по месту надо смотреть. Выше я писал про «оптимизацию» в файрбёрде (в середине нулевых) именно при использовании where...in. Буквально запрос превращался в что-то вроде WERE (a.OKP='11356432525' or a.OKP='9808958635'… <и так несколько тысяч раз>) и заново разбирался. Эпичненько?
Я не навязываю решение, но говорю об альтернативном мнении. Так что мнения — отдельно, факты — отдельно. А факты для каждой системы могут быть разные. Я всегда использовал в MSSQL Where...in и не парился, а файрбёрд тогда выкинул (именно потому, что имел много таких запросов). Но если у кого-то где-то запрос where...in не оптимизируется или влечёт проблемы — есть ещё вариант с джойном!!!
Меня очень правильно поправили:
вместо where… in
вместо where not… in
Select a.name
From authors a
Inner join bests b
On a.id=b.author_idвместо where… in
Select a.name
From authors a
left join bests b
On a.id=b.author_id
Where b.id is nullвместо where not… in
Мне кажется читать SQL станет намного проще если использовать более реальные примеры. Зачем писать
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
Когда можно написать.
WHERE books.stock>(SELECT avg(stock) FROM books)
Зачем писать
SELECT * FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE author='Robin Sharma';
Когда можно написать
SELECT author, sum(stock)
FROM books
WHERE author='Robin Sharma'
GROUP BY author;
И почему бы не использовать конструкцию HAVING, здесь?
SELECT author
FROM (
SELECT author, sum(stock)
FROM books
GROUP BY author
) AS results
WHERE sum > 3;
Возможно вы усложнили запросы, чтобы показать некоторые возможности SQL? Но такой подход боюсь только собьет с толку новичка.
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
Когда можно написать.
WHERE books.stock>(SELECT avg(stock) FROM books)
Зачем писать
SELECT * FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE author='Robin Sharma';
Когда можно написать
SELECT author, sum(stock)
FROM books
WHERE author='Robin Sharma'
GROUP BY author;
И почему бы не использовать конструкцию HAVING, здесь?
SELECT author
FROM (
SELECT author, sum(stock)
FROM books
GROUP BY author
) AS results
WHERE sum > 3;
Возможно вы усложнили запросы, чтобы показать некоторые возможности SQL? Но такой подход боюсь только собьет с толку новичка.
Умею писать на SQL по древней методичке. Использую сейчас в основном MSSQL.
Надеялся, что хоть тут мне толково объяснят что такое INNER JOIN и чем он отличается от JOIN, но, видимо, не судьба :)
Надеялся, что хоть тут мне толково объяснят что такое INNER JOIN и чем он отличается от JOIN, но, видимо, не судьба :)
Ничем. Если есть слева, то будет и справа. Например, у книги всегда есть автор (я тут разделил книжку с фамилиями авторов, чтоб легче искать было), смысла нет ставить меж ними left join. А вот читатель у книги не всегда может быть, поэтому в книга-join-читатель должен быть left join, если мы хотим найти те книги, которые сейчас никто не взял.
Почитали бы MSDN, например.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.
Есть 5 видов операции JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS.
INNER — это вид JOIN по умолчанию, это слово можно пропускать. Т.е. формы a JOIN b и a INNER JOIN b — это строго одно и то же, просто второй вариант считается "академичнее".
Слово OUTER тоже можно не писать.
И тут врывается NATURAL JOIN
И говорит: «Ой, я же синтаксический сахар»! Ничего не добавляет к семантике запроса — только сокращает запись, и может быть как inner, так и left/right.
Бесполезный и опасный тип джоина.
Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки», по которым джоинить — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)
Т.е. если подходить формально, джоины можно делить:
Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки», по которым джоинить — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)
Т.е. если подходить формально, джоины можно делить:
- По поведению в случае отсутствия записей с одной из сторон:
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- По способу указания условий соединения:
ON условие— явное указание условийUSING (колонка1, колонка2, …)— сокращение для «ON левая_таблица.колонка1=правая_таблица.колонка1 and левая_таблица.колонка2=правая_таблица.колонка2 and …»NATURAL— сокращение для «USING (все-колонки-присутствующие-в-обеих-таблицах-сразу)»CROSS— сокращение для «ON true» (в отличие от прошлых трёх способов, CROSS может быть только INNER, правда возможно это нюанс PostgreSQL)
По бесполезности и возможности отстрелить ногу — я полностью согласен. Как уже говорил, в продакшене я не видел чтобы использовали Natural (и без него веселья много).
А вот по классификации, я не то что не согласен, просто это бесполезное занятие, вон Oracle класифицирует их так:
1. Equi Joins
2. Self Joins
3. Cartesian Products
4. Inner Joins
5. Outer Joins
6. Antijoins
7. Semijoins
Хотя по мне Equijoin это частный случай Inner Join, а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS соответственно
А вот по классификации, я не то что не согласен, просто это бесполезное занятие, вон Oracle класифицирует их так:
1. Equi Joins
2. Self Joins
3. Cartesian Products
4. Inner Joins
5. Outer Joins
6. Antijoins
7. Semijoins
Хотя по мне Equijoin это частный случай Inner Join, а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS соответственно
1. А можно источник, где они их так классифицируют?
2. Ну, скажу честно, я пока не вижу никакой причины классифицировать по-другому, чем у меня.
2. Ну, скажу честно, я пока не вижу никакой причины классифицировать по-другому, чем у меня.
Да конечно, только ссылка в виде текста, наверное между GT и хабром есть разница в доступности тегов для Read&Comment пользователей:
Ссылка из доки по 12с: https://docs.oracle.com/database/121/SQLRF/queries006.htm
Интересно стало, а что же там у MS: https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx
Ссылка из доки по 12с: https://docs.oracle.com/database/121/SQLRF/queries006.htm
Интересно стало, а что же там у MS: https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx
Выводы:
1. Oracle не классифицирует джоины так. В той статье у них не классификация, а список важных определений. То, что приведенные названия являются подзаголовками одного уровня, не делает их классификацией (нигде явно не сказано, что это классификация; иначе можно было бы и первый подгалоговок «Join Conditions» считать типом джоина; да и вообще, логика, Equi Join может быть одновременно и Self, и Inner/Outer).
2. Oracle в той статье говорит в джоинах в гораздо более широком смысле. Они рассматривают джоины, не как операторы секции FROM, а как любые действия, приводящие к соединению таблиц (в т.ч. подзапросы) — в отличие от статей в доках postgres и MS. Т.е. это, формально говоря, «другие джоины».
> Хотя по мне Equijoin это частный случай Inner Join
Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств (в впротивоположность, например, «
> а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS
Так и есть. Поймите, Oracle говорит о «других» джоинах — она в отличие от postgres и MS говорит не о синтаксисе, а о конечном результате. Тот же «
Т.е. классификация джоинов-по-Oracle выглядит как-то так: 1. По типу критериев: (а) equi — система равенств, (б) cartesian product — полное отсутствие критериев, (в) все другие. 2. По включению-при-отсутствии-пары: (а) inner — не включаем, (б) outer — включаем, (в) anti — включаем ТОЛЬКО когда нет пары, (г) другое. 3. По связи с самим собой: (а) self, (б) не-self. Semi — отдельная история.
MS смотрит аналогично postgres'у.
1. Oracle не классифицирует джоины так. В той статье у них не классификация, а список важных определений. То, что приведенные названия являются подзаголовками одного уровня, не делает их классификацией (нигде явно не сказано, что это классификация; иначе можно было бы и первый подгалоговок «Join Conditions» считать типом джоина; да и вообще, логика, Equi Join может быть одновременно и Self, и Inner/Outer).
2. Oracle в той статье говорит в джоинах в гораздо более широком смысле. Они рассматривают джоины, не как операторы секции FROM, а как любые действия, приводящие к соединению таблиц (в т.ч. подзапросы) — в отличие от статей в доках postgres и MS. Т.е. это, формально говоря, «другие джоины».
> Хотя по мне Equijoin это частный случай Inner Join
Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств (в впротивоположность, например, «
table1 t1 JOIN table2 t2 ON (t1.x-t2.x)²+(t1.y-t2.y)²≤1.0»). Equi/не-Equi и Inner/Outer — это несвязанные вещи.> а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS
Так и есть. Поймите, Oracle говорит о «других» джоинах — она в отличие от postgres и MS говорит не о синтаксисе, а о конечном результате. Тот же «
FROM table1 t1, table2 t2 WHERE t1.id=t2.id» по postgres/MS будет cross join, а по Oracle — equi inner.Т.е. классификация джоинов-по-Oracle выглядит как-то так: 1. По типу критериев: (а) equi — система равенств, (б) cartesian product — полное отсутствие критериев, (в) все другие. 2. По включению-при-отсутствии-пары: (а) inner — не включаем, (б) outer — включаем, (в) anti — включаем ТОЛЬКО когда нет пары, (г) другое. 3. По связи с самим собой: (а) self, (б) не-self. Semi — отдельная история.
MS смотрит аналогично postgres'у.
>Oracle говорит о «других» джоинах
Магические «другие» JOIN мы можем увидеть в плане запросов.
NESTED LOOPS (Equi | Self | Cartesian | Outer | Anti | Semi)
HASH JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)
MERGE JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)
Причем зачастую они будут одинаковы и у Oracle и у SQL Server (pg под рукой сейчас нет)
>Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств
Тогда скажите запрос:
SELECT *
FROM a, b
WHERE a.id = b.id
Это EQUI JOIN или INNER JOIN
Магические «другие» JOIN мы можем увидеть в плане запросов.
NESTED LOOPS (Equi | Self | Cartesian | Outer | Anti | Semi)
HASH JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)
MERGE JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)
Причем зачастую они будут одинаковы и у Oracle и у SQL Server (pg под рукой сейчас нет)
>Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств
Тогда скажите запрос:
SELECT *
FROM a, b
WHERE a.id = b.id
Это EQUI JOIN или INNER JOIN
> Тогда скажите запрос
Ответ на Ваш вопрос содержится в том сообщении, на которое Вы отвечаете: equi inner.
> Это EQUI JOIN или INNER JOIN
Вопрос поставлен некорректно. Тот перечень, что Вы приводили, — это не непересекающиеся классы.
Вот типы джоинов.
Ответ на Ваш вопрос содержится в том сообщении, на которое Вы отвечаете: equi inner.
> Это EQUI JOIN или INNER JOIN
Вопрос поставлен некорректно. Тот перечень, что Вы приводили, — это не непересекающиеся классы.
Вот типы джоинов.
Бесполезный и опасный тип джоина.
Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки, по которым джоинить» — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)
Т.е., строго говоря, джоины можно делить:
Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки, по которым джоинить» — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)
Т.е., строго говоря, джоины можно делить:
- По поведению в случае отсутствия записей с одной из сторон:
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- По способу указания условий соединения:
ON условие— явное указание условийUSING (колонка1, колонка2, …)— сокращение для «ON левая_таблица.колонка1=правая_таблица.колонка1 and левая_таблица.колонка2=правая_таблица.колонка2 and …»NATURAL— сокращение для «USING (все-колонки-присутствующие-в-обеих-таблицах-сразу)»CROSS— сокращение для «ON true» (в отличие от способов 1-3, CROSS в PostgreSQL может быть только INNER)
Use having clause, Luke.
Select внутри where сильно замедляет скорость в ряде случаев.
Средний разработчик, боящийся SQL? Импосибру.
Хотя, «средний» разработчик, который всю дорогу ORM использовал в режиме «оно само», может и испугается.
Хотя, «средний» разработчик, который всю дорогу ORM использовал в режиме «оно само», может и испугается.
не хватает описания использования having. Полезно упомянуть, что можно использовать не только COUNT(), но и COUNT(x)
SQL — важная и нужная штука, но мои глаза всегда мозолил такой порядок аргументов, сравните:
SELECT поля FROM таблица…
DELETE FROM таблица…
UPDATE таблица (поля) SET…
INSERT INTO таблица (поля)…
Что-то примерно такое представляется мне более структурированным:
SELECT FROM таблица [FIELDS поля]…
DELETE IN таблица…
UPDATE IN таблица FIELDS поля=новые значения…
INSERT IN таблица [FIELDS поля[=новые значения]]… /*или по-старому*/
Что лучше:
1. Консистентный порядок
2. Более заметна разница между модификацией данных и получением
3. Единый формат описания полей
В общем, помечтал и хватит (:
SELECT поля FROM таблица…
DELETE FROM таблица…
UPDATE таблица (поля) SET…
INSERT INTO таблица (поля)…
Что-то примерно такое представляется мне более структурированным:
SELECT FROM таблица [FIELDS поля]…
DELETE IN таблица…
UPDATE IN таблица FIELDS поля=новые значения…
INSERT IN таблица [FIELDS поля[=новые значения]]… /*или по-старому*/
Что лучше:
1. Консистентный порядок
2. Более заметна разница между модификацией данных и получением
3. Единый формат описания полей
В общем, помечтал и хватит (:
Что список полей перед FROM в SELECT — нелогично, — абсолютно согласен (получается, мы сначала используем alias таблицы и только потом объявляем его).
Но уж так сложилось…
Но уж так сложилось…
SELECT импользуєтся не только для таблиц… но и для переменных… потому вот как есть — логичнее…
INSERT таблица (asd,asd) VALUE («2»,«3») в этих дужечках еще на этапе расшифровки запроса, создается набор значений, которой потом ставится в базу. И запутаться сложнее — вставка и выбор визуально совсем разные…
INSERT таблица (asd,asd) VALUE («2»,«3») в этих дужечках еще на этапе расшифровки запроса, создается набор значений, которой потом ставится в базу. И запутаться сложнее — вставка и выбор визуально совсем разные…
потому вот как есть — логичнее…
Отнюдь. Всего лишь можно опускать секцию FROM (аналогично тому, как сейчас опускаются секции FROM/GROUP BY/пр., когда они не нужны), когда запрос идёт не из таблиц (хранимая функция и пр.):
SELECT FROM users u WHERE u.birthdate<'1950-01-01' EXPRESSIONS u.name, u.surname;
SELECT EXPRESSIONS myfunction(1, 2) as myfunc12result;
Т.е. текущий синтаксис такой как он есть не потому, что он самый логичный, а потому, что так сложилось.
Про INSERT то не я говорил,
Про INSERT то не я говорил,
INSERT INTO table t (f1, f2) VALUES (v1, v2), (v3, v4) меня устраивает, это уже отвечайте тому, кто писал про INSERT (и, да, синтаксис никак не связан с тем, что «создается набор значений», например, INSERT INTO users SET name='vasya', surname='pupkin' SET name='fedya', surname='petrov' тоже прекрасно бы «создавало набор значений»).И, кстати, вот ещё одна причина указывать выражения после всего, а не перед FROM (окромя общей логичности и использования ещё-не-объявленых алиасов-таблиц):
Чайники не понимают, что расчёт этих выражений происходит после группировки GROUP BY'ем, а не до.
https://habrahabr.ru/post/305926/#comment_9708148
При "
Чайники не понимают, что расчёт этих выражений происходит после группировки GROUP BY'ем, а не до.
https://habrahabr.ru/post/305926/#comment_9708148
При "
SELECT FROM … WHERE … GROUP BY … HAVING … EXPRESSIONS …" бы таких проблем не было.Вы так и говорите в жизни «возьмите из корзины мяч и кубик», а не «возьмите кубик и мяч из корзины»?
Это зависит от того, о чем мы говорили последнем.
Т.е., если мы рассматриваем корзину как важный этап последовательности действий, мы указываем её в начале. Если мы рассматриваем корзину как незначительное уточнение, мы указываем её в конце. (Угадайте, чем является указание таблицы в SQL-запросе.)
И да, множественность предметов увеличивает вероятность того, что мы назовём корзину вначале: «возьми из корзины мячик, кубик, расчёску, бантик и погремушку».
Найди большую красную корзину. Нашёл? Возьми из корзины синий мячик.
Хочешь мячик? Возьми из его из красной корзины.
Т.е., если мы рассматриваем корзину как важный этап последовательности действий, мы указываем её в начале. Если мы рассматриваем корзину как незначительное уточнение, мы указываем её в конце. (Угадайте, чем является указание таблицы в SQL-запросе.)
И да, множественность предметов увеличивает вероятность того, что мы назовём корзину вначале: «возьми из корзины мячик, кубик, расчёску, бантик и погремушку».
для начинающих рекомендую почитать что-то из серии head first издательства O`Reily (с аббревиатурой SQL в названии).
Там в картинках, с хорошими примерами, последовательно и системно будет описана работа с запросами.
можно скачать в електронном виде…
Там в картинках, с хорошими примерами, последовательно и системно будет описана работа с запросами.
можно скачать в електронном виде…
Конечно, в таком виде этот запрос читать невозможно:
SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
GROUP BY members.firstname, members.lastname;Но после небольшого форматирования, выкидывания ненужных частей (зачем здесь соединение с books, если таблица вообще никак не используется???) и простановки псевдонимов таблиц разобраться в нём уже не составляет труда:
select m.firstname||' '||m.lastname "full name"
from members m
join borrowings b on (b.memberid = m.memberid)
where b.bookid in (select bookid from books where stock > (select avg(stock) from books))
group by m.firstname, m.lastname
;НЛО прилетело и опубликовало эту надпись здесь
Требование очень логичное, если не считать одно «но»:
к сожалению, когда Вы уже перечислили в GROUP BY все поля первичного ключа (или другого уникального ключа) какой-то таблицы, при выборке других полей этой таблицы их всё равно приходится указывать в GROUP BY:
Почему-то в таком запросе приходится указывать в GROUP BY не только u.id, но и u.name, u.surname. Вот это меня выбешивает (нелогично, ведь если все поля уникального ключа таблицы перечислены в GROUP BY, то перечисляй/не перечисляй остальные — разницы уже нет). Но в остальном это требование очень логично.
к сожалению, когда Вы уже перечислили в GROUP BY все поля первичного ключа (или другого уникального ключа) какой-то таблицы, при выборке других полей этой таблицы их всё равно приходится указывать в GROUP BY:
CREATE TABLE users (
id serial not null primary key,
name text not null,
surname text not null
);
CREATE TABLE items (
id serial not null primary key,
owner_user_id integer not null references users (id),
...
);
SELECT u.id, u.name, u.surname, count(*) as item_count
FROM users u left join items i on u.id=i.owner_user_id
GROUP BY u.id; -- , u.name, u.surname
Почему-то в таком запросе приходится указывать в GROUP BY не только u.id, но и u.name, u.surname. Вот это меня выбешивает (нелогично, ведь если все поля уникального ключа таблицы перечислены в GROUP BY, то перечисляй/не перечисляй остальные — разницы уже нет). Но в остальном это требование очень логично.
НЛО прилетело и опубликовало эту надпись здесь
Я давно не имел дела с MySQL, но насколько я помню, там всё было через ж#пу.
В случае с GROUP BY: там вообще это требование не проверялось, например, можно было сделать
Что полный бред (хотя может с тех пор что-то изменилось).
В случае с GROUP BY: там вообще это требование не проверялось, например, можно было сделать
SELECT t.field2 FROM table1 t GROUP BY t.field1, где field1 отнюдь не уникальный ключ.Что полный бред (хотя может с тех пор что-то изменилось).
НЛО прилетело и опубликовало эту надпись здесь
Всё, к сожалению, не настраивается. Остаётся всё равно куча бреда не по стандарту. Например, в какой-то версии (не знаю как щас):
Если Вы хотите из набора строк (которые получаются в результате GROUP BY) получить одно значение, Вы используете агрегатную функцию. Точка. Получение первого значения из набора и получение неопределённого значения из набора — это, как и, например, получение суммы, тоже агрегатные функции.
Если таких функций нету, Вы можете их дописать.
Теперь понимаете?
CREATE TABLE t3 (
...,
t1_id int not null REFERENCES t1 (id), -- тупо игнорируется!!!
t2_id int not null,
FOREIGN KEY (t2_id) REFERENCES t2 (id)
);
Не вижу бреда в выборке одного поля при группировке по другому.
Если Вы хотите из набора строк (которые получаются в результате GROUP BY) получить одно значение, Вы используете агрегатную функцию. Точка. Получение первого значения из набора и получение неопределённого значения из набора — это, как и, например, получение суммы, тоже агрегатные функции.
Если таких функций нету, Вы можете их дописать.
SELECT
count(i.*) as count,
sum(i.price) as total_price,
agg_first(i.name) as first_name,
agg_some(i.name) as some_name,
agg_last(i.name) as last_name
FROM
items i
GROUP BY
i.category_id -- неуникальное поле
Теперь понимаете?
НЛО прилетело и опубликовало эту надпись здесь
А что вы хотите этим GROUP BY сделать?
Попробую предпроложить, что раз нет ни одной агрегирующей функции, то наверное хотите получить уникальные записи
Может тогда лучше так?
SELECT DISTINCT
s.session_user_id AS user_id
, s.session_viewonline
, u.username
, u.user_type
, u.user_colour
FROM phpbb_sessions s
LEFT JOIN phpbb_users u
ON s.session_user_id = u.user_id
WHERE s.session_time >= 1469004330
AND s.session_user_id <> 1
ORDER BY u.username_clean
Попробую предпроложить, что раз нет ни одной агрегирующей функции, то наверное хотите получить уникальные записи
Может тогда лучше так?
SELECT DISTINCT
s.session_user_id AS user_id
, s.session_viewonline
, u.username
, u.user_type
, u.user_colour
FROM phpbb_sessions s
LEFT JOIN phpbb_users u
ON s.session_user_id = u.user_id
WHERE s.session_time >= 1469004330
AND s.session_user_id <> 1
ORDER BY u.username_clean
У него в запросе логическая ошибка. Он хочет получить перечень активных пользователей (не сессий; на одного пользователя может приходиться несколько сессий). Для этого он group-by'ит по пользователю (точнее по s.session_user_id, но оно джоинится с первичным ключём пользователя). Однако поля сессии он не пропускает через агрегатные функции.
Скорее всего, логически-правильный запрос должен выглядеть так:
Скорее всего, логически-правильный запрос должен выглядеть так:
SELECT u.*, bool_or(s.session_viewonline) as at_least_one_session_viewonline
FROM phpbb_sessions s left join phpbb_users u on s.session_user_id=u.user_id
WHERE s.session_time>=1469004330 and s.session_user_id<>1
GROUP BY u.* -- или "GROUP BY u.user_id", или "GROUP BY s.session_user_id" (пофиг)
ORDER BY u.username_clean;
НЛО прилетело и опубликовало эту надпись здесь
Это я же и обсуждал.
Я потому и пишу «логически-правильный»; это псевдокод; в реале конечно же, к сожалению, придётся перечислять после GROUP BY все поля.
Я к тому, чтобы Вы отличали реализацию стандарта с недоработкой (когда выражение, единственность значения которого очевидна, нельзя выбрать, не указав явно в GROUP BY) от полной вседозволенности (когда вообще никаких проверок нет, что бред).
Я потому и пишу «логически-правильный»; это псевдокод; в реале конечно же, к сожалению, придётся перечислять после GROUP BY все поля.
Я к тому, чтобы Вы отличали реализацию стандарта с недоработкой (когда выражение, единственность значения которого очевидна, нельзя выбрать, не указав явно в GROUP BY) от полной вседозволенности (когда вообще никаких проверок нет, что бред).
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
Ну, в таком случае я согласен.
Просто Вы не путайте две разные вещи:
Т.е. в Вашем запросе ругается на u.username_clean излишне. В Вашем запросе будет ругаться на s.session_viewonline очень правильно.
Просто Вы не путайте две разные вещи:
- Употребление полей таблицы вне GROUP BY, когда первичный/уникальный ключ этой таблицы уже указан в GROUP BY. В принципе, с логической точки зрения допустимо, но в стандарте почему-то запрещено.
- Употребление вне GROUP BY полей, уникальность которых никак не гарантируется структурой запроса. Стандартом запрещено и очень правильно.
Т.е. в Вашем запросе ругается на u.username_clean излишне. В Вашем запросе будет ругаться на s.session_viewonline очень правильно.
Потому что единственность s.session_user_id и u.* гарантируется структурой запроса (единственность s.session_user_id гарантируется тем, что оно непосредственно указано в GROUP BY, а единственность u.* гарантируется тем, что u.user_id (который первичный ключ для u) за-join-ен с первым).
Единственность всех остальных полей в s.* (кроме s.session_user_id) структурой запроса не гарантируется. Использоваться их вне агрегатных функций — нельзя. Даже просто с логической точки зрения (у Вас на одну строку результата приходится потенциально-несколько значений s.session_viewonline; какое из них брать: первое, bool_and или bool_or?).
Единственность всех остальных полей в s.* (кроме s.session_user_id) структурой запроса не гарантируется. Использоваться их вне агрегатных функций — нельзя. Даже просто с логической точки зрения (у Вас на одну строку результата приходится потенциально-несколько значений s.session_viewonline; какое из них брать: первое, bool_and или bool_or?).
В один прекрасный день окажется, что то поле, которое раньше было уникальным уже не может таковым быть. И вы убираете ограничение уникальности. Упс. Половина запросов сломалась на ровном месте… Так что о логичности вашей хотелки ещё можно поспорить.
Почему тогда
сделать нельзя?
Упс…
SELECT t.* FROM table1 t GROUP BY t.*;
сделать нельзя?
Упс…
Таки можно, но не так.
SELECT DISTINCT t.* FROM t
Не знаю только, можно ли добавить в выборку агрегатные функции. Проверить не могу, ибо сегодня я маляр-штукатур
Кстати, иметь в таблице полностью идентичные записи очень, очень некошерно, т.к средствами sql их невозможно идентифицировать по отдельности. Именно поэтому и вводят традиционно автоинкрементный id, если нет строгого регламента на наполнение, и осмысленного uid поля или комбинации полей
SELECT DISTINCT t.* FROM t
Не знаю только, можно ли добавить в выборку агрегатные функции. Проверить не могу, ибо сегодня я маляр-штукатур
Кстати, иметь в таблице полностью идентичные записи очень, очень некошерно, т.к средствами sql их невозможно идентифицировать по отдельности. Именно поэтому и вводят традиционно автоинкрементный id, если нет строгого регламента на наполнение, и осмысленного uid поля или комбинации полей
Речь не о DISTINCT, а о том, что я хочу сделать, например:
И так тоже нельзя:
Я об этом.
Это вообще мимо кассы.
SELECT t.*, count(i.*) as item_count, sum(i.price) as total_price
FROM table1 t left join items i on t.id=i.parent_id GROUP BY t.*;
-- а нельзя
И так тоже нельзя:
SELECT t.*, count(i.*) as item_count, sum(i.price) as total_price
FROM table1 t left join items i on t.id=i.parent_id GROUP BY t.id;
-- подразумевая, что table1 имеет PRIMARY KEY (id)
Я об этом.
Кстати, иметь в таблице полностью идентичные записи очень, очень некошерно
Это вообще мимо кассы.
Может быть так подойдет?
SELECT t.*
, count(*) over (PARTITION BY t.id) as item_count
, sum(i.price) over (PARTITION BY t.id) as total_price
FROM table1 t
LEFT JOIN items i
ON t.id = i.parent_id
;
SELECT t.*
, count(*) over (PARTITION BY t.id) as item_count
, sum(i.price) over (PARTITION BY t.id) as total_price
FROM table1 t
LEFT JOIN items i
ON t.id = i.parent_id
;
Ну и оно вернёт кучу повторяющихся записей (по одной строке на пару (t, i), а не на t).
Которые, конечно, можно убрать DISTINCT'ом.
Но, по-моему, это не самое логичное решение.
(Хотя, не спорю, приемлемое; наравне с ручным перечислением в GROUP BY и подзапросом.)
Которые, конечно, можно убрать DISTINCT'ом.
Но, по-моему, это не самое логичное решение.
(Хотя, не спорю, приемлемое; наравне с ручным перечислением в GROUP BY и подзапросом.)
Оконки еще будут медленнее GROUP BY.
Еще в голову пришло динамически собирать запрос.
Но вообще использовать SELECT * это как то не по феншую.
Еще в голову пришло динамически собирать запрос.
Но вообще использовать SELECT * это как то не по феншую.
У меня запросы и так динамически собираются :).
Тут вопрос (мой) не в том, как сделать, чтоб меньше писать.
Тут вопрос в том, что стандарт касательно GROUP BY (или его реализация в PostgreSQL — хотя скорее всё таки стандарт) немного недоработан.
То, что нельзя вне аггрегатных функций использовать выражения, единственность значений которых не гарантируется — тут я обомя руками за.
То, что считается, что единственность гарантируется только для выражений, явно указанных в GROUP BY — это недоработка. Если первичный ключ какой-то таблицы целиком указан в GROUP BY или целиком за-join-ен с полями, указанными в GROUP BY — то любые выражения над полями этой таблицы имеют единственное значение. Аналогично для non-null UNIQUE-ключей.
Особо прикалывает, что PostgresSQL позволяет писать «GROUP BY t» и «GROUP BY t.*» (где t — алиас таблицы), но обрабатывается оно неожиданно: первый вариант позволяет только непосредственно «
Тут вопрос (мой) не в том, как сделать, чтоб меньше писать.
Тут вопрос в том, что стандарт касательно GROUP BY (или его реализация в PostgreSQL — хотя скорее всё таки стандарт) немного недоработан.
То, что нельзя вне аггрегатных функций использовать выражения, единственность значений которых не гарантируется — тут я обомя руками за.
То, что считается, что единственность гарантируется только для выражений, явно указанных в GROUP BY — это недоработка. Если первичный ключ какой-то таблицы целиком указан в GROUP BY или целиком за-join-ен с полями, указанными в GROUP BY — то любые выражения над полями этой таблицы имеют единственное значение. Аналогично для non-null UNIQUE-ключей.
Особо прикалывает, что PostgresSQL позволяет писать «GROUP BY t» и «GROUP BY t.*» (где t — алиас таблицы), но обрабатывается оно неожиданно: первый вариант позволяет только непосредственно «
SELECT t FROM …» и «SELECT func1(t) FROM …» (но не «SELECT t.field1 FROM …» — при том, что мы можем успешно заюзать «CREATE FUNCTION func1(t table1) RETURNS type1 AS $$begin return t.field1; end$$ LANGUAGE plpgsql IMMUTABLE»), а второй, судя по всему, вообще никак не срабатывает.>Это вообще мимо кассы.
Ну, каков вопрос, таков и ответ
Здесь группировка бессмысленна, если в таблице нет дубликатов, или я не прав?
Про запрос с джойном: кто мешает группировать только правую таблицу по внешнему ключу в подзапросе?
Заметь, группировка идёт по одному заведомо индексированному полю, и набор данных для джойна меньше, чем в исходной таблице. Так что и по производительности можно выиграть
Ну, каков вопрос, таков и ответ
SELECT t.* FROM table1 t GROUP BY t.*;Здесь группировка бессмысленна, если в таблице нет дубликатов, или я не прав?
Про запрос с джойном: кто мешает группировать только правую таблицу по внешнему ключу в подзапросе?
SELECT t.*,i.item_count, i.total_price
FROM table1 t
left join (
select parent_id, count(*) as item_count, sum(price) as total_price
from items
group by parent_id
) i
on t.id=i.parent_id;Заметь, группировка идёт по одному заведомо индексированному полю, и набор данных для джойна меньше, чем в исходной таблице. Так что и по производительности можно выиграть
>Это вообще мимо кассы.
Ну, каков вопрос, таков и ответ
SELECT t.* FROM table1 t GROUP BY t.*;
Здесь группировка бессмысленна, если в таблице нет дубликатов, или я не прав?
Обсуждение велось в контексте. Имелось в виду «
SELECT …, t.*, … FROM table1 t … GROUP BY …, t.*, …» — если уж буквально. Обсуждалась нелогичность того, что нельзя обращаться ко всем полям таблицы, указав в GROUP BY только её первичный ключ, а также того, что просто «t.*» в GROUP BY указать нельзя.Про запрос с джойном: кто мешает группировать только правую таблицу по внешнему ключу в подзапросе?
SELECT t.*,i.item_count, i.total_price
FROM table1 t
left join (
select parent_id, count(*) as item_count, sum(price) as total_price
from items
group by parent_id
) i
on t.id=i.parent_id;
Заметь, группировка идёт по одному заведомо индексированному полю, и набор данных для джойна меньше, чем в исходной таблице. Так что и по производительности можно выиграть
Разумный вариант. Самый разумный из всех предложенных. Очень надеюсь, что он не быстрее (что DBMS оптимизирует его и исходный до одного состояния) — но по красоте он 100% лучше.
Небольшая разница только между исходным вариантом и новым: если каким-то строкам table1 не соответствует ни одной строки items, то в новом варианте значения всех статистик будут NULL, а в старом — по специфике аггрегатной функции (например, count(*) в старом варианте вернёт 0). Но это несущественно.
P.S.: Если не секрет, почему маляр-штукатур?
P.S.: Если не секрет, почему маляр-штукатур?
Уже два дня как сантехник. Берусь за всё. Кушать хочется, а из разработчиков ушёл по глупости лет восемь назад на тёплое место. Место остыло, а специализация старая сегодня не востребована (работал в Дельфи ещё до .NET), теперь с тоски переучиваюсь на веб-разработку. Когда время есть, ибо я кормлю жену, четырёх детей и один банк.
Так что не спешите расслабляться раньше пенсии. А любимая работа в целом лучше хорошей работы.
Так что не спешите расслабляться раньше пенсии. А любимая работа в целом лучше хорошей работы.
Зато у Вас есть жена и дети. То, что есть отнюдь не у каждого разработчика.
А почему веб-разработка? C++ разве не поближе к Delphi будет?
А почему веб-разработка? C++ разве не поближе к Delphi будет?
> А почему веб-разработка? C++ разве не поближе к Delphi будет?
Тут важнее не язык, а область его приложения. Дельфи всегда был заточен под БД и чуток под клиент-серверные технологии, из за чего остался живым именно в .NET. Я делал приложения для работы с базами данных управленческого учёта, аддоны для системы разработки технологической документации (с целью приведения интерфейса ко вменяемому уровню UX), до кучи программировал на полставки промышленные контроллеры в цехе спецстанков и систему сбора статистики со станков стороннего производителя. В общем, полноценная жизнь заводского программиста из спецназа местечкового значения АКА «Группа САПР».
В сухом остатке, помимо промышленных контроллеров (это были Мицубиси, а сейчас в тренде Сименс), фронтенд и базы данных.
Ушёл уже с должности руководителя группы (в мелкую фирму «главным» инженером) из-за того, что завод стагнировал и никому ничего было не надо. Бюджет отсутствовал («Вам же уже все программы купили ещё пять лет назад»), мы вырождались в группу документооборота электронных копий бумажных чертежей с двумя инженерами чтобы слать их по почте и класть на сервер и двумя студентами чтобы сканировать их (на планшетном сканере А3) и сшивать.
А в последнем марте я пробежался по работным сайтам, посмотрел, что мне близко из востребованного в нашей местности — и вот, учу веб-девелопмент, начиная с фронтенда (самое востребованное). Как в работе пауза, так и погружаюсь. Хотя мне, вероятно, был бы более по душе бекэнд, с его развитыми и хорошо структурированными фреймворками. Максимальное удовольствие от работы я получал именно тогда, когда строил универсальные инструменты для решения типовых задач или систематизировал и сериализовал подход к решению задач нетривиальных — и вот, пока я спал, оказалось, что это целый мир…
Такое вот «почему».
P.S. И хочется же в команду с развитыми понятиями о производстве ПО, где другим небезразличны вопросы организации процесса. В общем, в коллектив, который не является «толпой чёрных ящиков». Фриланса не хочется — это не только оставаться одиночкой (чего совсем не хочется), в пределе это наверняка выльется в узкую специализацию, отлаженный процесс и скуку смертную без дальнейшего развития. К тому же я наработался уже «строительным узбеком», и не хочется превращаться в «компьютерного индуса».
Тут важнее не язык, а область его приложения. Дельфи всегда был заточен под БД и чуток под клиент-серверные технологии, из за чего остался живым именно в .NET. Я делал приложения для работы с базами данных управленческого учёта, аддоны для системы разработки технологической документации (с целью приведения интерфейса ко вменяемому уровню UX), до кучи программировал на полставки промышленные контроллеры в цехе спецстанков и систему сбора статистики со станков стороннего производителя. В общем, полноценная жизнь заводского программиста из спецназа местечкового значения АКА «Группа САПР».
В сухом остатке, помимо промышленных контроллеров (это были Мицубиси, а сейчас в тренде Сименс), фронтенд и базы данных.
Ушёл уже с должности руководителя группы (в мелкую фирму «главным» инженером) из-за того, что завод стагнировал и никому ничего было не надо. Бюджет отсутствовал («Вам же уже все программы купили ещё пять лет назад»), мы вырождались в группу документооборота электронных копий бумажных чертежей с двумя инженерами чтобы слать их по почте и класть на сервер и двумя студентами чтобы сканировать их (на планшетном сканере А3) и сшивать.
А в последнем марте я пробежался по работным сайтам, посмотрел, что мне близко из востребованного в нашей местности — и вот, учу веб-девелопмент, начиная с фронтенда (самое востребованное). Как в работе пауза, так и погружаюсь. Хотя мне, вероятно, был бы более по душе бекэнд, с его развитыми и хорошо структурированными фреймворками. Максимальное удовольствие от работы я получал именно тогда, когда строил универсальные инструменты для решения типовых задач или систематизировал и сериализовал подход к решению задач нетривиальных — и вот, пока я спал, оказалось, что это целый мир…
Такое вот «почему».
P.S. И хочется же в команду с развитыми понятиями о производстве ПО, где другим небезразличны вопросы организации процесса. В общем, в коллектив, который не является «толпой чёрных ящиков». Фриланса не хочется — это не только оставаться одиночкой (чего совсем не хочется), в пределе это наверняка выльется в узкую специализацию, отлаженный процесс и скуку смертную без дальнейшего развития. К тому же я наработался уже «строительным узбеком», и не хочется превращаться в «компьютерного индуса».
Меня тоже раздражает это, мог бы и автоматом доставить все поля, что в агрегатах не участвуют. Учился SQL когда не было такого требования (хочешь — пиши, не хочешь — не пиши), потом перешли на следующую версию и, помнится, тогда много запросов дописывали.
Можно добавить в статью, что тестирования запросов существует
sqlfiddle.com
там можно попробовать запросы на разных БД: MySQL, Oracle, PostgesSQL, MSSQL, SQLite
P.S. а ещё есть
eval.in
sqlfiddle.com
там можно попробовать запросы на разных БД: MySQL, Oracle, PostgesSQL, MSSQL, SQLite
P.S. а ещё есть
eval.in
Спасибо за статью
НЛО прилетело и опубликовало эту надпись здесь
Каждый раз когда вижу этот «русский код» в голове, как будто, что то подклинивает :)
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
да, я знаю об этом, я про сам вариант описания операторов на русском, и про непривычность относительно повседневного SQL для лично меня .
Конкретно в запросе выше проблема не в русском языке — а в форматировании. Для начала — поставить отступы. Конструкцию "КОГДА… ТОГДА… ИНАЧЕ" лучше было бы записать в одну строку. Вместо капитанского "ВложенныйЗапрос" лучше бы написать что именно этот запрос делает. Или напротив, сократить псевдоним до "ВЗ", чтобы не отвлекал.
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
Низкий вам поклон от новичка, так как только начал заниматься SQL статья лично мне уже многим помогла.
Мне кажется в пункте 6.2 результат запроса не соответствует поставленной задаче.
Нужно: узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех.
Получили — книги авторов, чьих книг в библиотеке больше 3.
Книга, экземпляров которой в библиотеке больше 3, одна. Это Who Will Cry When You Die?
Нужно: узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех.
Получили — книги авторов, чьих книг в библиотеке больше 3.
Книга, экземпляров которой в библиотеке больше 3, одна. Это Who Will Cry When You Die?
Я один вижу, что в примерах используется ключевое слово INNER, но не объясняется что это такое?
«INNER JOIN» и «JOIN» — это одно и то же («INNER» в данном случае просто игнорируемое слово).
Объяснять 100%-необходимо было бы, если бы он где-то использовал другие формы JOIN'ов: «LEFT [OUTER] JOIN», «RIGHT [OUTER] JOIN» или «FULL [OUTER] JOIN» — но такого у него нет.
Хотя сказать пару слов по этому поводу и использовать везде однотипный синтаксис (или JOIN, или INNER JOIN) было бы безусловно лучше.
Объяснять 100%-необходимо было бы, если бы он где-то использовал другие формы JOIN'ов: «LEFT [OUTER] JOIN», «RIGHT [OUTER] JOIN» или «FULL [OUTER] JOIN» — но такого у него нет.
Хотя сказать пару слов по этому поводу и использовать везде однотипный синтаксис (или JOIN, или INNER JOIN) было бы безусловно лучше.
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
А есть ли в каких-то БД какой-нибудь auto join? Например, если я задал в таблице связь между ACCOUNT.ACCOUNT_TYPE и ACCOUNT_TYPES.TYPE_ID, почему я не могу написать просто select ACCOUNT.ACCOUNT_NUMBER, ACCOUNT_TYPES.TYPE_NAME from ACCOUNT, ACCOUNT_TYPES или select ACCOUNT.ACCOUNT_NUMBER, ACCOUNT_TYPES.TYPE_NAME as TYPE from ACCOUNT auto join ACCOUNT_TYPES безо всяких дополнительных on или where?
Можно без всяких on и where — using есть и natural join. Но всё это фигня, ибо связей может быть больше одной.
А что мешает в случае если связей несколько просто выдавать ошибку?
То, что схема со временем имеет тенденцию усложняться. Сегодня из таблицы table1 одна связь на table2, через полгода добавили ещё одну (с другим смыслом). И половина уже отлаженного приложения перестала работать.
У меня была когда-то более здравая мысль — разрешить «ходить» по именованым foreign key'ам (при условии, что они указывают на UNIQUE/PRIMARY KEY).
Например, в схеме:
Например, в схеме:
CREATE TABLE universities (
id serial not null PRIMARY KEY,
…
);
CREATE TABLE faculties (
id serial not null PRIMARY KEY,
…,
university_id int not null,
CONSTRAINT university FOREIGN KEY (university_id) REFERENCES universities (id)
);
CREATE TABLE students (
id serial not null PRIMARY KEY,
…,
faculty_id int not null,
CONSTRAINT faculty FOREIGN KEY (faculty_id) REFERENCES faculties (id)
);
SELECT s.…, f.…, u.…
FROM
students s
join faculties f on s.faculty_id=f.id
join universities u on f.university_id=u.id;
SELECT s.…, s.faculty.…, s.faculty.university.… FROM students s;
Нет, т.к. тебе могут разные типы соединений понадобится с этими таблицами.
Например, есть таблица USERS у которой необязательный ключ на Т.ACCOUNT — ACCOUNT_ID
Запрос 1:
Запрос 2:
И первый, и второй запросы верные, но выдают разные результаты.
Например, есть таблица USERS у которой необязательный ключ на Т.ACCOUNT — ACCOUNT_ID
Запрос 1:
SELECT *
FROM USERS
INNER JOIN ACCOUNT ON USERS.ACCOUNT_ID = ACCOUNT.ID
INNER JOIN ACCOUNT_TYPES ON ACCOUNT.TYPE_ID = ACCOUNT_TYPES.ID
Запрос 2:
SELECT *
FROM USERS
LEFT JOIN ACCOUNT ON USERS.ACCOUNT_ID = ACCOUNT.ID
LEFT JOIN ACCOUNT_TYPES ON ACCOUNT.TYPE_ID = ACCOUNT_TYPES.ID
И первый, и второй запросы верные, но выдают разные результаты.
Смысл чтоб ON были необязательными, если ключ присутствует. Т.е., left, inner, full, cross остаются, но без on. Тогда разработчик будет каждый раз чесать репу «а по каким полям?», а сервак мусолить винт «а по каким полям?». Такое может прокатить, но на простых запросах. Проще разработчика разок напрячь, чтоб правильно написал что хочет, ему же потом самому легче будет.
SQL так-то любит чтоб всё было разжёвано.
SQL так-то любит чтоб всё было разжёвано.
А так-то, без ON вполне здравая мысль.
Разработчику особо дела нет как там эти поля называются, БД может проверить что должен существовать FK в единственном числе и одним концом смотреть на PK и, если уж на то пошло, то IDE может подсказать разрабу что это за FK и какие ключи там есть.
Так что в целом мысль вполне годная.
Если я создал FK, то и соединять в 99% случаев буду по нему.
Лучше бы БД варнинги выдавала на JOIN'ы без FK.
Хотя тут тоже не однозначно, под «ACCOUNT» может быть накручена такая огромная VIEW
Разработчику особо дела нет как там эти поля называются, БД может проверить что должен существовать FK в единственном числе и одним концом смотреть на PK и, если уж на то пошло, то IDE может подсказать разрабу что это за FK и какие ключи там есть.
Так что в целом мысль вполне годная.
Если я создал FK, то и соединять в 99% случаев буду по нему.
Лучше бы БД варнинги выдавала на JOIN'ы без FK.
Хотя тут тоже не однозначно, под «ACCOUNT» может быть накручена такая огромная VIEW
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Как думать на SQL?