MySQL. Вложеные запросы. JOIN LEFT/RIGHT.

SELECT

Подзапросы чаще всего используются с инструкцией SELECT. При этом используется следующий синтаксис

					
    SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
        (SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
            (SELECT имя_столбца FROM имя_таблицы WHERE условие)
                ...
		)
		;
					
				

Ниже представлена струтура таблицы для демонстрации примеров

Вывести суммы заказов и даты, которые проводил продавец с фамилией "Плотников".

Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу SALES(или выполнили отдельный запрос), определили бы snum продавца "Плотников" — он равен 3. И выполнили бы запрос SQL с помощью условия WHERE.

					
    SELECT amt, odate
    FROM orders 
    WHERE snum = 3
					
				

Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос).

					
    SELECT amt, odate
    FROM orders
    WHERE snum = (  SELECT snum
                    FROM sales
                    WHERE sname = 'Плотников')
					
				

В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы SALES, а затем, в таблице ORDERS определяем по этому идентификатору нужные нам значения.

Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.

					
    SELECT snum, sname
    FROM sales
    WHERE snum IN ( SELECT snum
                    FROM orders
                    WHERE odate = 2016)
					
				

Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN.

Оператор IN следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений.

То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы SALES в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.

Предыдущие примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.

Вывести пары покупателей и продавцов, которые осуществили сделку между собой, но не позднее 2014 года

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

					
    SELECT cname as 'Покупатель', sname as 'Продавец'
    FROM customers cus, sales sal
    WHERE (cus.cnum, sal.snum) IN ( SELECT cnum, snum
                                    FROM orders
                                    WHERE odate < 2014 )
					
				

В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы ORDERS берутся те строки, которые удовлетворяют условию не позднее 2014 года, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.

На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN.

Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.

					
    CREATE TABLE NEW_TABLE_NAME AS
                                    SELECT [ column1, column2...columnN ]
                                    FROM EXISTING_TABLE_NAME
                                    [ WHERE ]
					
				

					
    CREATE TABLE city_bkp AS
                            SELECT *
                            FROM city
                            WHERE 1=0
					
				

					
    INSERT INTO table_name [ (column1 [, column2 ]) ]
                SELECT [ *|column1 [, column2 ]
                FROM table1 [, table2 ]
                [ WHERE VALUE OPERATOR ]
					
				

					
    INSERT INTO city_bkp SELECT * FROM city
					
				

					
    INSERT INTO city_bkp 
                SELECT * FROM city 
                WHERE CountryCode IN 
                                (SELECT Code FROM country 
                                 WHERE Population < 1000000 AND Population > 500000)
					
				

					
    UPDATE table
    SET column_name = new_value
        [ WHERE OPERATOR [ VALUE ]
        (SELECT COLUMN_NAME
         FROM TABLE_NAME)
         [ WHERE) ]
					
				

					
    UPDATE city_bkp SET Population = Population * 0.25 
    WHERE Population IN (
                         SELECT Population FROM city 
                         WHERE Population >= 100000 ) 
					
				

					
    DELETE FROM TABLE_NAME
    [ WHERE OPERATOR [ VALUE ]
        (SELECT COLUMN_NAME
         FROM TABLE_NAME)
         [ WHERE) ]
					
				

INNER JOIN

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

					
    SELECT * FROM Person
    INNER JOIN
        City
    ON Person.city_id = City.id
					
				

Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.

LEFT JOIN

Возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

					
    SELECT * FROM Person
    LEFT JOIN
        City
    ON Person.city_id = City.id
					
				

Для записей неудовлетворяющих условия объединения поля правой таблицы заполняются значениями NULL

RIGHT JOIN

Возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

					
    SELECT * FROM Person
    RIGHT JOIN
        City
    ON Person.city_id = City.id
					
				

Для записей неудовлетворяющих условия объединения поля левой таблицы заполняются значениями NULL