Comments 166
members.firstname, members.lastname
Варианты:
1) надо добавить еще одно поле и я не помню, как оно точно называется. Набираю короткий алиас, и список полей таблицы у меня перед глазами. Нет алиаса — я вынужден идти смотреть описание таблицы.
2) надо приджойнить таблицу в которой есть совпадающее поле — тут же становится необходимо завести алиас
3) надо просто понять, что делает запрос, а в нем идет соединение таблиц без алиасов и не ясно, какое поле к какой таблице относится.
В общем я считаю, что обязательное использование алиасов — хороший стиль.
- Таблицы, к сожалению, принято называть во множественном числе. Но
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
Есть запросы на тысячу с лишним строк!!!
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;
Все те же претензии «на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить» — справедливы для того же Си или любого другого языка. Английские термины в качестве ключевых слов — облегчают их мнемоническое запоминание, но не более того. Они не являются эквивалентом конструкций естественного языка.
FROM borrowings, members, booksЭто ужасно. Настолько же ужасно, как
FROM borrowings
INNER JOIN members
INNER JOIN books
ON members.memberid=borrowings.memberid
ON books.bookid=borrowings.bookid
(а MSSQL такое позволяет).Так же как по секциям расположены описание полей на выходе, сортировка, группировка.
Удивительно что мой пример у вас вызвал отторжение, хотя по структуре он точно такой же, как и первоначальный.
В монстро-запросе будет другая ситуация. Если планировщик базы хорошо оптимизирует подобное — возможно имеет смысл разбивать сложный запрос на более простые и оформлять их в виде функций или WITH выражений, хотя это не всегда полезно.
Я не претендую на истину в последней инстанции и всегда можно найти обратный пример. Просто привёл пример для сравнения, как можно упростить простой запрос и сделать его легко читаемым для человека. Фанатизм же до добра не доведёт.
В моём же случае я сразу вижу как две таблицы об'единяются (from и первый join), осознаю их смысловую нагрузку, лишнее я уже отбросил и предполагаю об'ём результата. Если в join'ах ещё есть таблицы, то к результату предыдущего шага добавляю ещё одну таблицу и проделываю то же самое дальше.
FROM borrowings
INNER JOIN members
INNER JOIN books
ON members.memberid=borrowings.memberid
ON books.bookid=borrowings.bookid
не могу не согласиться — здесь просто месиво операторов вместо лаконичного и визуально структурированного перечисления.
FROM borrowings
INNER JOIN members USING (memberid)
INNER JOIN books USING (bookid)
Но, имхо, это тоже сложнее читается, чем лаконичная секция FROM и подробная WHERE.
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;
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.
В первом случае это список всех взятых книг, второе внутренне соединение убирает не взятые книги.
Во втором это список всех книг без исключения и информация о их взятии исключая те взятия у которых не определены читатели.
Т.е. в Вашем варианте Вы сначала CROSS JOIN'ите все 3 таблицы, а потом фильтруете получившееся месиво с помощью WHERE.
(Да, DBMS оптимизируют CROSS JOIN + WHERE до того же, что получается в результате INNER JOIN… ON. Только вот мои мозги не оптимизируют. В смысле, что человеку понимать INNER JOIN… ON гораздо проще.)
Я не знаю, возможно это отпечаток какой-то привычки, но не могу подтвердить описанного эффекта — моё сознание просто воспринимает «берём то, что нам нужно». Я в реальной жизни не «джойню» в уме сущности, чтобы осознать такие вещи как «все читатели, которые держат хоть одну книгу, которой в наличии больше среднего». Само описание на русском языке в данном случае строится через взятие полного множества всех читателей и последовательного исключения из него через перечисление правил — это, прямо скажем, легко и естественно. И это хорошо ложится на структуру 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 это с радостью позволит. Но можно адаптировать для человека — у нас есть для этого достаточно свободы.
Одна проблема: человеки сразу займутся сравнением вкуса фломастеров и этого у нас не отнять :-)
Выбираю я всегда из одной (основной) таблицы:
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'ов строк. Хотя, может быть, это действительно фломастеры.
Есть несколько причин использовать «новый» стиль (если стандарта по вашему мнению не достаточно) могу привести еще несколько аргументов в пользу нового стиля. Во-первых разделения объединения и фильтраци в разные блоки. Во-вторых в случае если вы забыли указать условие объединения старый стиль приведет к расчету огроного объема данных и неверного результата (который может быть не так легко обнаружить), в то время как новый выдаст синтаксическую ошибку (http://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/). Можно продолжить гуглить и находить «за» и «против», а можно просто перейти на использование стандартов и выбросить дурное из головы. Ваш код могут читать люди которые родились после введения стандарта SQL-92 и ваш код будет выглядеть для них так, как будто его писали мамонты.
ЕМНИП, 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
не засунешь.
SELECT foo, длинное-длинное-выражение as bar
GROUP BY foo
HAVING bar > 42
И вообще, авторов в отдельную таблицу бы. И многие ко многим авторы-книги.
А то мало ли, вдруг кто-то напишет книгу в соавторстве с кем-то?
Что потом делать с запросом
SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';
А еще таблицу поступлений книг, и триггеры на обновление поля stock на приход/уход.
Вот тогда уже будет интереснее.
И многие ко многим авторы-книги.
Как-то писал небольшую соцсеть, и тоже пришлось сделать «многие ко многим», чтобы у статьи могло быть несколько авторов, а у авторов несколько (или много) статей. Так что пожелание весьма правильное.
member_id, book_id, first_name, last_name
читается легче, чем
memberid, bookid, firstname, lastname
Это кажется незначительным различием в простом запросе. Но когда запрос сложный и нужно посидеть, «распарсить» его и понять — каждая подобная мелочь сильно облегчает работу.
По поводу конструкции:
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
Так что лучше избегать подобных конструкций и выносить их в секцию с JOIN'ами как, например, показано в комментарии выше.
Как показала реальная практика, результат оптимизации и скорость выполнения запроса в большей степени зависели от наличия индексов, чем от формы записи запроса.
И при некоторых комбинациях индексов наличие подзапроса во WHERE существенно ускорало выполнение запроса, в то время как использование JOIN приводило к игнорированию индексов, что приводило к замедлению.
Например, при наличии индексов по полям, используемым в WHERE для вложенного запроса и отсутствии индексов для таблицы из FROM, если не путаю за давностью лет, конечно.
Отсюда, кстати, вывод. SQL-оводу имеет смысл знать об индексах, используемых в его СУБД и их особенностях.
Если в подзапросе есть дубликаты — то JOIN продублирует строки основного запроса, в отличии от IN. Избавляться от дублей придется с помощью группировки — а она может сказаться на оптимальности плана, поскольку требует упорядоченности.
MS SQL и сейчас кушает такие подзапросы с легкостью (более того, в плане выполнения 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 не оптимизируется или влечёт проблемы — есть ещё вариант с джойном!!!
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
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? Но такой подход боюсь только собьет с толку новичка.
Надеялся, что хоть тут мне толково объяснят что такое INNER JOIN и чем он отличается от JOIN, но, видимо, не судьба :)
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 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)
А вот по классификации, я не то что не согласен, просто это бесполезное занятие, вон 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 соответственно
2. Ну, скажу честно, я пока не вижу никакой причины классифицировать по-другому, чем у меня.
Ссылка из доки по 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) система равенств (в впротивоположность, например, «
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'у.
Магические «другие» 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
Вопрос поставлен некорректно. Тот перечень, что Вы приводили, — это не непересекающиеся классы.
Вот типы джоинов.
Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки, по которым джоинить» — бывает 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)
Хотя, «средний» разработчик, который всю дорогу ORM использовал в режиме «оно само», может и испугается.
SELECT поля FROM таблица…
DELETE FROM таблица…
UPDATE таблица (поля) SET…
INSERT INTO таблица (поля)…
Что-то примерно такое представляется мне более структурированным:
SELECT FROM таблица [FIELDS поля]…
DELETE IN таблица…
UPDATE IN таблица FIELDS поля=новые значения…
INSERT IN таблица [FIELDS поля[=новые значения]]… /*или по-старому*/
Что лучше:
1. Консистентный порядок
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 INTO table t (f1, f2) VALUES (v1, v2), (v3, v4)
меня устраивает, это уже отвечайте тому, кто писал про INSERT (и, да, синтаксис никак не связан с тем, что «создается набор значений», например, INSERT INTO users SET name='vasya', surname='pupkin' SET name='fedya', surname='petrov'
тоже прекрасно бы «создавало набор значений»).Чайники не понимают, что расчёт этих выражений происходит после группировки GROUP BY'ем, а не до.
https://habrahabr.ru/post/305926/#comment_9708148
При "
SELECT FROM … WHERE … GROUP BY … HAVING … EXPRESSIONS …
" бы таких проблем не было.Найди большую красную корзину. Нашёл? Возьми из корзины синий мячик.
Хочешь мячик? Возьми из его из красной корзины.
Т.е., если мы рассматриваем корзину как важный этап последовательности действий, мы указываем её в начале. Если мы рассматриваем корзину как незначительное уточнение, мы указываем её в конце. (Угадайте, чем является указание таблицы в 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:
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, то перечисляй/не перечисляй остальные — разницы уже нет). Но в остальном это требование очень логично.
В случае с GROUP BY: там вообще это требование не проверялось, например, можно было сделать
SELECT t.field2 FROM table1 t GROUP BY t.field1
, где field1 отнюдь не уникальный ключ.Что полный бред (хотя может с тех пор что-то изменилось).
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 -- неуникальное поле
Теперь понимаете?
Попробую предпроложить, что раз нет ни одной агрегирующей функции, то наверное хотите получить уникальные записи
Может тогда лучше так?
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 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. В принципе, с логической точки зрения допустимо, но в стандарте почему-то запрещено.
- Употребление вне GROUP BY полей, уникальность которых никак не гарантируется структурой запроса. Стандартом запрещено и очень правильно.
Т.е. в Вашем запросе ругается на u.username_clean излишне. В Вашем запросе будет ругаться на s.session_viewonline очень правильно.
Единственность всех остальных полей в 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 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
;
Которые, конечно, можно убрать DISTINCT'ом.
Но, по-моему, это не самое логичное решение.
(Хотя, не спорю, приемлемое; наравне с ручным перечислением в GROUP BY и подзапросом.)
Еще в голову пришло динамически собирать запрос.
Но вообще использовать SELECT * это как то не по феншую.
Тут вопрос (мой) не в том, как сделать, чтоб меньше писать.
Тут вопрос в том, что стандарт касательно 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% лучше.
P.S.: Если не секрет, почему маляр-штукатур?
Так что не спешите расслабляться раньше пенсии. А любимая работа в целом лучше хорошей работы.
А почему веб-разработка? C++ разве не поближе к Delphi будет?
Тут важнее не язык, а область его приложения. Дельфи всегда был заточен под БД и чуток под клиент-серверные технологии, из за чего остался живым именно в .NET. Я делал приложения для работы с базами данных управленческого учёта, аддоны для системы разработки технологической документации (с целью приведения интерфейса ко вменяемому уровню UX), до кучи программировал на полставки промышленные контроллеры в цехе спецстанков и систему сбора статистики со станков стороннего производителя. В общем, полноценная жизнь заводского программиста из спецназа местечкового значения АКА «Группа САПР».
В сухом остатке, помимо промышленных контроллеров (это были Мицубиси, а сейчас в тренде Сименс), фронтенд и базы данных.
Ушёл уже с должности руководителя группы (в мелкую фирму «главным» инженером) из-за того, что завод стагнировал и никому ничего было не надо. Бюджет отсутствовал («Вам же уже все программы купили ещё пять лет назад»), мы вырождались в группу документооборота электронных копий бумажных чертежей с двумя инженерами чтобы слать их по почте и класть на сервер и двумя студентами чтобы сканировать их (на планшетном сканере А3) и сшивать.
А в последнем марте я пробежался по работным сайтам, посмотрел, что мне близко из востребованного в нашей местности — и вот, учу веб-девелопмент, начиная с фронтенда (самое востребованное). Как в работе пауза, так и погружаюсь. Хотя мне, вероятно, был бы более по душе бекэнд, с его развитыми и хорошо структурированными фреймворками. Максимальное удовольствие от работы я получал именно тогда, когда строил универсальные инструменты для решения типовых задач или систематизировал и сериализовал подход к решению задач нетривиальных — и вот, пока я спал, оказалось, что это целый мир…
Такое вот «почему».
P.S. И хочется же в команду с развитыми понятиями о производстве ПО, где другим небезразличны вопросы организации процесса. В общем, в коллектив, который не является «толпой чёрных ящиков». Фриланса не хочется — это не только оставаться одиночкой (чего совсем не хочется), в пределе это наверняка выльется в узкую специализацию, отлаженный процесс и скуку смертную без дальнейшего развития. К тому же я наработался уже «строительным узбеком», и не хочется превращаться в «компьютерного индуса».
sqlfiddle.com
там можно попробовать запросы на разных БД: MySQL, Oracle, PostgesSQL, MSSQL, SQLite
P.S. а ещё есть
eval.in
Конкретно в запросе выше проблема не в русском языке — а в форматировании. Для начала — поставить отступы. Конструкцию "КОГДА… ТОГДА… ИНАЧЕ" лучше было бы записать в одну строку. Вместо капитанского "ВложенныйЗапрос" лучше бы написать что именно этот запрос делает. Или напротив, сократить псевдоним до "ВЗ", чтобы не отвлекал.
Нужно: узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех.
Получили — книги авторов, чьих книг в библиотеке больше 3.
Книга, экземпляров которой в библиотеке больше 3, одна. Это Who Will Cry When You Die?
Объяснять 100%-необходимо было бы, если бы он где-то использовал другие формы JOIN'ов: «LEFT [OUTER] JOIN», «RIGHT [OUTER] JOIN» или «FULL [OUTER] JOIN» — но такого у него нет.
Хотя сказать пару слов по этому поводу и использовать везде однотипный синтаксис (или JOIN, или INNER JOIN) было бы безусловно лучше.
Например, в схеме:
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:
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
И первый, и второй запросы верные, но выдают разные результаты.
SQL так-то любит чтоб всё было разжёвано.
Разработчику особо дела нет как там эти поля называются, БД может проверить что должен существовать FK в единственном числе и одним концом смотреть на PK и, если уж на то пошло, то IDE может подсказать разрабу что это за FK и какие ключи там есть.
Так что в целом мысль вполне годная.
Если я создал FK, то и соединять в 99% случаев буду по нему.
Лучше бы БД варнинги выдавала на JOIN'ы без FK.
Хотя тут тоже не однозначно, под «ACCOUNT» может быть накручена такая огромная VIEW
Как думать на SQL?