Сравнение двух таблиц, вывод уникальных значений по одному столбцу
Имеется 2 одинаковых таблицы. Допустим Лист1 и Лист2, в каждой таблице имеется один столбец Поле1. Мне нужно сверить значения Лист2 с Лист1 и вывести только уникальные. (Хочу убрать дубли, которые имеются в Лист1). Другими словами. Мне нужно вывести только те значения Лист2, которых нет в Лист1.
Отслеживать
51.6k 200 200 золотых знаков 61 61 серебряный знак 242 242 бронзовых знака
задан 1 мая 2018 в 7:16
31 1 1 серебряный знак 4 4 бронзовых знака
не ясно что скрывается под словом «сверить», видимо вам нужен join. А уникальные дает ключевое слово distinct
1 мая 2018 в 7:21
Мне нужно вывести только те значения Лист2, которых нет в Лист1.
1 мая 2018 в 7:39
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Есть два варианта.
Первый вам привёл @IgorSl:
SELECT DISTINCT [Поле1] FROM [Лист1] WHERE 1 = 1 AND [Поле1] NOT IN (SELECT [Поле1] FROM [Лист2]) Второй через JOIN :
SELECT DISTINCT [Лист1].[Поле1] FROM [Лист1] LEFT JOIN [Лист2] ON [Лист1].[Поле1] = [Лист2].[Поле1] WHERE 1 = 1 AND [Лист2].[Поле1] IS NULL В принципе, они эквиваленты. В разовом запросе на небольших данных да при отсутствии индекса по полю разницы особо не будет (и там и там в плане выполнения запроса будет table scan).
Как применить операцию сразу ко всем таблицам или ко всем базам данных
Администраторам часто бывает необходимо перебрать все таблицы базы данных, чтобы над каждой таблицей произвести какое-то действие. Например, перестроить индексы.
Традиционно для такого перебора можно использовать курсор. Но есть способ проще — процедура sp_MSForEachTable. Ей можно передать текст команды или запроса, который будет выполнен для каждой таблицы в базе. Команда, разумеется, параметризуется — вместо вопросительного знака будет подставлено название таблицы.
Посмотрите на сигнатуру этой процедуры:
За один вызов вы можете передать ей три команды, которые будут выполнены для каждой таблицы, плюс начальное и конечное действия для всего пакета команд, а также указать условие включения таблицы в перебор. Параметр @ReplaceChar предназначен для запросов, в которых не получается использовать вопросительный знак для параметризации.
Каждая из трёх команд может содержать больше одного SQL-запроса. При написании фильтра @WhereAnd учтите, что ваша строка будет встроена внутри процедуры в более сложный запрос к системным таблицам, поэтому используйте для фильтрации столбцы из SysObjects. Например:
Имеется аналогичная процедура для перебора всех баз данных на сервере — sp_MSForEachDB:
С её помощью вы сможете выполнить однотипный набор действий над каждой базой на сервере:
Подробнее об этом Вы сможете узнать на курсах SQL Server
Заказ добавлен в Корзину.
Для завершения оформления, пожалуйста, перейдите в Корзину!
Ограничения SQL: как создать, примеры
В процессе работы с таблицами SQL вам нередко нужно будет устанавливать ограничения в базе данных SQL на типы данных, которые могут храниться в определенной таблице. Допустим, у вас есть таблица с данными о сотрудниках — логично, что значения в некоторых ячейках не могут быть пустыми. И мы можем задать такое ограничение значений SQL при помощи простой инструкции. Также можно требовать, чтобы вводимые значения были уникальными, или, например, проверять данные по условию. В статье рассмотрим, как это сделать и используем все возможные типы ограничений, но сначала немного о терминологии.
Что такое ограничения таблицы SQL
То или иное правило, которое мы применяем к полям SQL, определяя, какие значение допустимо туда вносить, а какие нет, будет называться ограничением SQL . После добавления такого правила программа будет проверять, можно ли вставлять, обновлять или удалять данные в таблице, исходя из заданных пользователем ограничений. И если нет, операция не будет выполнена и программа вернет ошибку. Теперь давайте рассмотрим все возможные типы ограничений в базах данных SQL, а для наглядности приведем примеры, которые могут иметь практическую ценность для вас.
Добавление ограничений SQL
Создать ограничения SQL можно, используя инструкции PRIMARY KEY , FOREIGN KEY , UNIQUE , CHECK и NOT NULL .
Ограничение NOT NULL
Ограничение NOT NULL гарантирует, что столбец обязательно будет иметь значение для каждой записи, то есть значение будет не нулевым. Таким образом программа не позволит хранить в столбцах пустые значения. Давайте создадим таблицу, содержащую столбец с таким ограничением:
CREATE TABLE Countries (
Country VARCHAR(46) NOT NULL,
Capital VARCHAR(46)
)Здесь мы допускаем, что название столицы государства может быть опущено, но при этом обязательно должно быть введено название страны. Попробуем добавить запись, нарушающую это правило:
INSERT INTO Countries VALUES (null, 'Madrid')Результатом будет эта ошибка:
Column 'Country' cannot be nullА вот такая запись ошибки не вызовет, потому что оставлять пустым столбец с названиями столиц ( Capital ) мы не запрещали:
INSERT INTO Countries VALUES ('Spain', null)Ограничение NOT NULL может быть полезно для столбцов с контактными данными, когда нам нужно обязать пользователя, например, ввести свою электронную почту или номер телефона. Поэтому такие обязательные поля нередко используют ограничение NOT NULL , чтобы гарантировать, что пользователь введет определенное значение:
CREATE TABLE Subscribers (
SubscriberName VARCHAR(46) NOT NULL,
SubscriberContact VARCHAR(46) NOT NULL,
)В данном случае мы требуем от пользователей обязательного ввода имени и адреса электронной почты, установив ограничение для каждого поля таблицы в 64 символа. Указывать лимиты на количество символов в некоторых полях тоже может быть полезно, чтобы предотвратить добавление заведомо некорректных данных. Также эта операция нередко применяется для экономии, чтобы не раздувать объем базы данных.
Ограничение UNIQUE
Unique значит «уникальный», и это название полностью отражает суть ограничения. Таким образом, ограничение UNIQUE гарантирует, что никакие два значения в определяемом столбце не будут одинаковыми. Давайте посмотрим на таблицу, в которой используется UNIQUE :
CREATE TABLE Workers1 (
WorkerName VARCHAR(46) NOT NULL,
WorkerDate DATE,
WorkerContact INTEGER UNIQUE
)Мы создали таблицу работников, в которую будем добавлять имя работника (поле не может быть пустым, так как мы установили для него уже знакомое ограничение NOT NULL ), дату приема на работу (в формате даты, на что указывает тип данных DATE ) и номер телефона. При этом номер телефона должен быть уникальным, на что и указывает ограничение UNIQUE . Давайте вставим в нашу таблицу следующие данные:
INSERT INTO Workers1 VALUES ('Vasya Pupkin', DATE '2018-05-10', 89009000000)Теперь при попытке добавления строки с таким же номером телефона:
INSERT INTO Workers1 VALUES ('Petya Pupkin', DATE '2020-06-11', 89009000000)Программа выдаст ошибку:
Duplicate entry '89009000000' for key 'uniqueconstraint.WorkerContact'Ограничение UNIQUE идеально подходит для столбцов, которые не должны содержать повторяющихся значений. Например, у каждого из нас уникальные номера паспорта и полиса социального страхования (СНИЛС). Таким образом, если таблица содержит столбцы, в которых хранятся номера паспорта и СНИЛС, эти столбцы должны использовать ограничение UNIQUE . Это необходимо, чтобы избежать того, что у двух человек будут одни и те же номера, которые могут быть вставлены по ошибке или намеренно.
Ограничение CHECK
Check в переводе с английского значит «проверять», и ограничение CHECK служит для проверки значений по определенному условию. Рассмотрим следующий пример:
CREATE TABLE Customers1 (
CustomerName1 VARCHAR(46),
CustomerName2 VARCHAR(46),
CustomerEmail VARCHAR(56),
CustomerAge INTEGER CHECK (CustomerAge>17)
)Мы включили ограничение по возрасту, который должен быть больше 17 лет. Теперь посмотрим, что мы получим, если покупатель вводит следующие данные:
INSERT INTO Customers1 VALUES ('Vasya', 'Pupkin', 'vasya_pupkin@anysite.com', 17)Вот что нам выдаст система:
Check constraint 'checkconstraint_chk_1' is violatedИнструкцию CHECK можно использовать для реализации пользовательских ограничений. Так, если в таблице должны храниться только данные взрослых, мы могли бы использовать ограничение CHECK для столбца «Возраст покупателя» ( CustomerAge>17 , как в примере выше). Другой пример: если в таблице должны храниться данные только граждан России, мы могли бы использовать CHECK : например, для нового столбца Customer Country: CHECK (CustomerCountry=’Russia’) .
Ограничение PRIMARY KEY
PRIMARY KEY — это одно из ограничений ключа таблицы SQL , в данном случае — первичного . PRIMARY KEY используется для создания идентификатора, с которым соотносится каждая строка в таблице. Добавим, что PRIMARY KEY в таблице может относиться только к одному столбцу (и это понятно, так как это идентификатор). Соответственно, каждое значение PRIMARY KEY обязательно должно быть уникальным, при этом нулевые значения в столбце, определенном с помощью PRIMARY KEY , не допускаются. Чтобы было понятнее, о чём речь, рассмотрим следующий пример:
CREATE TABLE Workers2 (
id INTEGER PRIMARY KEY,
WorkerName1 VARCHAR(46),
WorkerName2 VARCHAR(46),
WorkerAge INTEGER CHECK (WorkerAge>17)
)Как видим, ключ PRIMARY KEY , позволяет нам задать id работника, чтобы затем можно было обращаться к каждой записи через уникальный числовой ключ. Также обратим внимание на уже привычное ограничение CHECK в столбце возраста.
Ограничение FOREIGN KEY
Ограничение FOREIGN KEY (внешний ключ) создает ссылку на PRIMARY KEY из другой таблицы. Таким образом, столбец, в котором есть FOREIGN KEY , ссылается на столбец с PRIMARY KEY из другой таблицы, и текущая таблица связывается с ней через это ограничение. Чтобы было понятнее, что делает этот ключ, давайте посмотрим на пример ограничения FOREIGN KEY , связанного с PRIMARY KEY из уже созданной выше таблицы:
CREATE TABLE WorkersTaxes (
WorkerTax INTEGER,
Worker_id INTEGER,
FOREIGN KEY (Worker_id) REFERENCES Workers2(id)
)Итак, нам понадобилось создать таблицу для расчета налогов работников. И, чтобы связать эту таблицу ( WorkersTaxes ) с таблицей работников ( Workers2 ), мы использовали ссылку FOREIGN KEY , которая идентифицирует работников по PRIMARY KEY из таблицы Workers2 . Таким образом мы достигли связности значений, и теперь каждый сотрудник может быть без труда идентифицирован в обеих таблицах по связанным ключам.
Другие ограничения
Осталось добавить, что к ограничениям SQL Standard также иногда относят DEFAULT , однако DEFAULT не ограничивает тип вводимых данных, поэтому технически не может быть отнесен к ограничениям. Тем не менее эту инструкцию также следует упомянуть здесь, поскольку она позволяет реализовать довольно важную функцию: подстановку значений по умолчанию, когда пользователь их не вводит. Это может понадобится, например, для того, чтобы избежать возможных ошибок при отсутствии ввода. Рассмотрим следующий пример:
CREATE TABLE Customers2 (
CustomerName1 VARCHAR(46) NOT NULL,
CustomerName2 VARCHAR(46) NOT NULL,
CustomerAge INTEGER DEFAULT 18,
)Теперь, если покупатель не укажет возраст, он будет проставлен автоматически. В данном случае это помогло бы избежать лишних вопросов, которые бы появились у проверяющих, если бы возраст не был указан. А обязать клиента вводить имя и фамилию мы смогли при помощи уже знакомого ограничения NOT NULL .
Надеемся, вам стало понятно, как использовать каждое ограничение SQL и какие преимущества они дают. Удачной работы!
Что такое join в SQL и как с ним работать
Join — оператор для объединения данных из нескольких таблиц с общим ключом.
Анастасия Хамидулина
Автор статьи
9 июня 2022 в 18:11
SQL — Simple Query Language, то есть «простой язык запросов». Его создали, чтобы работать с реляционными базами данных. В таких базах данные представлены в виде таблиц. Зависимости между несколькими таблицами задают с помощью связующих — реляционных столбцов.
Когда запрашиваем данные из одной таблицы, работа со связующими столбцами не нужна. Но если нужно агрегировать данные из нескольких, стоит описать правила: как будут связаны строки на основе значений связующих столбцов. Тогда на помощь и приходит оператор join.
Что такое оператор join в SQL
Join — оператор, который используют, чтобы объединять строки из двух или более таблиц на основе связующего столбца между ними. Такой столбец еще называют ключом.
Чтобы разобраться в основах SQL, записывайтесь на курс
«Аналитик данных». Вы научитесь делать таблицы и составлять запросы для анализа. Узнаете, как соединять и обрабатывать несколько таблиц, использовать оконные функции.
Предположим, что у нас есть таблица заказов — Orders:
| OrderID | CustomerID | OrderDate |
| 304101 | 21 | 10-05-2021 |
| 304102 | 34 | 20-06-2021 |
| 304103 | 22 | 25-07-2021 |
И таблица клиентов — Customers:
| CustomerID | CustomerName | ContactName |
| 21 | Балалайка Сервис | Иван Иванов |
| 22 | Рога и копыта | Семён Семёнов |
| 23 | Редиска Менеджмент | Пётр Петров |
Столбец CustomerID в таблице заказов соотносится со столбцом CustomerID в таблице клиентов. То есть он — связующий двух таблиц. Чтобы узнать, когда, какой клиент и какой заказ оформил, составьте запрос:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Результат запроса будет выглядеть так:
| OrderID | CustomerName | OrderDate |
| 304101 | Балалайка Сервис | 10-05-2021 |
| 304103 | Редиска Менеджмент | 25-07-2021 |
Общий синтаксис оператора join:
Python-разработчик: новая работа через 9 месяцев
Получится, даже если у вас нет опыта в IT
Соединять можно и больше двух таблиц: к запросу добавьте еще один оператор join. Например, в дополнение к предыдущим двум таблицам у нас есть таблица продавцов — Managers:
| OrderID | ManagerName | ContactDate |
| 304101 | Артём Лапин | 05-05-2021 |
| 304102 | Егор Орлов | 15-06-2021 |
| 304103 | Евгений Соколов | 20-07-2021 |
Таблица продавцов связана с таблицей заказов столбцом OrderID. Чтобы в дополнение к предыдущему запросу узнать, какой продавец обслуживал заказ, составьте следующий запрос:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, Managers.ManagerName FROM Orders JOIN Customers ON Orders.CustomerID=Customers.CustomerID JOIN Managers ON Orders.OrderId=Managers.OrderId
| OrderID | CustomerName | OrderDate | ManagerName |
| 304101 | Балалайка Сервис | 10-05-2021 | Артём Лапин |
| 304103 | Редиска Менеджмент | 25-07-2021 | Евгений Соколов |
Внутреннее соединение INNER JOIN
Если использовать оператор INNER JOIN, в результат запроса попадут только те записи, для которых выполняется условие объединения. Еще одно условие — записи должны быть в обеих таблицах. В финальный результат из примера выше не попали записи с CustomerID=23 и OrderID=304102: для них нет соответствия в таблицах.
Если хотите составлять запросы на объединение данных, приходите на курс «Анализ данных». Научитесь работать с простыми и сложными запросами и использовать разные комбинации для решения реальных задач. А еще получите диплом установленного образца и сможете хорошо зарабатывать.
Общий синтаксис запроса INNER JOIN:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Иллюстрация работы INNER JOIN
Слово INNER в запросе можно опускать, тогда общий синтаксис запроса будет выглядеть так:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Внешние соединения OUTER JOIN
Если использовать внешнее соединение, то в результат запроса попадут не только записи с совпадениями в обеих таблицах, но и записи одной из таблиц целиком. Этим внешнее соединение отличается от внутреннего.
Указание таблицы, из которой нужно выбрать все записи без фильтрации, называется направлением соединения.
LEFT OUTER JOIN / LEFT JOIN
В финальный результат такого соединения попадут все записи из левой, первой таблицы. Даже если не будет ни одного совпадения с правой. И записи из второй таблицы, для которых выполняется условие объединения.
Иллюстрация работы LEFT JOIN
Синтаксис:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Пример:
| OrderID | CustomerID | OrderDate |
| 304101 | 21 | 10-05-2021 |
| 304102 | 34 | 20-06-2021 |
| 304103 | 22 | 25-07-2021 |
| CustomerID | CustomerName | ContactName |
| 21 | Балалайка Сервис | Иван Иванов |
| 22 | Рога и копыта | Семён Семёнов |
| 23 | Редиска Менеджмент | Пётр Петров |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
| OrderID | CustomerName | OrderDate |
| 304101 | Балалайка Сервис | 10-05-2021 |
| 304102 | null | 20-06-2021 |
| 304103 | Редиска Менеджмент | 25-07-2021 |
RIGHT OUTER JOIN / RIGHT JOIN
В финальный результат этого соединения попадут все записи из правой, второй таблицы. Даже если не будет ни одного совпадения с левой. И записи из первой таблицы, для которых выполняется условие объединения.
Иллюстрация работы RIGHT JOIN
Синтаксис:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Пример:
| OrderID | CustomerID | OrderDate |
| 304101 | 21 | 10-05-2021 |
| 304102 | 34 | 20-06-2021 |
| 304103 | 22 | 25-07-2021 |
| CustomerID | CustomerName | ContactName |
| 21 | Балалайка Сервис | Иван Иванов |
| 22 | Рога и копыта | Семён Семёнов |
| 23 | Редиска Менеджмент | Пётр Петров |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
| OrderID | CustomerName | OrderDate |
| 304101 | Балалайка Сервис | 10-05-2021 |
| null | Рога и копыта | null |
| 304103 | Редиска Менеджмент | 25-07-2021 |
FULL OUTER JOIN / FULL JOIN
В финальный результат такого соединения попадут все записи из обеих таблиц. Независимо от того, выполняется условие объединения или нет.
Если хотите разобраться в нюансах join в SQL, приходите на курс
«Программирование для анализа данных». Вы узнаете, как работать с базами данных и таблицами: создавать их, объединять и обрабатывать. Выполните практические задания и получите ответы на вопросы от наставников.
Иллюстрация работы FULL JOIN
Синтаксис:
SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
Пример:
| OrderID | CustomerID | OrderDate |
| 304101 | 21 | 10-05-2021 |
| 304102 | 34 | 20-06-2021 |
| 304103 | 22 | 25-07-2021 |
| CustomerID | CustomerName | ContactName |
| 21 | Балалайка Сервис | Иван Иванов |
| 22 | Рога и копыта | Семён Семёнов |
| 23 | Редиска Менеджмент | Пётр Петров |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders FULL JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
| OrderID | CustomerName | OrderDate |
| 304101 | Балалайка Сервис | 10-05-2021 |
| 304102 | null | 20-06-2021 |
| 304103 | Редиска Менеджмент | 25-07-2021 |
| null | Рога и копыта | null |
Перекрестное соединение CROSS JOIN
Этот оператор отличается от предыдущих операторов соединения: ему не нужно задавать условие объединения (ON table1.column_name = table2.column_name). Записи в таблице с результатами — это результат объединения каждой записи из левой таблицы с записями из правой. Такое действие называют декартовым произведением.
Иллюстрация работы CROSS JOIN
Синтаксис:
SELECT column_name(s) FROM table1 CROSS JOIN table2;
Пример:
| OrderID | CustomerID | OrderDate |
| 304101 | 21 | 10-05-2021 |
| 304102 | 34 | 20-06-2021 |
| 304103 | 22 | 25-07-2021 |
| CustomerID | CustomerName | ContactName |
| 21 | Балалайка Сервис | Иван Иванов |
| 22 | Рога и копыта | Семён Семёнов |
| 23 | Редиска Менеджмент | Пётр Петров |
</p> SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders CROSS JOIN Customers;
| OrderID | CustomerName | OrderDate |
| 304101 | Балалайка Сервис | 10-05-2021 |
| 304101 | Рога и копыта | 10-05-2021 |
| 304101 | Редиска Менеджмент | 10-05-2021 |
| 304102 | Балалайка Сервис | 20-06-2021 |
| 304102 | Рога и копыта | 20-06-2021 |
| 304102 | Редиска Менеджмент | 20-06-2021 |
| 304103 | Балалайка Сервис | 25-07-2021 |
| 304103 | Рога и копыта | 25-07-2021 |
| 304103 | Редиска Менеджмент | 25-07-2021 |
Соединение SELF JOIN
Его используют, когда в запросе нужно соединить несколько записей из одной и той же таблицы.
В SQL нет отдельного оператора, чтобы описать SELF JOIN соединения. Поэтому, чтобы описать соединения данных из одной и той же таблицы, воспользуйтесь операторами JOIN или WHERE.
Если интересно разобраться в тонкостях работы с SQL, понять, как делать запросы и составлять таблицы, приходите на курс «Аналитик данных». Под руководством наставников вы попрактикуетесь в решении разных задач и сможете профессионально работать с одним из самых популярных языков запросов.
Учтите, что в одном запросе нельзя дважды использовать имя одной и той же таблицы: иначе запрос вернет ошибку. Поэтому, чтобы выполнить соединение таблицы SQL с самой собой, в запросе ей присваивают два разных временных имени — алиаса.
Синтаксис соединения SELF JOIN при использовании оператора JOIN:
SELECT column_name(s) FROM table1 a1 JOIN table1 a2 ON a1.column_name = a2.column_name;
Оператор JOIN может быть любым: используйте LEFT JOIN, RIGHT JOIN. Результат будет таким же, как когда объединяли две разные таблицы.
Синтаксис соединения SELF JOIN при использовании оператора WHERE:
SELECT column_name(s) FROM table1 a1, table1 a2 WHERE a1.common_col_name = a2.common_col_name;
Пример:
| StudentID | Name | CourseID | Duration |
| 1 | Артём | 1 | 3 |
| 2 | Пётр | 2 | 4 |
| 1 | Артём | 2 | 4 |
| 3 | Борис | 3 | 2 |
| 2 | Ирина | 3 | 5 |
Запрос с оператором WHERE:
SELECT s1.StudentID, s1.Name FROM Students AS s1, Students s2 WHERE s1.StudentID = s2.StudentID AND s1.CourseID <> s2.CourseID;
| StudentID | Name |
| 1 | Артём |
| 2 | Ирина |
| 1 | Артём |
| 2 | Пётр |
Запрос с оператором JOIN:
SELECT s1.StudentID, s1.Name FROM Students s1 JOIN Students s2 ON s1.StudentID = s2.StudentID AND s1.CourseID <> s2.CourseID GROUP BY StudentID;
| StudentID | Name |
| 1 | Артём |
| 2 | Ирина |