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

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

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

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

✍ Решение:
 

1
2
3
4
5

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

✍ Решение:
 

1
2
3
4

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

Иногда в предложении 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

✍ Решение:
 

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 INNER JOIN

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

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

teachers	lessons

✍ Решение:
 

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

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

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

Запросы sql OUTER JOIN

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

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

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

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

✍ Решение:
 

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

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

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

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

✍ Решение:
 

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

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

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

Синтаксис:

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 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

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

✍ Решение:
 

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

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

✍ Решение:
 

1
2
3
4
5
6
7

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