Редкий sql

Содержание:

Example — With Multiple Expressions

Next, let’s look at how to simulate an INTERSECT query in MySQL that returns more than one column.

First, this is how you would use the INTERSECT operator to return multiple expressions.

SELECT contact_id, last_name, first_name
FROM contacts
WHERE contact_id < 100
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name <> 'Johnson';

Again, since you can’t use the INTERSECT operator in MySQL, you can use the EXISTS clause in more complex situations to simulate the INTERSECT query as follows:

SELECT contacts.contact_id, contacts.last_name, contacts.first_name
FROM contacts
WHERE contacts.contact_id < 100
AND EXISTS (SELECT *
            FROM customers
            WHERE customers.last_name <> 'Johnson'
            AND customers.customer_id = contacts.contact_id
            AND customers.last_name = contacts.last_name
            AND customers.first_name = contacts.first_name);

In this more complex example, you can use the EXISTS clause to return multiple expressions that exist in both the contacts table where the contact_id is less than 100 as well as the customers table where the last_name is not equal to Johnson.

Because you are doing an INTERSECT, you need to join the intersect fields as follows:

AND customers.customer_id = contacts.contact_id
AND customers.last_name = contacts.last_name
AND customers.first_name = contacts.first_name

This join is performed to ensure that the customer_id, last_name, and first_name fields from the customers table are intersected with the contact_id, last_name, and first_name fields from the contacts table.

Union

Операция Union возвращает объединение множеств из двух исходных последовательностей. У этой операции имеется один прототип, описанный ниже:

Эта операция возвращает объект, который сначала перечисляет элементы последовательности по имени first, выдавая последовательность, в которой каждый элемент не эквивалентен предыдущим выданным, затем перечисляет вторую входную последовательность second, опять-таки, выдавая последовательность без повторений. Эквивалентность элементов определяется методами GetHashCode и Equals.

Чтобы продемонстрировать разницу между операцией Union и описанной ранее операцией Concat, в примере, представленном ниже, создаются последовательности first и second из массива cars, что приведет к дублированию пятого элемента в обеих последовательностях. Затем отображается количество элементов в массиве cars, а также в последовательностях first и second, наряду с количеством элементов в конкатенированной и объединенной последовательностях:

В конечном итоге последовательность concat должна иметь на один элемент больше, чем массив cars. Последовательность union должна содержать то же количество элементов, что и массив cars. Это доказывают результаты выполнения кода:

Пример

Рассмотрим на примере как использовать Varray в Oracle PL/SQL.

Oracle PL/SQL

DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); — Varray type

— переменная varray, инициализированная конструктором:

team Foursome := Foursome(‘John’, ‘Mary’, ‘Alberto’, ‘Juanita’);

PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);

FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i || ‘.’ || team(i));
END LOOP;

DBMS_OUTPUT.PUT_LINE(‘—‘);
END;

BEGIN
print_team(‘2001 Team:’);

team(3) := ‘Pierre’; — Изменение значений двух элементов
team(4) := ‘Yvonne’;
print_team(‘2005 Team:’);

— Вызывать конструктор для назначения новых значений переменной Varray:

team := Foursome(‘Arun’, ‘Amitha’, ‘Allan’, ‘Mae’);
print_team(‘2009 Team:’);
END;

В результате получим:
2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
—
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
—
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
—

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

DECLARE

TYPEFoursomeISVARRAY(4)OFVARCHAR2(15);— Varray type

— переменная varray, инициализированная конструктором:

teamFoursome:=Foursome(‘John’,’Mary’,’Alberto’,’Juanita’);

PROCEDUREprint_team(headingVARCHAR2)IS

BEGIN

DBMS_OUTPUT.PUT_LINE(heading);

FORiIN1..4LOOP

DBMS_OUTPUT.PUT_LINE(i||’.’||team(i));

ENDLOOP;

DBMS_OUTPUT.PUT_LINE(‘—‘);

END;

BEGIN

print_team(‘2001 Team:’);

team(3):=’Pierre’;— Изменение значений двух элементов

team(4):=’Yvonne’;

print_team(‘2005 Team:’);

— Вызывать конструктор для назначения новых значений переменной Varray:

team:=Foursome(‘Arun’,’Amitha’,’Allan’,’Mae’);

print_team(‘2009 Team:’);

END;

Врезультатеполучим:
2001Team:
1.John
2.Mary
3.Alberto
4.Juanita
—
2005Team:
1.John
2.Mary
3.Pierre
4.Yvonne
—
2009Team:
1.Arun
2.Amitha
3.Allan
4.Mae
—

В этом примере мы определили Foursome как локальный тип Varray, объявили переменную team этого типа (инициализировали его конструктором) и определили процедуру print_team, которая напечатала Varray. Пример вызывает процедуру три раза:

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

UNION

Последнее обновление: 20.07.2017

Оператор UNION подобно inner join или outer join позволяет соединить две таблицы. Но в отличие от inner/outer join
объединения соединяют не столбцы разных таблиц, а два однотипных набора в один. Формальный синтаксис объединения:

SELECT_выражение1
UNION  SELECT_выражение2
 SELECT_выражениеN]

Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица Customers) и для сотрудников банка (таблица Employees):

USE usersdb;

CREATE TABLE Customers
(
    Id INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(20) NOT NULL,
	LastName NVARCHAR(20) NOT NULL,
	AccountSum MONEY
);
CREATE TABLE Employees
(
    Id INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(20) NOT NULL,
	LastName NVARCHAR(20) NOT NULL,
);

INSERT INTO Customers VALUES
('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)

INSERT INTO Employees VALUES
('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')

Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами —
именем (FirstName) и фамилией (LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:

SELECT FirstName, LastName 
FROM Customers
UNION SELECT FirstName, LastName FROM Employees

В данном случае из первой таблицы выбираются два значения — имя и фамилия клиента. Из второй таблицы Employees также
выбираются два значения — имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип
совпадают для обеих выборок.

При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку,
то в выражениях ORDER BY необходимо ориентироваться именно на названия столбцов первой выборки:

SELECT FirstName + ' ' +LastName AS FullName
FROM Customers
UNION SELECT FirstName + ' ' + LastName AS EmployeeName 
FROM Employees
ORDER BY FullName DESC

В данном случае каждая выборка имеет по одному столбцу, который представляет объединение имени и фамилии клиента или сотрудника.
Но в случае с клиентами столбец будет называться FullName, а в случае с сотрудниками — EmployeeName. Тем не менее для сортировки применяется название столбца из первой выборки и он же будет в результирующей выборке:

Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены. Например, в следующем случае объединение завершится с ошибкой:

SELECT FirstName, LastName, AccountSum
FROM Customers
UNION SELECT FirstName, LastName 
FROM Employees

Также соответствующие столбцы должны соответствовать по типу. Так, следующий пример завершится с ошибкой из-за не соответствия по типу данных:

SELECT FirstName, LastName
FROM Customers
UNION SELECT Id, LastName 
FROM Employees

В данном случае первый столбец первой выборки имеет тип NVARCHAR, то есть хранит строку. Первый столбец второй выборки — Id имеет тип INT, то есть хранит число.

Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются.
Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах.
При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL:

SELECT FirstName, LastName
FROM Customers
UNION ALL SELECT FirstName, LastName 
FROM Employees

Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:

SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum 
FROM Customers WHERE AccountSum < 3000
UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum 
FROM Customers WHERE AccountSum >= 3000

В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то
проценты увеличиваются до 30%.

НазадВперед

Пример

Рассмотрим несколько примеров функции Oracle SYS_CONTEXT и изучим, как использовать функцию SYS_CONTEXT в Oracle/PLSQL.

Oracle PL/SQL

SELECT SYS_CONTEXT(‘USERENV’, ‘LANG’) FROM DUAL;

—Результат: RU

SELECT SYS_CONTEXT(‘USERENV’, ‘LANGUAGE’) FROM DUAL;

—Результат: RUSSIAN_CIS.CL8MSWIN1251

SELECT SYS_CONTEXT(‘USERENV’, ‘NLS_CALENDAR’) FROM DUAL;

—Результат: GREGORIAN

SELECT SYS_CONTEXT(‘USERENV’, ‘NLS_DATE_FORMAT’) FROM DUAL;

—Результат: DD.MM.RR

SELECT SYS_CONTEXT(‘USERENV’, ‘NLS_TERRITORY’) FROM DUAL;

—Результат: CIS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECTSYS_CONTEXT(‘USERENV’,’LANG’)FROMDUAL;

—Результат: RU

SELECTSYS_CONTEXT(‘USERENV’,’LANGUAGE’)FROMDUAL;

—Результат: RUSSIAN_CIS.CL8MSWIN1251

SELECTSYS_CONTEXT(‘USERENV’,’NLS_CALENDAR’)FROMDUAL;

—Результат: GREGORIAN

SELECTSYS_CONTEXT(‘USERENV’,’NLS_DATE_FORMAT’)FROMDUAL;

—Результат: DD.MM.RR

SELECTSYS_CONTEXT(‘USERENV’,’NLS_TERRITORY’)FROMDUAL;

—Результат: CIS

Пример с одним выражением

Во-первых, давайте рассмотрим, как имитировать запрос INTERSECT в MySQL, который имеет одно поле с тем же типом данных. Если база данных поддерживала оператор INTERSECT (чего нет у MySQL), так вы бы использовали оператор INTERSECT для возврата общих значений category_id между таблицами products и inventory.

MySQL

SELECT category_id
FROM products
INTERSECT
SELECT category_id
FROM inventory;

1
2
3
4
5

SELECTcategory_id

FROMproducts
INTERSECT

SELECTcategory_id

FROMinventory;

Поскольку вы не можете использовать оператор INTERSECT в MySQL, вы будете использовать оператор IN для имитации запроса INTERSECT следующим образом:

MySQL

SELECT products.category_id
FROM products
WHERE products.category_id IN (SELECT inventory.category_id FROM inventory);

1
2
3

SELECTproducts.category_id

FROMproducts

WHEREproducts.category_idIN(SELECTinventory.category_idFROMinventory);

В этом простом примере вы можете использовать оператор IN для возврата всех значений category_id, которые существуют как в products, так и в таблицах inventory.

Теперь давайте усложним наш пример, добавив условия WHERE к запросу INTERSECT.

Например, так выглядит INTERSECT с условиями WHERE:

MySQL

SELECT category_id
FROM products
WHERE category_id < 100
INTERSECT
SELECT category_id
FROM inventory
WHERE quantity > 0;

1
2
3
4
5
6
7

SELECTcategory_id

FROMproducts

WHEREcategory_id<100
INTERSECT

SELECTcategory_id

FROMinventory

WHEREquantity>0;

Вот как вы могли бы моделировать запрос INTERSECT с помощью оператора IN и включать условия WHERE:

MySQL

SELECT products.category_id
FROM products
WHERE products.category_id < 100
AND products.category_id IN
(SELECT inventory.category_id
FROM inventory
WHERE inventory.quantity > 0);

1
2
3
4
5
6
7

SELECTproducts.category_id

FROMproducts

WHEREproducts.category_id<100

ANDproducts.category_idIN

(SELECTinventory.category_id

FROMinventory

WHEREinventory.quantity>0);

В этом примере были добавлены предложения WHERE, которые фильтруют как таблицу products, так и результаты из таблицы inventory.

SQL intersect samples

OK, now that we remind the set theory and that we understand it, let’s jump to an example.

We will use the AdventureworksDW tables. We will use 2 tables. The dbo.FactInternetSales and the dbo.DimCurrency tables. We will get the common elements. Let’s take a look at the dbo.FactInternetSales first:

Notice that this table has the CurrencyKey column, we will use this column to get common values between this table and the dbo.DimCurrency that contains all the CurrencyKey IDs.

Now, let’s take a look at the dbo.DimCurrency table:

The currencykey is the common column between both tables, we will compare them and find the common values, the query will be this one:

1
2
3

selectCurrencykeyfromdbo.FactInternetSales

intersect

selectcurrencykeyfromDimCurrency

The result displayed by the query is the following:

These values are common in both tables. You can compare multiple columns, if applicable, it is also possible to get the intersected values between 3 or more tables. We will show these scenarios below:

How to do a SQL intersect with 3 or more tables

The following example, will create 2 extra tables for this example:

1 2	selecttop5intodbo.table1fromdbo.FactInternetSales selecttop7intodbo.table2fromdbo.FactInternetSales

The query is creating 2 tables named table1 and table2 based on the top 5 and top 7 rows of the dbo.FactInternetSales.

Once that we have the tables, let’s run the example:

1
2
3
4
5
6
7

selectCurrencykeyfromdbo.FactInternetSales

intersect

selectcurrencykeyfromDimCurrency

intersect

selectcurrencykeyfromdbo.table1

intersect

selectcurrencykeyfromdbo.table2

This example will show all the common currency keys between the tables dbo.Facinternetsales, dimcurrency, table1 and table2.

MySQL Query Types

SELECT Statement	Retrieve records from a table
SELECT LIMIT Statement	Retrieve records from a table and limit results
INSERT Statement	Insert records into a table
UPDATE Statement	Update records in a table
DELETE Statement	Delete records from a table
DELETE LIMIT Statement	Delete records and limit number of deletions
TRUNCATE TABLE Statement	Delete all records from a table (no rollback)
UNION Operator	Combine 2 result sets (removes duplicates)
UNION ALL Operator	Combine 2 result sets (includes duplicates)
INTERSECT Operator	Intersection of 2 result sets
Subqueries	A query within a query

SQL Server EXCEPT Examples

If we want to find out which people exists in the manager table, but not in the
customer table and get a distinct list back we can issue the following command:

SELECT FIRSTNAME, 
       LASTNAME, 
       ADDRESSLINE1, 
       CITY, 
       STATEPROVINCECODE, 
       POSTALCODE 
FROM   MANAGER  
EXCEPT 
SELECT FIRSTNAME, 
       LASTNAME, 
       ADDRESSLINE1, 
       CITY, 
       STATEPROVINCECODE, 
       POSTALCODE 
FROM   CUSTOMER

Here is the result set:

To do this same thing with a regular T-SQL command we would have to write the
following:

SELECT   M.FIRSTNAME, 
         M.LASTNAME, 
         M.ADDRESSLINE1, 
         M.CITY, 
         M.STATEPROVINCECODE, 
         M.POSTALCODE 
FROM     MANAGER M 
WHERE    NOT EXISTS (SELECT * 
                     FROM   CUSTOMER C 
                     WHERE  M.FIRSTNAME = C.FIRSTNAME 
                            AND M.LASTNAME = C.LASTNAME 
                            AND M.ADDRESSLINE1 = C.ADDRESSLINE1 
                            AND M.CITY = C.CITY 
                            AND M.POSTALCODE = C.POSTALCODE) 
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY, 
         M.STATEPROVINCECODE,M.POSTALCODE

From the two examples above we can see that using the EXCEPT and INTERSECT commands
are much simpler to write then having to write the join or exists statements.

To take this a step further if we had a third table (or forth…) that listed
sales reps and we wanted to find out which managers were customers, but not sales
reps we could do the following.

Here is the SalesRep table sample data:

SELECT FIRSTNAME, 
       LASTNAME, 
       ADDRESSLINE1, 
       CITY, 
       STATEPROVINCECODE, 
       POSTALCODE 
FROM   MANAGER 
INTERSECT  
SELECT FIRSTNAME, 
       LASTNAME, 
       ADDRESSLINE1, 
       CITY, 
       STATEPROVINCECODE, 
       POSTALCODE 
FROM   CUSTOMER  
EXCEPT 
SELECT FIRSTNAME, 
       LASTNAME, 
       ADDRESSLINE1, 
       CITY, 
       STATEPROVINCECODE, 
       POSTALCODE 
FROM   SALESREP

Here is the result set:

As you can see this is pretty simple to mix and match these statements.
In addition, you could also use the UNION and UNION ALL operators to further extend
your final result sets.

Next Steps

Take a look at your existing code to see how the INTERSECT and EXCEPT operators
could be used
Keep these new operators in mind next time you need to compare different
datasets with like data

About the author

Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
View all my tips

Related Resources

SQL Server Join Example…
Join SQL Server tables where columns include NULL …
UNION vs. UNION ALL in SQL Server…
Compare SQL Server Datasets with INTERSECT and EXC…
SQL Server CROSS APPLY and OUTER APPLY…
More Database Developer Tips…

Become a paid author

Differences between SQL intersect and SQL INNER join

For some scenarios, both options can be used. The way the results is displayed are different. If you are not familiar with inner join we strongly recommend to check our link related:

A step-by-step walkthrough of SQL Inner Join

The inner join will show common values between

Let’s take a look at the results of the intersect first:

1
2
3

selectCurrencykeyfromdbo.FactInternetSales

intersect

selectcurrencykeyfromDimCurrency

The result of the previous query is the following:

Now, let’s take a look at the inner join:

1
2
3

selectf.Currencykeyfromdbo.FactInternetSalesf

innerjoindimcurrencyd

onf.currencykey=d.currencykey

The result of the inner join is the following:

The main visible difference is that intersect does not show repeated values. That may imply a big difference in the performance.

If we run a select distinct with the inner join, we may have the same value that we have got using the intersect clause.

1
2
3

selectdistinctf.Currencykeyfromdbo.FactInternetSalesf

innerjoindimcurrencyd

onf.currencykey=d.currencykey

Синтаксис

Cинтаксис Oracle PL/SQL WITH с одним подзапросом:

WITH query_name AS (SELECT expressions FROM table_A) SELECT column_list FROM query_name

или

Cинтаксис Oracle PL/SQL WITH с с несколькими подзапросами:

WITH query_name_A AS (SELECT expressions FROM table_A), query_name_B AS ( | ) SELECT column_list FROM query_name_A, query_name_B

expressions — поля или расчеты подзапроса.column_list — поля или расчеты основного запроса.table_A, table_B, table_X, table_Z — таблицы или соединения для подзапросов.query_name_A, query_name_B — псевдоним подзапроса. Если подзапросов несколько, то они перечисляются через запятую.WHERE conditions — условия которые должны быть выполнены для основных запросов.

Пример с одним expressions

Рассмотрим пример запроса INTERSECT в SQL Server (Transact-SQL), который возвращает один столбец с тем же типом данных. Например:

Transact-SQL

SELECT product_id
FROM products
INTERSECT
SELECT product_id
FROM inventory;

1
2
3
4
5

SELECTproduct_id

FROMproducts

INTERSECT

SELECTproduct_id

FROMinventory;

В этом примере INTERSECT, если product_id появился как в таблице products, так и в таблице inventory, он появится в вашем результирующем наборе для этого запроса INTERSECT.

Теперь давайте усложним наш пример, добавив условия WHERE к запросу INTERSECT.

Transact-SQL

SELECT product_id
FROM products
WHERE product_id >= 30
INTERSECT
SELECT product_id
FROM inventory
WHERE quantity > 10;

1
2
3
4
5
6
7

SELECTproduct_id

FROMproducts

WHEREproduct_id>=30

INTERSECT

SELECTproduct_id

FROMinventory

WHEREquantity>10;

В этом примере к каждому набору данных добавлены предложения WHERE. Первый набор данных был отфильтрован таким образом, что возвращаются только записи из таблицы products, где product_id больше или равно 30. Второй набор данных был отфильтрован таким образом, чтобы возвращались только записи из таблицы inventory, где quantity больше 10.