Intersect sql examples. Operator for INTERSECT data sets. Example with double quotes

The INTERSECT operator retrieves identical rows from the result sets of one or more queries. In some respects, the INTERSECT operator is very similar to INNER JOIN.

INTERSECT belongs to the class of operators for working with data sets (set operator). Other such operators include EXCEPT and UNION. All set operators are used to simultaneously manipulate the result sets of two or more queries, hence their name.

SQL2003 syntax

There are no technical restrictions on the number of queries in the INTERSECT operator. General syntax next.

INTERSECT

] INTERSECT

Keywords

Duplicate rows from all result sets are included.

DISTINCT

Duplicate rows are removed from all result sets before comparison is performed by the INTERSECT operator. Columns with empty (NULL) values ​​are considered duplicates. If neither the ALL nor DISTINCT keyword is specified, DISTINCT is assumed by default.

CORRESPONDING

Specifies that only columns that have the same name in both queries will be returned, even if the wildcard character (*) is used in both queries.

Specifies that only named columns will be returned, even if queries encounter other columns with matching names. This clause must be used in conjunction with the CORRESPONDING keyword.

General rules

There is only one important rule to remember when working with the INTERSECT operator.

The order and number of columns must be the same in all queries. The data types of the corresponding columns must also be compatible.

For example, the types CHAR and VARCHAR are compatible. By default, the result set in each column will be the size corresponding to the largest type at each particular position.

Neither platform supports the CORRESPONDING clause.

According to the ANSI standard, the INTERSECT operator has higher precedence than other set operators, although the precedence of such operators is handled differently on different platforms. You can explicitly control operator precedence using parentheses. Otherwise, the DBMS may execute them in order from left to right or from first to last.

According to the ANSI standard, you can only use one ORDER BY clause in a query. Insert it at the very end of the last SELECT statement. To avoid ambiguity in naming columns and tables, be sure to assign the same alias to all matching table columns. For example:

On platforms that do not support the INTERSECT operator, you can replace it with a FULL JOIN subquery.

SELECT a.au_lname AS "lastname", a.au_fname AS "firstname" FROM authors AS a INTERSECT SELECT e.emp_lname AS "lastname", e.emp_fname AS "firstname" FROM employees AS e ORDER BY lastname, firstname;

Because the data types of columns in different INTERSECT statements may be compatible, different RDBMS platforms may have different options for dealing with columns of different lengths. For example, if the aujname column from the first query in the previous example is significantly longer than the empjname column from the second query, then different platforms may apply different rules for determining the length of the final result. But generally speaking, platforms will choose a longer (and less restrictive) size for the result.

Each RDBMS may have its own rules for using a column name if the names in the column lists are different. Typically the column names of the first query are used.

DB2

The DB2 platform supports the ANSI keywords INTERSECT and INTERSECT ALL plus the optional VALUES clause.

(instruction._SELECT_7 | VALUES (expr7 [, ...])) INTERSECT

] (instruction_SCJ_2 | VALUES (expr2 [, ...])) INTERSECT

Although the INTERSECT DISTINCT statement is not supported, the functional equivalent is INTERSECT. The CORRESPONDING clause is not supported.

Additionally, the LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, and structure data types are not used in the INTERSECT clause, but they can be used in the INTERSECT ALL clause.

If the result set has a column that has the same name in all SELECT statements, then that name is used as the final name for the column returned by the statement. If queries for a column use different names, DB2 will generate a new name for the resulting column. After this, it becomes unusable in ORDER BY and FOR UPDATE clauses.

If a single query uses multiple operators to work with data sets, the one enclosed in parentheses is executed first. After this, the order of execution will be from left to right. However, all INTERSECT statements are executed before the UNION and EXCEPT statements, for example:

SELECT empno FROM employee WHERE workdept LIKE "E%" INTERSECT (SELECT empno FROM emp_act WHERE projno IN ("IF1000", "IF2000", "AD3110") UNION VALUES ("AA0001"), ("AB0002"), ("AC0003 "))

The example above retrieves the IDs of all employees working in the department that starts with "E" from the employee table. However, IDs are only retrieved if they also exist in the employee account table named emp_act and participate in projects IF1000, IF200, and AD3110.

The lesson will cover the topic of using the operations of union, intersection and difference queries. Examples of how it is used SQL query Union, Exists, and the use of the SOME, ANY and All keywords. String functions covered

You can perform the operations of union, difference, and Cartesian product on a set. The same operations can be used in sql queries(perform operations with requests).

Used to combine multiple queries function word UNION.
Syntax:

< запрос 1 >UNION[ALL]< запрос 2 >

<запрос 1>UNION<запрос 2>

The Union SQL query is used to combine the output rows of each query into a single result set.

If used parameter ALL, then all duplicate output lines are saved. If the parameter is missing, then only unique rows remain in the result set.

You can combine any number of queries together.

Using the UNION operator requires several conditions to be met:

1. the number of output columns of each query must be the same;
2. the output columns of each query must be comparable to each other by data type (in order of priority);
3. the resulting set uses the column names specified in the first query;
4. ORDER BY can only be used at the end of a compound query because it applies to the result of the join.

Example: Display prices for computers and laptops, as well as their numbers (i.e., unload from two different tables in one query)

✍ Solution:

1 2 3 4 5 6

SELECT `Number` , `Price` FROM pc UNION SELECT `Number` , `Price` FROM notebook ORDER BY `Price`

SELECT `Number` , `Price` FROM pc UNION SELECT `Number` , `Price` FROM notebook ORDER BY `Price`

Result:

Let's look at a more complex example with an inner join:

Example: Find product type, number and price of computers and laptops

✍ Solution:

1 2 3 4 5 6 7 8

SELECT product. `Type` , pc. `Number` , `Price` FROM pc INNER JOIN product ON pc. `Number` = product. `Number` UNION SELECT product. `Type` , notebook. `Number` , `Price` FROM notebook INNER JOIN product ON notebook. `Number` = product. `Number` ORDER BY `Price`

SELECT product.`Type` , pc.`Number` , `Price` FROM pc INNER JOIN product ON pc.`Number` = product.`Number` UNION SELECT product.`Type` , notebook.`Number` , `Price` FROM notebook INNER JOIN product ON notebook.`Number` = product.`Number` ORDER BY `Price`

Result:

SQL Union 1. Find manufacturer, part number and price of all laptops and printers

SQL Union 2. Find numbers and prices of all products produced by the manufacturer Russia

SQL Existence Predicate EXISTS

SQL has facilities for performing intersection and difference operations on queries—the INTERSECT clause (intersection) and the EXCEPT clause (difference). These clauses work similar to how UNION works: the result set includes only those rows that are present in both queries - INTERSECT, or only those rows of the first query that are missing in the second - EXCEPT. But the trouble is that many DBMSs do not support these proposals. But there is a way out - using the EXISTS predicate.

The EXISTS predicate accepts value TRUE(true) if the subquery returns at least some number of rows, otherwise EXISTS is FALSE. There is also a predicate NOT EXISTS, which does the opposite.

Typically EXISTS is used in dependent subqueries (for example, IN).

EXISTS(table subquery)

Example: Find computer manufacturers who also produce laptops

✍ Solution:

SELECT DISTINCT Manufacturer FROM product AS pc_product WHERE Type = "Computer" AND EXISTS (SELECT Manufacturer FROM product WHERE Type = "Laptop" AND Manufacturer = pc_product.Manufacturer)

Result:

Find those computer manufacturers that do not make printers

SQL SOME Keywords | ANY and ALL

The keywords SOME and ANY are synonymous, so you can use either of them in your query. The result of such a query will be one column of values.

Syntax:

< выражение>< оператор сравнения>SOME | ANY (< подзапрос> )

<выражение><оператор сравнения>SOME | ANY (<подзапрос>)

If for any value X returned from a subquery, the result of the " " operation returns TRUE , then the ANY predicate also evaluates to TRUE .

Example: Find computer suppliers whose numbers are not on sale (i.e. not in the pc table)

✍ Solution:

Table source data:

Result:

In the example, the predicate Number = ANY(SELECT Number FROM pc) will return the value TRUE when the Number from the main query is found in the list of Numbers of table pc (returned by the subquery). Additionally, NOT is used. The result set will consist of one column - Manufacturer. To prevent one manufacturer from being displayed several times, the service word DISTINCT has been introduced.
Now let's look at using the ALL keyword:

Example: Find numbers and prices of laptops that cost more than any computer

✍ Solution:

Important: It is worth noting that in general, a query with ANY returns a set of values. Therefore, using a subquery in a WHERE clause without the EXISTS , IN , ALL , and ANY operators, which produce a Boolean value, may result in a query runtime error

Example: Find numbers and prices of computers whose cost exceeds the minimum cost of laptops

✍ Solution:

This query is correct because the scalar expression Price is compared with a subquery that returns a single value

Functions for working with strings in SQL

The LEFT function cuts the number of characters specified by the second argument from the left of a string:

LEFT (<строка>,<число>)

The RIGHT function returns the specified number of characters to the right from a string expression:

RIGHT(<строка>,<число>)

Example: Print the first letters of the names of all manufacturers

✍ Solution:

SELECT DISTINCT LEFT(`Manufacturer` , 1) FROM `product`

Result:

Example: Print manufacturer names that begin and end with the same letter

✍ Solution:

SQL Replace Function

Syntax:

SELECT `name` , REPLACE(`name` , "a", "aa") FROM `teachers`

When you often come across any technology, programming language, or standard, a certain picture of their capabilities and the boundaries within which they are used are formed. This can go on for quite a long time until the eye catches examples that expand the hardened horizons of knowledge. Today, I would like to talk about such examples and demonstrate them for the SQL language. Interesting and rare designs, forgotten expressions, strange techniques await you in this article. Anyone interested, welcome to cat.

Nuances

I am often asked, who is this article for? But, believe me, it’s not always easy to give an answer: on the one hand, there are ninja developers who are difficult to surprise with anything, and on the other, young padawans. But I can say one thing for sure - for a reader who is interested in SQL, who is able to complement his rich picture with small but very interesting details. This article will not contain kilometer-long pages of sql queries, a maximum of 1 or 2 lines and only what is rare in my opinion. But since I want to be completely frank, if you are no stranger to sql, the article will seem a bit boring. All examples in the article, with the exception of the first and fourth, can be attributed to the SQL-92 standard.

Data

In order to make our life easier, I have put up a simple data plate on which certain points will be tested and for the sake of brevity, I will present the result of the experiment on them. I check all queries on PostgreSql.

Scripts and data table

CREATE TABLE goods(id bigint NOT NULL, name character varying(127) NOT NULL, description character varying(255) NOT NULL, price numeric(16,2) NOT NULL, articul character varying(20) NOT NULL, act_time timestamp NOT NULL , availability boolean NOT NULL, CONSTRAINT pk_goods PRIMARY KEY (id)); INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (1, "Slippers", "Soft", 100.00, "TR-75", (ts "2017-01-01 01:01: 01.01"), TRUE); INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (2, "Pillow", "White", 200.00, "PR-75", (ts "2017-01-02 02:02: 02.02"), TRUE); INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (3, "Blanket", "Down", 300.00, "ZR-75", (ts "2017-01-03 03:03: 03.03"), TRUE); INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (4, "Pillowcase", "Grey", 400.00, "AR-75", (ts "2017-01-04 04:04: 04.04"), FALSE); INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (5, "Sheet", "Silk", 500.00, "BR-75", (ts "2017-01-05 05:05: 05.05"), FALSE);

Requests

1. Double quotes

And the first thing I have is a simple question: Could you give an example of a sql query using double quotes? Yes, not with singles, doubles?

Example with double quotes

SELECT name "Product name" FROM goods

I was very surprised when I saw this for the first time. If you try to change double quotes to single quotes, the result will be completely other!

This may not seem like a very useful example for real development. For me this is not the case. Now I actively use it in all my SQL templates. The point is simple: when you return after half a year to an sql query of 40 columns, oh how their “our” name saves you. Despite the fact that I did not mention about SQL-92, in the latest edition there is a mention of double quotes.

2. Pseudo table. SQL-92

It’s a little inaccurate from the point of view of terminology, but the essence is simple - the table resulting from a subquery in the FROM section. Perhaps the most famous fact in this article

Pseudo table

SELECT mock.nickname "Nickname", (CASE WHEN mock.huff THEN "Yes" ELSE "No" END) "Offended?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock

In our example, the mock is a pseudo table (sometimes called a virtual table). Naturally, they are not intended at all to distort the true meaning. An example is this.

3. Data block constructor. SQL-92

It sounds scary, simply because I haven't found a good translation or interpretation. And as always, it’s easier to explain with an example:

Data block constructor example

SELECT name "Product name", price "Price" FROM (VALUES ("Slippers", 100.00), ("Pillow", 200.00)) AS goods(name, price)

Product name	Price
Slippers	100.00
Pillow	200.00

In section FROM used keyword VALUES, followed by data in parentheses, line by line. The point is that we do not select data from any table at all, but simply create it on the fly, “call” it a table, name the columns and then use it at our discretion. This thing turned out to be extremely useful when testing different sql query cases, when there is no data for some tables (in your local database), and writing insert is too lazy or sometimes very difficult, due to the relatedness of the tables and restrictions.

4. Time, Date and Time-and-Date

Probably everyone has encountered in requests the need to specify time, date or date-and-time. Many DBMSs support the literals t, d, and ts, respectively, for working with these types. But it’s easier to explain with an example: For the literals d and t, everything is similar.
I apologize to the reader for misleading me, but everything that is said in paragraph 4 does not relate to the SQL language, but relates to the capabilities of preprocessing queries in JDBC.

5. Denial. SQL-92

We all know about the operator NOT, but very often they forget that it can be applied both to a group of predicates and to a single column:

6. Comparison of data blocks. SQL-92

Once again I apologize for the terminology. This is one of my favorite examples

Example of comparing data blocks

SELECT * FROM goods WHERE (name, price, availability) = ("Pillowcase", 400.00, FALSE) -- or its equivalent SELECT * FROM goods WHERE name = "Pillowcase" AND price = 400.00 AND availability = FALSE

As can be seen from the example, comparing data blocks is similar to comparing element-by-element meaning_ 1 _block_1 = value_ 1 _block_2, value_ 2 _block_1 = value_ 2 _block_2, value_ 3 _block_1 = value_ 3 _block_2 using AND between them.

7. Comparison operators with modifiers ANY, SOME or ALL. SQL-92

This is where some clarification is required. But as always, first an example What does it mean ALL in this case? And it means that the selection condition is satisfied only by those rows whose identifiers (in our case these are 4 and 5) are greater any from the found values ​​in the subquery (1, 2 and 3). 4 is greater than 1 and than 2 and than 3. 5 is the same. What happens if we replace ALL on ANY?
What does it mean ANY in this case? And it means that the selection condition is satisfied only by those rows whose identifiers (in our case these are 2, 3, 4 and 5) are greater at least one from the found values ​​in the subquery (1, 2 and 3). For myself I associated ALL With AND, A ANY With OR. SOME And ANY analogues to each other.

8. Operators for working with/under requests. SQL-92

It is well known that you can combine 2 queries with each other using the operators UNION or UNION ALL. This is used often. But there are 2 more operators EXCEPT And INTERSECT.

Example with EXCEPT

Actually, the data from the second set is excluded from the first set of values.
Actually, the intersection of the first set of values ​​and the second set occurs.
That's all, thank you for your attention.

Editorial

N1. Thanks to streetflush for the constructive criticism. Contributed an article with information about what is a language standard and what is not.
N2. Point 4 has been corrected to clarify that ts/d/t is not part of the SQL language. Thank you for your attention Melkij.

This SQL tutorial explains how to use the SQL INTERSECT operator with syntax and examples.

Description

The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Intersect Query

Explanation: The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2.

Each SQL statement within the SQL INTERSECT must have the same number of fields in the result sets with similar data types.

Syntax

The syntax for the INTERSECT operator in SQL is:

SELECT expression1, expression2, ... expression_n FROM tables INTERSECT SELECT expression1, expression2, ... expression_n FROM tables ;

Parameters or Arguments

expression1, expression2, expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.

Example - With Single Expression

The following is a SQL INTERSECT operator example that has one field with the same data type:

SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;

In this SQL INTERSECT example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

Now, let's complicate our example further by adding to the INTERSECT query.

SELECT supplier_id FROM suppliers WHERE supplier_id > 78 INTERSECT SELECT supplier_id FROM orders WHERE quantity<> 0;

In this example, the WHERE clauses have been added to each of the datasets. The first dataset has been filtered so that only records from the suppliers table where the supplier_id is greater than 78 are returned. The second dataset has been filtered so that only records from the orders quantity is not equal to 0.

Example - With Multiple Expressions

Next, let's look at an example of how to use the INTERSECT operator in SQL to return more than one column.

SELECT contact_id, last_name, first_name FROM contacts WHERE last_name<>"Anderson" INTERSECT SELECT customer_id, last_name, first_name FROM customers WHERE customer_id< 50;

In this INTERSECT example, the query will return the records from the contacts table where the contact_id, last_name, and first_name values ​​match the customer_id, last_name, and first_name value from the customers table.

There are WHERE conditions on each data set to further filter the results so that only records from the contacts are returned where the last_name is not Anderson. The records from the customers table are returned where the customer_id is less than 50.

Example - Using ORDER BY

SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 2000 INTERSECT SELECT company_id, company_name FROM companies WHERE company_id > 1000 ORDER BY 2;

Since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we"ve sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2 .

The supplier_name / company_name fields are in position #2 in the result set.

SQL provides two ways to join tables:

• specifying the tables to be joined (including subqueries) in the FROM clause SELECT statement. First, the tables are joined, and only then the conditions specified by the WHERE clause are applied to the resulting set, aggregation defined by the GROUP BY clause, data ordering, etc.;
• defining the union of the result sets obtained by processing the SELECT statement. In this case, two SELECT statements are connected by the phrase UNION, INTERSECT, EXCEPT or CORRESPONDING.

UNION

Phrase UNION combines the results of two queries according to the following rules:

The standard does not impose any restrictions on the ordering of rows in the result set. Thus, some DBMSs first display the result of the first query, and then the result of the second query. The Oracle DBMS automatically sorts records by the first column specified, even if no index has been created for it.

To explicitly specify the required sort order, use the ORDER BY clause. In this case, you can use both the column name and its number (Fig. 4.3).

Rice. 4.3.

Phrase UNION ALL performs a union of two subqueries similar to a UNION clause with the following exceptions:

• matching rows are not removed from the generated result set;
• merged queries are displayed in the result set sequentially without ordering.

When merging more than two queries, you can use parentheses to change the order in which the join operation is performed (Figure 4.4).

Rice. 4.4.

INTERSECT association

Phrase INTERSECT allows you to select only those rows that are present in each result set being merged. In Fig. 4.5 shows an example of combining queries as intersecting sets.

Rice. 4.5.

EXCEPT join

Phrase EXCEPT allows you to select only those rows that are present in the first result set being merged but not in the second result set.

Phrases INTERSECT And EXCEPT should only be supported at full level of compliance with the SQL-92 standard. So, some DBMSs instead of the phrase