INTERSECT и EXCEPT операторы Transact-SQL – описание и примеры использования

Продолжаем изучать язык SQL и в частности его расширение Transact-SQL, и сегодня мы затронем тему таких операторов объединения набора данных как INTERSECT и EXCEPT, мы узнаем, что это за операторы и как их можно использовать.

INTERSECT и EXCEPT

Язык Transact-SQL мы изучаем уже достаточно давно и посветили ему немало статей, но такие операторы как INTERSECT и EXCEPT мы не рассматривали, хотя если говорить в принципе об объединениях, то мы изучали такие конструкции как UNION и UNION ALL, а также рассматривали простые объединения JOIN. Поэтому сегодня давайте узнаем, что делают такие полезные, но малоизвестные операторы Transact-SQL как INTERSECT и EXCEPT.

Примечание! Все примеры мы будем делать в Management Studio SQL Server 2014 Express, также если кому интересно как можно обновиться с 2008 SQL сервера до 2014, то это мы рассматривали вот здесь.

Исходные данные для примеров

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

Таблица 1

 
   CREATE TABLE [dbo].[test_table](
        [id] [int] NOT NULL,
        [tip] [varchar](50) NULL,
        [summa] [varchar](50) NULL
   ) ON [PRIMARY]
   GO

Ее данные

Скриншот 1

Таблица 2

 
   CREATE TABLE [dbo].[test_table_two](
        [id] [int] NOT NULL,
        [tip] [varchar](50) NULL,
        [summa] [varchar](50) NULL
   ) ON [PRIMARY]
   GO

И ее данные

Скриншот 2

Оператор INTERSECT

INTERSECT (пересечение) – это оператор Transact-SQL, который выводит одинаковые строки из первого, второго и последующих наборов данных. Другими словами, он выведет только те строки, которые есть как в первом результирующем наборе, так и во втором (третьем и так далее), т.е. происходит пересечение этих строк.

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

Как и у оператора UNION, у INTERSECT есть правила, например, то, что количество полей во всех результирующих наборах должно быть одинаковым, также как и их тип данных.

Пример

Давайте узнаем, какие данные у нас есть и в таблице test_table и в таблице test_table_two, т.е. совпадения, для этого пишем простой SQL запрос:

 
   SELECT tip, summa FROM test_table
        intersect
   SELECT tip, summa FROM test_table_two

Скриншот 3

Как видите, у нас в обеих таблицах есть «Принтер» с суммой 100 и «Сканер» с суммой 150.

Оператор EXCEPT

EXCEPT (разность) — это оператор Transact-SQL, который выводит только те данные из первого набора строк, которых нет во втором наборе.

Здесь те же правила, что и у оператора INTERSECT, т.е. количество столбцов (и их тип) должно быть одинаковым.

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

Пример

Давайте посмотрим, какие строки есть только в первой таблице

 
   SELECT tip, summa FROM test_table
        except
   SELECT tip, summa FROM test_table_two

Скриншот 4

Как видите, во второй таблице нет строки, у которой tip «Монитор», а сумма 200, если помните, то у нас во второй таблице монитор с суммой 250.

А теперь давайте поменяем наши таблицы местами и посмотрим на результат.

 
   SELECT tip, summa FROM test_table_two
        except
   SELECT tip, summa FROM test_table

Скриншот 5

Здесь результат уже другой, так как за основу взята другая таблица, и в результате у нас вывелись те строки, которых нет в таблице test_table.

Вот в принципе и вся основа этих операторов, надеюсь, данная статья помогла Вам в решение тех или иных задач, начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментариев: 6
  1. Аватар
    Nero

    Беда при заполнении таблицы в базе данных выскакивает EROR1054 Unknown column хотя просматривая созданную таблицу через DESC колонка есть. Что это может быть, подскажите пожалуйста.

  2. Админ
    Админ (автор)

    Цитирую Nero:Беда при заполнении таблицы в базе данных выскакивает EROR1054 Unknown column хотя просматривая созданную таблицу через DESC колонка есть. Что это может быть, подскажите пожалуйста.
    Что за СУБД? И приведите пример названия таблицы с названиями колонок, а также пример запроса на вставку данных.
    Данная ошибка означает «Неизвестный столбец»

  3. Аватар
    Nero

    СУБД из видеоурока на Youtubу CMTV [IT науки] Изучение программировани я. SQL 4. Базовые команды.
    Название СУБД clients
    Название таблицы subscribers
    Колонки: id, имя, фамилия, email
    Запрос: CREATE TABLE subscribers (
    -id INT,
    -fname VARCHAR(30),
    -lname VARCHAR(30),
    -email VARCHAR(30));
    Queri OK…
    DESC subscribers;
    Показывает таблицу
    INSERT INTO subscribers VALUES (
    -1,
    -`Petr`,
    -`Radko`,
    -`testmail.ru`);
    EROR 1054 (42S22): Unknown column `Petr` in `field list`

  4. Аватар
    Nero

    СУБД из видеоурока на Youtube CMTV [IT науки] Изучение программировани я. SQL 4. Базовые команды
    mysql> CREATE TABLE subscribers (
    -> id INT,
    -> fname VARCHAR(30),
    -> lname VARCHAR(30),
    -> email VARCHAR(30));
    mysql> DESC subscribers;
    +——-+————-+——+——+———+——-+
    | Field | Type | Null | Key | Default | Extra |
    +——-+————-+——+——+———+——-+
    | id | int(11) | YES | | NULL | |
    | fname | varchar(30) | YES | | NULL | |
    | lname | varchar(30) | YES | | NULL | |
    | email | varchar(30) | YES | | NULL | |
    +——-+————-+——+——+———
    4 rows in set (0.05 sec)
    mysql> INSERT INTO subscribers VALUES (
    -> 1,
    -> `Petr`,
    -> `Radko`,
    -> `testmail.ru`);
    ERROR 1054 (42S22): Unknown column ‘Petr’ in ‘field list’
    mysql>

  5. Админ
    Админ (автор)

    Первое это СУБД MySQL (данная статья про MS SQL Server), второе писать значения нужно в апострофах, например:
    INSERT INTO subscribers VALUES (1, ‘Petr’,’Radko’,’testmail.ru’)

  6. Аватар
    Nero

    Так ведь все значения в апострофах и написаны.

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.