Execution of complex SQL queries. Operator for datasets EXCEPT general rules Sql except examples
Nuances
I am often asked, who is this article for? But, believe me, it is 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 one thing I can say for sure - for a reader who is interested in SQL, who is able to supplement his rich picture with small, but very interesting details. This article will not contain kilometer pages of an sql query, a maximum of 1, 2 lines, and only what is rare in my opinion. But since I want to be completely frank, if you are with sql on you, the article will seem 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 life easier for us, I threw a simple data plate on which certain points will be tested, and for brevity, I will give the result of the experiment on them. I check all requests 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 it for the first time. If you try to change double quotes to single quotes, the result will be completely different!
It may seem that this is not a very useful example for real development. For me it's not. Now I actively use it in all my sql-blanks. The bottom line is simple, when you return in half a year to a sql-query of 40 columns, oh, how their "our" name saves. Despite the fact that I did not indicate about SQL-92, in latest edition there is a mention of double quotes.
2. Pseudo table. SQL-92
A little not exactly, in terms of terminology, but the essence is simple - the table resulting from the subquery in the FROM section. Perhaps the most known fact In this articlePseudo table
SELECT mock.nickname "Nickname", (CASE WHEN mock.huff THEN "Yes" ELSE "No" END) "Huffed?" 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
Sounds scary, just because I didn't find 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 keyword is used VALUES, followed by data in parentheses, line by line. The bottom line is that we do not select data from any table at all, but simply create it on the fly, “name” it a table, name the columns and then use it at our discretion. This thing turned out to be extremely useful when testing different cases of a sql query, 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 connection of tables and restrictions.
4. Time, Date and Time-and-Date
Probably everyone has come across in queries, with the need to specify the time, date or date-and-time. Many DBMSs support the literals t, d, and ts, respectively, to work with these types. But it's easier to explain with an example: For the literals d and t, everything is the same.I apologize to the reader for misleading, but everything that is said in paragraph 4 does not apply to the SQL language, but refers to the query preprocessing capabilities in JDBC.
5. Denial. SQL-92
We all know about the operator NOT, but very often 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.Data Block Comparison Example
SELECT * FROM goods WHERE (name, price, availability) = ("Pillowcase", 400.00, FALSE) -- or equivalent SELECT * FROM goods WHERE name = "Pillowcase" AND price = 400.00 AND availability = FALSE
As you can see 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 ANY, SOME or ALL modifiers. SQL-92
This is where an explanation is required. But as always, first an exampleWhat 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 than 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 similar. What happens if we replace ALL on ANY?What does 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 than 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 requests / under requests. SQL-92
It is enough known that it is possible to combine 2 queries with each other using the operators UNION or UNION ALL. This is often used. But there are 2 more operators EXCEPT And INTERSECT.Example with EXCEPT
Actually, the data of the second set are excluded from the first set of values.
Actually there is an intersection of the first set of values and the second set.
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. Item 4 corrected, with clarification that ts/d/t is not part of the SQL language. Thank you for your attention Melkij.
The INTERSECT statement retrieves identical rows from the result sets of one or more queries. In some ways, 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 dataset operators are used to manipulate the result sets of two or more queries at the same time, hence their name.
Syntax SQL2003
There is no technical limit to the number of queries in an INTERSECT statement. 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 statement. Columns with NULL values are considered duplicates. If neither ALL nor DISTINCT 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 both queries use the wildcard character (*).
Specifies that only named columns will be returned, even if queries find 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 in all queries must be the same. The data types of the corresponding columns must also be compatible.
For example, CHAR and VARCHAR types are compatible. By default, the result set in each column will use the size corresponding to the largest type in 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, only one ORDER BY clause can be used in a query. Insert it at the very end of the last SELECT statement. To avoid ambiguity in specifying 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;
Since the data types of columns in different INTERSECT statements may be compatible, different RDBMS platforms may have different ways of 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 have 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 can enforce its own rules for using a column name in case the names in the column lists differ. Typically the column names of the first query are used.
DB2
The DB2 platform supports the ANSI INTERSECT and INTERSECT ALL keywords plus an 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.
In addition, the data types LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, and structural types do not apply in an INTERSECT clause, but they can be used in an 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. However, if different column names are used in queries, the DB2 platform will generate a new name for the resulting column. After that, it becomes unusable in ORDER BY and FOR UPDATE clauses.
If multiple operators are used in a single query to work with data sets, then the one enclosed in parentheses is executed first. After that, the execution order 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 employee IDs of all employees in the department whose name begins with "E" from the employee table. However, identifiers are retrieved only if they also exist in the employee account table named emp_act and are involved in projects IF1000, IF200" and AD3110.
There is only one important rule for using the EXCEPT statement to remember.
The order, number, and data types of columns must be the same type in all queries.
According to the ANSI standard, the UNION and EXCEPT set operators have the same precedence, but the INTERSECT operator is evaluated before the other set operators. We recommend explicitly controlling operator precedence using parentheses. This is generally very good practice.
According to the ANSI standard, only one ORDER BY clause can be used in a query. Insert it at the very end of the last SELECT statement. To avoid ambiguity in specifying columns and tables, be sure to assign the same alias to all matching table columns. For example:
SELECT au_lname AS "lastname", au_fname AS "firstname" FROM authors EXCEPT SELECT emp_lname AS "lastname", emp_fname AS "firstname" FROM employees ORDER BY lastname, firstname;
In addition, since columns can be specified in each column list with correspondingly compatible data types, different RDBMS platforms may have different ways of dealing with columns of different lengths. For example, if the au_lname column from the first query in the previous example is significantly longer than the emp_lname column from the second query, then different platforms may have 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 can enforce its own rules for using a column name in case the names in the column lists differ. In general, the column names of the first query are used.
The data types need not be identical, but they must be compatible. For example, CHAR and VARCHAR types are compatible. By default, the result set in each column will use the size corresponding to the largest type in each particular position. For example, a query that retrieves data from columns containing values of type VARCHAR(IO) and VARCHAR(15) will use the type and size of VARCHAR(15).
None of the platforms support the CORRESPONDING )) EXCEPT clause
(SELECT statemenr.2 | VALUES (expressionl, expression2 [, ...])) EXCEPT
Allows you to specify one or more manually specified columns that are included in the final result set. (This is called the row constructor.) The VALUES clause must specify exactly as many columns as are specified in the EXCEPT statement queries. Although the EXCEPT DISTINCT statement is not supported, EXCEPT is the functional equivalent. The CORRESPONDING clause is not supported. Also, the LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK data types, and structure types are not used in an EXCEPT clause, but they can be used in an EXCEPT 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 this column is named differently in different instructions SELECT, then you must rename the column in all queries using the same AS alias clause in all of them.
If multiple operators are used in a single query to work with data sets, then the one enclosed in parentheses is executed first. After that, the execution order 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%" EXCEPT SELECT empno FROM emp_act WHERE projno IN (TF1000", TF2000", -AD3110") UNION VALUES ("AA0001"), ("AB0002"), ("AC0003") ;
The above example retrieves the IDs of all employees who work in the department that starts with "E" from the employee table, then removes the IDs of those who work in projects IF1000, IF200" and AD3110 from the employee account table (emp_act) Finally, three additional IDs, AA0001, AB0002, and AC0003, are added using the UNION set operator.
MySQL
MySQL does not support the EXCEPT statement. Alternatively, you can use the NOT IN or NOT EXISTS operators.
SQL provides two ways to join tables:- by specifying the tables to join (including subqueries) in the FROM clause of the SELECT statement. First, the tables are joined, and only then the conditions specified by the WHERE clause, aggregation, data ordering, etc., determined by the GROUP BY clause, are applied to the resulting set;
- defining the union of the result sets produced by the processing of the SELECT statement. In this case, the two SELECT statements are joined 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. So, some DBMS first display the result of the first query, and then the result of the second query. Oracle DBMS automatically sorts records by the first specified column, 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 name of the column and its number (Fig. 4.3).
Rice. 4.3.
Phrase UNION ALL performs the union of two subqueries in the same way as the UNION clause, with the following exceptions:
- matching rows are not removed from the generated result set;
- merged queries appear sequentially in the result set without ordering.
When combining more than two queries, parentheses can be used to change the order in which the join operation is performed (Figure 4.4).
Rice. 4.4.
INTERSECT join
Phrase INTERSECT allows you to select only those rows that are present in each result set being merged. On fig. 4.5 shows an example of combining queries as intersecting sets.
Rice. 4.5.
EXCEPT union
Phrase EXCEPT allows you to select only those rows that are present in the first result set to be merged but not present in the second result set.
Phrases INTERSECT And EXCEPT should only be supported at full SQL-92 compliance level. So, some DBMS instead of the phrase
The lesson will cover the topic of using the operations of union, intersection and query difference. Examples of how to use SQL query Union, Exists, as well as the use of the SOME, ANY and All keywords. String Functions Considered
On a set, you can perform union, difference, and Cartesian product operations. The same operations can be used in sql queries(perform operations on requests).
A function word is used to combine multiple queries UNION.
Syntax:
< запрос 1 >UNION [ALL]< запрос 2 > <запрос 1>UNION<запрос 2>
The SQL Union query is used to combine the output rows of each query into one result set.
If used parameter ALL, then all duplicate output rows are kept. If the parameter is absent, then only unique rows remain in the result set.
Any number of queries can be combined together.
The use of the UNION operator requires several conditions to be met:
- the number of output columns of each of the queries must be the same;
- the output columns of each of the queries must be comparable to each other in terms of data types (in the order of their priority);
- the resulting set uses the column names specified in the first query;
- ORDER BY can only be used at the end of a multipart query, as it applies to the result of a 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:
Consider 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 manufactured by the manufacturer Russia
SQL Existence Predicate EXISTS
IN SQL language there are tools for performing intersection and query difference operations - the INTERSECT clause (intersection) and the EXCEPT clause (difference). These clauses work similarly to how UNION works: only those rows that are present in both queries - INTERSECT , or only those rows of the first query that are absent in the second - EXCEPT get into the result set. But the trouble is that many DBMS do not support these proposals. But there is a way out - using the EXISTS predicate.
The EXISTS predicate is TRUE if the subquery returns any number of rows, otherwise EXISTS is FALSE. There is also a NOT EXISTS predicate that does the opposite.
Typically, EXISTS is used in dependent subqueries (like IN).
EXISTS(table subquery)
Example: Find those computer manufacturers that also make laptops
✍ Solution:
SELECT DISTINCT Manufacturer FROM product AS pc_product WHERE Type = "Computer" AND EXISTS (SELECT Manufacturer FROM product WHERE Type = "Notebook" AND Manufacturer = pc_product.Manufacturer)
Result: 
Find those computer manufacturers that don't make printers
Keywords SQL SOME | ANY and ALL
The keywords SOME and ANY are synonyms, so either one can be used in a query. The result of such a query will be one column of values.
Syntax:
< выражение>< оператор сравнения>SOME | ANY(< подзапрос> ) <выражение><оператор сравнения>SOME | ANY(<подзапрос>)
If for any value of X obtained from the subquery, the result of the operation " " returns TRUE , then the ANY predicate also equals TRUE .
Example: Find computer vendors that don't have part numbers on sale (i.e. not in the pc table)
✍ Solution:
Initial data of tables:
Result: 
In the example, the predicate Number = ANY(SELECT Number FROM pc) will return TRUE if the Number from the main query is found in the list of Numbers in the pc table (returned by the subquery). Also, NOT is used. The result set will consist of one column - Manufacturer. To prevent the same manufacturer from being displayed multiple times, the DISTINCT keyword has been introduced.
Now consider using keyword ALL:
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 that yield a boolean (boolean) value can result in a query runtime error
Example: Find numbers and prices of computers that cost more than the minimum price of laptops
✍ Solution:
This query is correct because the scalar expression Price is compared with a subquery that returns a single value
SQL String Functions
The LEFT function cuts the number of characters specified by the second argument from the left of the string:
LEFT (<строка>,<число>)
The RIGHT function returns the specified number of characters to the right of a string expression:
RIGHT(<строка>,<число>)
Example: Extract the first letters of the names of all manufacturers
✍ Solution:
SELECT DISTINCT LEFT(`Manufacturer` , 1) FROM `product`
Result: 
Example: Print manufacturer names that start and end with the same letter
✍ Solution:
SQL Replace function
Syntax:
SELECT `name` , REPLACE(`name` , "a", "aa") FROM `teachers`