SQL урок 3. Запросы sql INNER JOIN (объединение таблиц)

На уроке будут рассмотрены запросы SQL INNER JOIN на объединение таблиц. Будут разобраны конкретные примеры запросов

Выборка из нескольких таблиц (неявная операция соединения)

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

Рассмотрим пример неявной операции соединения:

Пример: БД Компьютерные курсы.
Необходимо выбрать имена преподавателей, учебную группу и курс, на котором они преподают. Условием отбора должно являться одинаковое значение полей Учебная группа в таблицах Список и Группы.

✍ Решение:
 

1
2
3
4
5
SELECT DISTINCT группы.`Преподаватель` , 
   список.`Учебная группа` , список.`курс` 
FROM группы, список
WHERE группы.`Учебная группа` = список.`Учебная группа` 
AND курс <3

Пример: БД Компьютерный магазин. Найти номер и производителя компьютеров, имеющих цену менее 30000.

✍ Решение:
 

1
2
3
4
SELECT DISTINCT pc.Номер, Производитель
FROM  pc, product
WHERE pc.Номер = product.Номер
AND  Цена <30000

sql выборка из нескольких таблиц

Sql tables 1. БД Компьютерный магазин. Укажите производителя и скорость для тех компьютеров, которые имеют жесткий диск объемом не менее 1000 Гб.

Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В таком случае для таблицы потребуется псевдоним. Рассмотрим пример:

Пример: БД Компьютерные курсы. Вывести номера курсов студентов, имеющих одинаковый год рождения, но при этом курс у них разный.

✍ Решение:
 

1
2
3
4
5
SELECT DISTINCT A.`Курс` AS номер_курса1, B.`Курс` AS номер_курса2
FROM список AS A, список AS B
WHERE A.`Год рождения` = B.`Год рождения` 
AND A.Курс < B.Курс
LIMIT 0 , 30

Результат:

Здесь условие A.Курс < B.Курс используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой курса.
В общем случае можно использовать условие A.Курс <> B.Курс!

Пример: БД Компьютерный магазин. Вывести номера моделей компьютеров, имеющих одинаковые цены

✍ Решение:
 

1
2
3
4
SELECT DISTINCT A.Номер AS модель1, B.Номер AS модель2
FROM pc AS A, pc AS B
WHERE A.Цена = B.Цена
  AND A.Номер < B.Номер

sql переименование
Здесь условие A.Номер < B.Номер используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой номера:
sql запросы несколько таблиц

Sql tables 2. Вывести номера учителей (tid), которые ведут уроки по одинаковым курсам (таблица lessons)
Задание 3_1. БД "Компьютерные курсы".
1. Вывести все сведения из таблиц Личные данные и Список, совпадающие по полям Код и Код студента
2. Вывести фамилии, адреса и оценки по word из таблиц Личные данные и Список, совпадающие по полям Код и Код студента
Задание 3_2. БД "Компьютерные курсы".
Вывести курс и год рождения студентов, учащихся на одном курсе, но имеющих разный год рождения. При этом рассмотреть все курсы, кроме первого.

Результат:

Запросы sql INNER JOIN

В предложении FROM может использоваться явная операция соединения двух и более таблиц.

Разберем пример. Имеем две таблицы: teachers (учителя) и lessons (уроки):

teachers lessons
sql объединение таблиц sql объединение таблиц

Пример: Выбрать имена учителей и проведенные уроки по курсам, которые они ведут

✍ Решение:
 

 
Для этого необходимы обе таблицы:

SELECT t.name,t.code,l.course 
FROM teachers t 
INNER JOIN lessons l ON t.id=l.tid

запросы sql inner join
Результат:
sql inner join

В запросе буквы l и t являются псевдонимами таблиц lessons (l) и teachers (t).

Inner Join - это внутреннее объединение (JOIN - с англ. "объединение", ключевое слово INNER можно опустить).

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

запрос sql inner join

Важно: Inner Join - выбираются значения только в случае присутствия в обеих таблицах

Задание 3_3. БД "Компьютерные курсы". Указать фамилии студентов с оценкой 5 по курсу Word
Задание 3_4. БД "Компьютерные курсы". Вывести список фамилий и номеров телефонов студентов

Важно: Соединение таблиц может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебное слово INNER можно опускать, тогда при использовании просто слова JOIN имеется в виду внутреннее соединение (INNER)

Sql left inner join 1. БД Институт. Вывести фамилии всех преподавателей, названия и длительность курсов, которые они ведут (name, title, length) из таблиц teachers и courses. Использовать внутреннее объединение
Sql left inner join 2. БД Компьютерный магазин. Найти производителя, номер и цену каждого компьютера, имеющегося в базе данных.

Запросы sql OUTER JOIN

При использовании внутреннего объединения inner join выбираются только совпадающие данные из объединяемых таблиц. Для того чтобы получить данные, которые подходят по условию частично, необходимо использовать внешнее объединение.

OUTER JOIN - внешнее объединение, которое возвращает данные из обеих таблиц (совпадающие по условию объединения), ПЛЮС выборка дополнится оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.

Существует два типа внешнего объединения - LEFT OUTER JOIN ("внешней" таблицей будет находящаяся слева) и RIGHT OUTER JOIN ("внешней" таблицей будет находящаяся справа).

Рисунок относится к объединению типа Left Outer Join:
sql inner join left join

Важно: Ключевое слово OUTER можно опустить. Запись LEFT JOIN эквивалентна записи LEFT OUTER JOIN.

Пример БД Институт: Выбрать имена всех учителей и курсы, которые они ведут. Если учитель не прикреплен к курсу, его фамилию все равно необходимо вывести

✍ Решение:
 

SELECT t.name, t.code, l.course
FROM teachers t
LEFT OUTER JOIN lessons l ON t.id = l.tid

Результат:
left outer join sql

Важно: Таким образом, соединение LEFT JOIN означает, что помимо строк, для которых выполняется условие, в результирующий набор попадут все остальные строки из левой таблицы. При этом отсутствующие значения из правой таблицы будут заполнены NULL-значениями.

С тем же примером (выбрать имена учителей и курсы, которые они ведут) фильтрация по RIGHT OUTER JOIN вернет полный список уроков по курсам (правая таблица) и сопоставленных учителей. Но так как нет таких уроков, которые бы не соответствовали определенным учителям, то выборка будет состоять только из двух строк:

SELECT t.name, t.code, l.course
FROM teachers t
RIGHT OUTER JOIN lessons l ON t.id = l.tid

sql left outer join

Важно: Left Outer Join - после основной выборки, удовлетворяющей условиям, выбираются оставшиеся данные левой таблицы (внешней), которые по условию не подходят

Задание 3_5: БД Компьютерные курсы. Для выполнения задания необходимо добавить в таблицу Личные данные сведения для нового студента, у которого пока отсутствуют оценки (остальные данные заполнить). Этого же студента добавить в таблицу список (с тем же кодом).

Выбрать фамилии студентов и их оценки по Word. В случае отсутствия оценки, все равно выводить фамилию.

Sql left outer join 1. Вывести фамилии всех преподавателей, названия и длительность курсов, которые они ведут (name, title, length) из таблиц teachers и courses. Использовать внешнее объединение

В приведенных примерах можно вводить фильтры для более точной фильтрации:

Пример БД Институт: выводить только тех учителей, которые не проводили/не будут проводить уроков

✍ Решение:
 

SELECT t.name, t.code, l.course
FROM teachers t
LEFT OUTER JOIN lessons l ON t.id = l.tid
WHERE l.tid IS NULL

Запросы sql INNER JOIN

Объединение с подзапросом

При использовании объединения часто бывает необходимо, чтобы результирующая выборка содержала данные только по одной конкретной строке

Синтаксис:

SELECT t1.*, t2.* FROM left_table t1 
LEFT JOIN (SELECT * FROM right_table WHERE some_column = 1 LIMIT 1) 
t2 ON t1.id = t2.join_id

или

SELECT t1.*, t2.* FROM left_table t1 
INNER JOIN (SELECT * FROM right_table WHERE some_column = 1 LIMIT 1) 
t2 ON t1.id = t2.join_id

Пример БД Институт: Выбрать данные по учителям и проведенным ими урокам, только для уроков по курсу "php"

✍ Решение:
 

SELECT t1.*, t2.* FROM teachers t1 
INNER JOIN (SELECT * FROM lessons WHERE course = "php" LIMIT 1) 
t2 ON t1.id = t2.tid

Результат:
sql Объединение с подзапросом

Разберем еще один пример:

Пример: БД Компьютерный магазин. Выбрать все данные по компьютерам из таблиц product и pc.

✍ Решение:
 

1
2
3
4
5
6
7
SELECT t1.производитель, t1.Тип, t2 . * 
FROM pc t2
INNER JOIN (
SELECT * 
FROM product
WHERE Тип =  "Компьютер"
) t1 ON t2.Номер = t1.Номер

Так как в таблице product находятся данные не только по компьютерам, то мы использовали подзапрос, при этом сохранив внутреннее соединение таблиц.
Результат:
запросы sql inner join пример

Задание 3_6: БД Компьютерные курсы. Отобразить фамилии и оценки студентов, у которых по дисциплине Word оценка "отлично".
Поделитесь уроком с коллегами и друзьями:
One Comment

    Светлана Кныш

    Благодарю! Интересно как !

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*
*

Вставить формулу как
Блок
Строка
Дополнительные настройки
Цвет формулы
Цвет текста
#333333
Используйте LaTeX для набора формулы
Предпросмотр
\({}\)
Формула не набрана
Вставить