Mysql LEFT JOIN примеры запросов, схемы, объяснение


Содержание

Примеры SQL запросов к базе данных MySQL

Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP

1. Самые простые SQL запросы

1. Выведет список ВСЕХ баз.

2. Выведет список ВСЕХ таблиц в Базе Данных base_name.

2. Простые SELECT (выбрать) запросы к базе данных MySQL

SELECT – запрос, который выбирает уже существующие данные из БД. Для выбора можно указывать определённые параметры выбора. Например, суть запроса русским языком звучит так — ВЫБРАТЬ такие-то колонки ИЗ такой-то таблицы ГДЕ параметр такой-то колонки равен значению.

1. Выбирает ВСЕ данные в таблице tbl_name.

2. Выведет количество записей в таблице tbl_name.

3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.

4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.

5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.

6. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы users и сортирует их (ORDER BY) по полю id в порядке возрастания, лимит (LIMIT) первые 5 записей.

7. Выбирает все записи из таблицы users, где поле fname соответствует значению Gena.

8. Выбирает все записи из таблицы users, где значение поля fname начинается с Ge.

9. Выбирает все записи из таблицы users, где fname заканчивается на na, и упорядочивает записи в порядке возрастания значения id.

10. Выбирает все данные из колонок fname, lname из таблице users.

Внимание! Старайтесь указывать конкретные колонки (как в примере 10). Это важно для того, чтобы запросы обрабатывались намного быстрее!

11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения

12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.

13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users. То есть если у Вас в таблице самое большее значение age(с англ. возраст) равно 55, то результатом запроса будет 55.

14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.

15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.

3. Простые INSERT (новая запись) запросы

INSERT – запрос, который позволяет ПЕРВОНАЧАЛЬНО вставить запись в БД. То есть создаёт НОВУЮ запись (строчку) в БД.

1. Делает новую запись в таблице users, в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.

4. Простые UPDATE запросы к базе данных MySQL

UPDATE – запрос, который позволяет ПЕРЕЗАПИСАТЬ значения полей или ДОПИСАТЬ что-то в уже существующей строке в БД. Например, есть готовая строка, но в ней нужно перезаписать параметр возраста, так как он изменился со временем.

1. В таблице users ГДЕ id равно 3 значение поля age становится 18.


2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.

5. Простые DELETE (удалить запись) запросы к базе данных MySQL

DELETE – запрос, который удаляет строку из таблицы.

1. Удаляет строку из таблицы users ГДЕ id равен 10.

6. Простые DROP (удалить таблицу) запросы к базе данных MySQL

DROP – запрос, который удаляет таблицу.

1. Удаляет целиком таблицу tbl_name.

7. Сложные запросы к базе данных MySQL

Любопытные запросы, которые могут пригодиться даже опытным пользователям

Данный сложный запрос ВЫБИРАЕТ колонки id,name,country В ТАБЛИЦАХ users,admins ГДЕ registration_date (дата) не старше 14 дней И activation НЕ РАВНО , СОРТИРОВАТЬ по registration_date в обратном порядке (новое в начале).

Выше указан пример так называемого запроса в запросе в SQL. Обновить возраст среди пользователей на 18+, где пол — мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных — они будут прорабатываться быстрее.

8. Запросы к базе данных MySQL и PHP

В MySQL запросы в PHP странице можно вставлять переменные в качестве сравниваемых и тп значений. Пара примеров

1. Выбирает все записи из таблицы users, где поле fname соответствует значению переменной $name.

2. В таблице users ГДЕ id равно 3 значение поля age изменяется на значение переменной $age.

Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!

Как присоединить три таблицы в SQL запросе – Пример в MySQL

Главное меню » Базы данных » Учебное пособие по SQL » Как присоединить три таблицы в SQL запросе – Пример в MySQL

В случае присоединения трех таблиц, первая относится к таблице 2, а затем таблица 2 относится к таблице 3. Если вы посмотрите внимательно, то вы обнаружите , что таблица 2 представляет собой присоединенную таблицу, которая содержит первичный ключ из обеих таблиц 1 и 2. Как мы сказали, это может быть очень запутанным, чтобы понять объединение трех или более таблиц.

Мы обнаружили, что понимание отношение таблиц в качестве первичного ключа и внешнего ключа помогает облегчить задачу.

SQL Join также является очень популярной темой в SQL и там всегда были некоторые вопросы из соединений, как разница между INNER и OUTER JOIN, например SQL – запрос с JOIN Employee Department и разница между LEFT и RIGHT OUTER JOIN и т.д. Короче говоря это одна из самых важных тем в SQL как из опыта так и из точки зрения цели.

Единственный способ освоить SQL JOIN, это сделать как можно больше упражнений, насколько это возможно. Если бы вы могли решить большинство головоломок SQL из классической книги Джо Селко, SQL Puzzles and Answers, 2nd edition, вы были бы более уверены в работе с SQL JOIN, хоть это быть две, три или четыре таблицы.

Объединение трех таблиц, синтаксис в SQL

Вот общий синтаксис запроса SQL, чтобы присоединить три или более таблиц. Этот SQL-запрос должен работать во всех основных баз данных, например в базе данных MySQL, Oracle, Microsoft SQLServer, Sybase и PostgreSQL:

Илон Маск рекомендует:  Оперативная память эпизод iii управление памятью в приложениях

Мы сначала присоединим таблице 2 к таблице 1, которые создадут временную таблицу с комбинированными данными из table1 и table2, а затем присоединим к Table3. Эта формула может быть распространена на более чем 3 -х таблиц в N таблиц, Вам просто нужно убедиться, что SQL – запрос должен иметь N-1 join, чтобы присоединить N таблиц. Как для объединения двух таблиц мы требуем 1 join а для присоединения 3 таблиц нам нужно 2 join.

Вот хорошая схема, которая хорошо показывает, как применять различные типы присоединений, например как работают в SQL inner, left outer, right outer и cross joins:

SQL запрос по присоединению трех таблиц в MySQL

Для того, чтобы лучше понять присоединение 3 таблицы в SQL запросе, давайте рассмотрим пример. Рассмотрим популярный пример Employee и Department. В нашем случае мы использовали таблицу ссылок под названием Register, который связывает или имеет отношение Employee для Department. Первичный ключ таблицы Employee (emp_id) является внешним ключом в Register и аналогичным образом, первичный ключ таблицы Department (dept_id) является внешним ключом в таблице Register.

Для того , чтобы написать запрос SQL для печати имя сотрудника и название отдела мы должны присоединиться к трем таблицам. Первое присоединение Employee и Register и создают временную таблицу, с колонкой dept_id. Теперь второе присоединение таблицы Department к этой временной таблицы по колонке dept_id, чтобы получить желаемый результат. Вот полный SELECT, пример SQL – запроса, чтобы присоединиться к 3 таблицам, и она может быть расширена, чтобы присоединиться к более чем 3 или N таблицам.


Если вы хотите понять это лучше, попытайтесь объединить таблицы шаг за шагом. Таким образом, вместо того, чтобы присоединиться 3 таблицы за один раз, сначала соединить 2 таблицы и посмотреть, как будет выглядеть таблица результатов. Это все о том, как присоединить три таблицы в одном запросе SQL в реляционной базе данных. Кстати, в этом примере SQL JOIN, мы использовали ANSI SQL, и он будет работать в другой реляционной базы данных, а также, Oracle, SQL Server, Sybase, PostgreSQL и т.д. Дайте нам знать, если вы сталкивались с какой – либо проблемой во время объединения 3 таблицы запросом JOIN в любой другой базе данных.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Left Join по условию(4 таблицы) CASE

Вопрос по SQL. Есть 4 таблицы со след структурой:

Таблица main_table содержит все id, которые присутствуют в таблицах t1,t2,t3, в этих 3х таблицах id не пересекаются. Необходимо объединить таблицы t1,t2,t3 с таблицей main_table в зависимости от типа(main_table.type).

Я написал следующий вариант

В этом случае если в таблицах много столбцов, будет очень длинный запрос и много проверок.

Можно ли в выбрать сразу все столбцы или перечислить без повторных проверок. Или подскажите вариант более лаконичный, а то я уверен что я извращение сотворил.

Есть еще вот такой вариант:

в данном случае работает так как надо, вот только необходимо исключить null значения из двух остальных таблиц

В общем смысл я думаю понятен, нужно объединить 2 таблицы, первая всегда одна и та же, вторая выбирается из 3 в зависимости от значения поля тип в первой таблице.

Залил пример на sqlfiddle http://sqlfiddle.com/#!2/03869/1 со следующими данными:

со следующим запросом:

Необходимы только непустые значения, т.е. выбор на каждой строке главной таблицы из нужной присоединенной таблицы по значению type из главной.

Конечный результат должен быть такого вида:

Скорее всего я просто не до конца понимаю работу самого SQL для последовательности выборки такого вида и хочу что бы при выборе строки из main_table, к примеру с типом type_1 SQL даже не смотрел в таблицы t2,t3 не затрачивая на это ресурсы пусть и минимальные, а работал лишь с таблицей t1. Постараюсь сейчас привести полный пример с CASE и COALESCE которые отобразят желаемый результат.

Оптимизация запроса MySQL LEFT JOIN для простой схемы из трех таблиц?

Я написал систему запросов на работу на PHP с базой данных MySQL, и у меня возникла проблема с медленным запросом.

Моя схема (упрощенная) выглядит следующим образом:

tbl_job
job_id
job_desc requestter_user_id

tbl_user
user_id
user_name

tbl_workermap
workermap_id
job_id
worker_user_id

Таблица, содержащая задания, пользовательская таблица возможных работников и таблица для сопоставления работников с заданиями. Работа может иметь одного или нескольких работников, работник может иметь одну или несколько работ.

tbl_user содержит как пользователей, которые запрашивают работу, так и тех, кто работает с заданиями, поэтому идентификаторы пользователей хранятся в файле worker_user_id в tbl_workermap и requestter_user_id в tbl_job

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

Система некоторое время использовалась, и у меня сейчас около 8000 Записи в tbl_job и 7000 в tbl_workermap и получение всех результатов занимает более 4 секунд. Запрос EXPLAIN показывает соединение tbl_workermap, возвращающее около 7000 строк и «Использование где; Использование буфера соединения (блочный вложенный цикл)».

Есть ли что-нибудь, что я могу сделать, чтобы ускорить это?

РЕДАКТИРОВАТЬ: добавить информацию о таблице
Я бы упростил объяснение, но вот фактическая структура таблицы , Соединений больше, но единственной проблемной является tbl_workermap:

Сергей Червач


блог о том, что я знаю, умею, с чем согласен

Наглядное объяснение принципа объединения таблиц в MySQL

При разработке веб-проектов с участием базы данных нам часто необходимо в запросах объединять таблицы базы, чтобы получить необходимые данные.

В статье рассмотрены принципы объединений таблиц и, для наглядности, они изображены на круговых диаграммах (диаграммах Венна). Первоисточником статьи является статья Джеффа Этвуда, но я (Kreker, в смысле), её перевел, подрихтовал и добавил неописанные типы объединений для MySQL (изначально статья была для другой СУБД).

Предупреждение: в статье в условии объединения таблиц используется как ON, так и USING, для разнообразия. Напоминаю, что если столбцы, по которым происходит объединение, имеют одинаковые имена, то необходимо использовать USING (`имя_столбца`), в противном случае используется ON `TableA`.`имя_столбца_из_TableA` = `TableB`.`имя_столбца_из_TableB`

Итак, предположим, что у нас есть два стола. Стол А (TableA) слева, а стол Б (TableB) справа. Мы заселим каждый четырьмя персонажами, имена которых могут присутствовать на обеих столах.

TableA TableB
id name id name
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

В СУБД MySQL существуют следующие операторы объединения:

INNER JOIN

— INNER JOIN производит выборку записей, которые только существуют в TableA и TableB одновременно.
— CROSS JOIN — это эквивалент INNER JOIN.
— INNER JOIN можно заменить условием объединения в WHERE.

TableA TableB
id name id name
1 Pirate 2 Pirate
3 Ninja 4 Ninja

FULL OUTER JOIN*

FULL OUTER JOIN производит выборку всех записей из TableA и TableB, вне зависимости есть ли соответствующая запись в соседней таблице. Если таковой нет, то недостающая сторона будет содержать пустой указатель и результатом будет выводится NULL.

TableA TableB
id name id name
1 Pirate 2 Pirate
2 Monkey NULL NULL
3 Ninja 4 Ninja
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader

В MySQL нечто похожее можно получить запросом:

TableA TableB
id name id name
1 Pirate 2 Pirate
2 Monkey NULL NULL
3 Ninja 4 Ninja
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader

Чтобы произвести выборку уникальных записей из двух таблиц (значения одной таблицы отсутствуют в другой), мы воспользуемся тем же FULL OUTER JOIN, указав, что NULL может быть как в результате одной таблицы, так и в результате другой.

TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader

В MySQL нечто похожее можно получить запросом:

TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader

LEFT JOIN

LEFT OUTER JOIN (LEFT JOIN) указывает, что левая таблица управляющая (в нашем случае TableA) и производит из нее полную выборку, осуществляя поиск соответствующих записей в таблице TableB. Если таких соответствий не найдено, то база вернет пустой указатель — NULL. Указание OUTER — не обязательно.

TableA TableB
id name id name
1 Pirate 2 Pirate
2 Monkey NULL NULL
3 Ninja 4 Ninja
4 Spaghetti NULL NULL

Чтобы произвести выборку записей из таблицы TableA, которых не существует в таблице TableB, мы выполняем LEFT JOIN, но затем из результата исключаем записи, которые не хотим видеть, путем указания, что TableB.id является нулем (указывая, что записи нет в таблице TableB).

TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL

RIGHT JOIN

RIGHT JOIN выполняет те же самые функции, что и LEFT JOIN, за исключением того, что правая таблица будет прочитана первой. Таким образом, если в запросах из предыдущей главы LEFT заменить на RIGHT, то таблица результатов, грубо говоря, отразится по вертикали. То есть, в результате вместо значений TableA будут записи TableB и наоборот.

NATURAL JOIN

Суть этой конструкции в том, что база сама выбирает, по каким столбцам сравнивать и объединять таблицы. А выбор этот падает на столбцы с одинаковыми именами. В этом кроется засада &mdash база может выбрать совершенно не те столбцы для объединения и запрос будет работать совершенно не так, как вы предполагали.


В этом случае СУБД выбирает для объединения таблиц столбцы id и name, так как они присутствуют в обеих таблицах и превращает исходный запрос в запрос следующего вида:

Но так как у нас нет записей с одинаковым id и name одновременно в обеих таблицах, то запрос вернет пустой результат.

Если же сделать управляющей левую таблицу и изменить запрос:

Такой запрос приводится СУБД к следующему:

То результат будет таким:

TableA TableB
id name id name
1 Pirate NULL NULL
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL

Происходит это так: так как левая таблица управляющая, то она читается первой и полностью выбирается, независимо от правой таблицы; когда начинается поиск соответствующих записей в правой таблице, то СУБД не находит ни одной записи, которая была бы идентична по name и id одновременно, поэтому возвращаются пустые указатели.

Для более подробного понимания работы NATURAL JOIN изменим name в первой записи в таблице TableB на Pirate.

Таким образом, у нас получилось:

TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

А теперь выполним те же запросы с NATURAL JOIN, что использованы выше.

id name
1 Pirate

Так как теперь запись с одинаковым id и name присутствует в обеих таблицах, то она и будет выведена.

TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL

Таким образом, база сама выбирает по каким столбцам и каким способом объединять таблицы. С одной стороны это весьма удобно, с другой — несет неразбериху: где гарантия того, что столбцы с одинаковыми именами в таблицах будут именно ключевыми и предназначены для объединения? NATURAL JOIN ухудшает читаемость кода, так как разработчик не сможет по запросу определить, как объединяются таблицы. Поэтому, обращая внимание на такие факторы, NATURAL JOIN использовать не рекомендуется .

STRAIGHT JOIN

STRAIGHT JOIN выполняет те же функции, что и обычный INNER JOIN, за исключением того, что левая таблица читается раньше правой.

TableA TableB
name id id
Pirate 1 1
Pirate 1 2
Ninja 3 4
TableA TableB
name id id
Pirate 1 1
Pirate 2 1
Ninja 4 3

Декартова выборка

Если при объединении таблиц не указать условие объединения через ON или USING, то база произведет так называемую Декартову выборку, когда значению одной таблицы приравнивается каждое значение другой. Таким образом, СУБД, в нашем случае, возвращает 4×4 = 16 строк.

Разбираемся с mySQL JOIN, визуальное представление.

На протяжении немалого времени, в начале своей карьеры веб-разработчика, я работал с базой данный как умел, а умел я не многое. Составлял простые примитивные запросы, а порою даже вставлял запросы в циклы. На тот момент мне к сожалению не попалась в руки правильная книжка по mySQL и учить приходилось методом проб и ошибок. Множество статей в интернете как-то не сразу донесли до меня замечательный mySQL запрос — JOIN.
В этой публикации я расскажу о всех возможных вариантах работы с JOIN и более того, представлю принцип работы каждой команды — визуально.

Рассматривать мы будем:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN

  4. OUTER JOIN
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. OUTER JOIN EXCLUDING INNER JOIN

Отдельно стоит отметить пункты 5,6 и 7. На самом деле эти запросы не соединяют две таблицы, а наоборот исключают из одной таблицы столбцы присутствующие в другой. На деле это может оказать очень полезным.

Inner JOIN

Один из самых распространенных запросов, встречается предельно часто. Этот запрос вернет все записи из левой таблицы (таб. А) и записи из (таб. В), но при этом возвращены будут только совпадающие столбцы.

Left JOIN

Данный запрос вернет все столбцы из левой таблицы (таб. А), а также все столбцы из правой таблицы (таб. В) но только которые совпадают со столбцами из левой таблицы.

Right JOIN

Аналогичен предыдущему запросу, но уже вернет все столбцы из правой таблицы (таб. В), а также все столбцы из левой таблицы (таб. А) которые совпадают со столбцами из правой таблицы.

Outer JOIN

Часто данный запрос записывают как FULL OUTER JOIN или FULL JOIN, все вариации выполняют одно действие, а именно возвращают все столбцы из обоих таблиц, при этом совпадающие столбцы будут перекрыты столбцами из левой таблицы.

Left Excluding JOIN

Этот запрос вернет все столбцы из левой таблицы (таб. А), которые не совпадают со столбцами из правой таблицы (таб. В).

Right Excluding JOIN

Почти как и предыдущий, но этот запрос вернет все столбцы из правой таблицы (таб. В), которые не совпадают со столбцами из левой таблицы (таб. А).

Outer Excluding JOIN

Этот запрос вернет все записи из левой и правой таблицы, но при этом исключит совпадающие столбцы.

Надеюсь этот пост пригодится многим начинающий веб-разработчикам, ведь запомнить сразу все отличия запросов друг от друга очень сложно, ввиду чего многие пользуются одним, двумя типами запросов и забывают про силу и мощь mySQL.

В качестве шпаргалки приведу изображение со всеми типами JOIN запросов и кратким примером кода.

Описание оператора JOIN

Часто вижу на форуме проблемы связанные с объединением в одном запросе данных из нескольких таблиц. Например:

  • как одним запросом достать список категорий, и количество статей в каждой категории?
  • как одним запросом достать два уровня каталога с указанием родителя каждой категории?
  • как одним запросом достать название товара и описание к нему, если они лежать в разных таблицах?

В этом посте мы научимся все это делать, и многое другое.

Введение
Для начала нам нужен некий набор таблиц, на которых мы будем ставить эксперименты. Я выбрал четыре до безобразия простых таблицы:

Обратите внимание, цены и описания установлены не на все товары.

В зависимости от наших запросов, мы будем использовать три способа соединения таблиц:

  • INNER (CROSS) JOIN — внутреннее (перекрёстное) объединение
  • LEFT JOIN — левостороннее внешнее объединение
  • RIGHT JOIN — правостороннее внешнее объединение

INNER (CROSS) JOIN
Это объединение извлекает строки, которые обязательно присутствуют в объединяемых таблицах.
Без указания условий отбора, выборка вернет декартово произведение, где каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы:

Такое соединение таблиц редко нужно, чаще требуется выбрать только сопоставленные записи. Делается это установкой условия отбора, используя ON или USING

Запрос вернул только те записи, которые имеют одинаковые идентификаторы в обеих таблицах.
Мы использовали USING, потому что в обоих таблицах ключевой столбец имеет одно и тоже имя — id.

SQL — LEFT JOIN

Дата публикации: 2020-12-05

От автора: LEFT JOIN SQL возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений. Это означает, что, если условие ON соответствует 0 (нолю) записей в правой таблице; объединение все равно вернет строку в результат, но с NULL в каждом столбце из правой таблицы.

Это означает, что LEFT JOIN возвращает все значения из левой таблицы, а также соответствующие значения из правой таблицы или NULL в случае отсутствия соответствующего предиката объединения.


Оптимизация запроса MySQL LEFT JOIN для простой схемы из трех таблиц?

Я написал систему запросов на работу на PHP с базой данных MySQL, и у меня возникла проблема с медленным запросом.

Моя схема (упрощенная) выглядит следующим образом:

tbl_job
job_id
job_desc requestter_user_id

tbl_user
Идентификатор пользователя
user_name

tbl_workermap
workermap_id
job_id
worker_user_id

Таблица, содержащая задания, пользовательская таблица для возможных работников и таблица для сопоставления работников с заданиями. Работа может иметь одного или нескольких работников, работник может иметь одну или несколько работ.

tbl_user содержит как пользователей, которые запрашивают работу, так и тех, кто работает над заданиями, поэтому идентификаторы пользователей хранятся в файле worker_user_id в tbl_workermap и requestter_user_id в tbl_job

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

Система использовалась некоторое время, и теперь у меня есть около 8000 записей в tbl_job и 7000 в tbl_workermap, и для получения всех результатов требуется более 4 секунд. Запрос EXPLAIN показывает соединение tbl_workermap, возвращающее около 7000 строк и «Использование где; Использование буфера соединения (блочный вложенный цикл)».

Что я могу сделать, чтобы ускорить это?

РЕДАКТИРОВАТЬ: добавить информацию таблицы
Я бы упростил объяснение, но здесь фактическая структура таблицы. Есть больше объединений, но единственной проблемной является tbl_workermap:

MySQL отличие LEFT от INNER

Очень подробно про джоины написано тут

Однако, там описаны примеры работы джоина двух таблиц, но не описано что случится если будет участвовать третья таблица, вот об этом мы и поговорим.

Но для начала быстрая напоминалка о LEFT и INNER джоинах.

Допустим есть 2 таблицы alpha и beta.

alpha

id nameA
1 mama
2 papa
3 ya

beta

id nameB
2 Pirate
3 Monkey

CREATE TABLE alpha (
id int(11) DEFAULT NULL,
nameA varchar(50) DEFAULT NULL );
INSERT INTO alpha(id, nameA) VALUES (1, ‘mama’), (2, ‘papa’), (3, ‘ya’);

CREATE TABLE beta (
id int(11) DEFAULT NULL,
nameB varchar(50) DEFAULT NULL );
INSERT INTO beta(id, nameB) VALUES (2, ‘Pirate’), (3, ‘Monkey’);

Запрос c LEFT JOIN вернет все 3 строки из таблицы alpha, объединив их по id с таблицей beta, а недостающие значения заполнит NULL:

SELECT * FROM alpha LEFT JOIN beta USING ( >SELECT * FROM alpha LEFT JOIN beta ON beta. >

пояснение: показать строки таблицы alpha
и подходящие этим строкам данные

Запрос c INNER JOIN вернет 2 строки c id которые есть в обеих таблицах:

SELECT * FROM alpha INNER JOIN beta ON beta. >

id nameA nameB
1 mama NULL
2 papa Pirate
3 ya Monkey
id nameA nameB
2 papa Pirate
3 ya Monkey


пояснение: показать строки таблиц alpha и beta
по условию совпадению данных в этих строках

Теперь добавим еще одну строку с уже существующим id:

3 — INSERT INTO beta ( >3 , ‘ Ship ‘);

INNER JOIN:

id nameA nameB
2 papa Pirate
3 ya Monkey
3 ya Ship

LEFT JOIN:

id nameA nameB
1 mama NULL
2 papa Pirate
3 ya Monkey
3 ya Ship

Как мы видим данные из таблицы alfa появились в третьей строке, лично мое мнение — третья строка не должна появляться при INNER JOIN, такое должно быть только при FULL OUTER JOIN.

Если мы сделаем INNER JOIN еще одной таблицы, то чтобы понять что произойдет, просто представьте, что у Вас уже есть таблица из двух таблиц и Вы просто добавляете к ней еще одну таблицу.

gamma

id nameG
1 lazer

CREATE TABLE gamma (
id int(11) DEFAULT NULL,
nameG varchar(50) DEFAULT NULL );
INSERT INTO gamma(id, nameG) VALUES (1, ‘lazer’);

В итоге, если мы сделаем запрос:

SELECT *
FROM alpha
INNER JOIN beta ON beta. > INNER JOIN gamma ON gamma . >

То в ответе получим ноль строк, потому что:

    в предыдущем запросе мы получили beta. >gamma . >gamma . >gamma . >gamma нет строк с >Различие указания вариантов условий при LEFT JOIN

Создадим еще одну таблицу omega:

CREATE TABLE omega (
id int(11) DEFAULT NULL,
z int(11) DEFAULT NULL,
nameO varchar(50) DEFAULT NULL );

INSERT INTO omega(id, z, nameO) VALUES
(1, 1, ‘N1’),(2, 1, ‘N2’),(3, 2, ‘N3’);

А теперь выполним 2 казалось бы одинаковых запроса:

SELECT * FROM alpha
LEFT JOIN omega ON omega. >WHERE omega.z = 1

SELECT * FROM alpha
LEFT JOIN omega ON omega. >AND omega.z = 1

id z nameO
1 1 N1
2 1 N2
3 2 N3
id nameA id1 z nameO
1 mama 1 1 N1
2 papa 2 1 N2
id nameA id1 z nameO
1 mama 1 1 N1
2 papa 2 1 N2
3 ya null null null
WHERE указывает какие строки показать
(срабатывает после JOIN-a )
AND указывает какие строки присоединить
(срабатывает во время JOIN-a)

Как видите, условия вроде идентичны, но результат получается совершенно разным.

p.s. имейте ввиду, что в этой статье столбец id уникален в рамках своей таблицы. Т.е. если в Ваших таблицах столбец id не уникален, то результаты запросов будут совершенно иными.

JOIN-соединения

Возможно ниже изложенное — тема отдельной статьи, но хочется поделиться информацией о всех операциях горизонтального соединения данных.

Есть пять типов соединения:

  1. JOIN – левая_таблица JOIN правая_таблица ON условия_соединения
  2. LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения
  3. RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения
  4. FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения
  5. CROSS JOIN – левая_таблица CROSS JOIN правая_таблица
Краткий синтаксис Полный синтаксис Описание (Это не всегда всем сразу понятно. Так что, если не понятно, то просто вернитесь сюда после рассмотрения примеров.)
JOIN INNER JOIN Из строк левой_таблицы и правой_таблицы объединяются и возвращаются только те строки, по которым выполняются условия_соединения.
LEFT JOIN LEFT OUTER JOIN Возвращаются все строки левой_таблицы (ключевое слово LEFT). Данными правой_таблицы дополняются только те строки левой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк правой_таблицы вставляются NULL-значения.
RIGHT JOIN RIGHT OUTER JOIN Возвращаются все строки правой_таблицы (ключевое слово RIGHT). Данными левой_таблицы дополняются только те строки правой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк левой_таблицы вставляются NULL-значения.
FULL JOIN FULL OUTER JOIN Возвращаются все строки левой_таблицы и правой_таблицы. Если для строк левой_таблицы и правой_таблицы выполняются условия_соединения, то они объединяются в одну строку. Для строк, для которых не выполняются условия_соединения, NULL-значения вставляются на место левой_таблицы, либо на место правой_таблицы, в зависимости от того данных какой таблицы в строке не имеется.
CROSS JOIN Объединение каждой строки левой_таблицы со всеми строками правой_таблицы. Этот вид соединения иногда называют декартовым произведением.

Как видно из таблицы полный синтаксис от краткого отличается только наличием слов INNER или OUTER.

Лично я всегда при написании запросов использую только краткий синтаксис, по той причине:

  1. Это короче и не засоряет запрос лишними словами;
  2. По словам LEFT, RIGHT, FULL и CROSS и так понятно о каком соединении идет речь, так же и в случае просто JOIN;
  3. Считаю слова INNER и OUTER в данном случае ненужными рудиментами, которые больше путают начинающих.

А на последок, диаграмма, поясняющая работу JOIN-ов:

Илон Маск рекомендует:  Что такое код settimer
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL