Rare SQL
Introductory
When you often come across any technology, programming language, standard, a certain picture of their capabilities is formed, the boundaries in which they are used. This can continue for 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 tricks are waiting for you in this article. Anyone interested, welcome to cat.
Nuances
I am often asked, but for whom is this article? 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 something, and on the other, young padawans. But one thing I can say for sure is for the reader who is interested in SQL, who is able to complement his rich picture with small, but very interesting details. This article will not have kilometer pages of sql query, maximum 1, 2 lines and only what is rare in my opinion. But since I want to be completely frank, if you are from sql to 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 simplify our life, I threw a simple data plate on which certain moments will be tested and for brevity, I will give the result of an 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, 'Тапочки', 'Мягкие', 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, 'Подушка', 'Белая', 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, 'Одеяло', 'Пуховое', 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, 'Наволочка', 'Серая', 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, 'Простынка', 'Шелковая', 500.00, 'BR-75', {ts '2017-01-05 05:05:05.05'}, FALSE);
id | name | description | price | articul | act_time | availability |
1 | Slippers | Soft | 100.00 | TR-75 | 2017-01-01 01: 01: 01.01 | true |
2 | Pillow | White | 200.00 | PR-75 | 2017-01-02 02: 02: 02.02 | true |
3 | Blanket | Down | 300.00 | ZR-75 | 2017-01-03 03: 03: 03.03 | true |
4 | Pillowcase | Gray | 400.00 | AR-75 | 2017-01-04 04: 04: 04.04 | false |
5 | Sheet | Silk | 500.00 | BR-75 | 2017-01-05 05: 05: 05.05 | false |
Inquiries
1. Double quotes
And the first thing I have is a simple question: Could you give an example of an sql query using double quotes? Yes, not with single, double?
Double quote example
SELECT name "Имя товара" FROM goods
Product Name |
Slippers |
Pillow |
Blanket |
Pillowcase |
Sheet |
I was very surprised when I saw this for the first time. If you try to change double quotes to single, the result will be completely different !
Single quote example
SELECT name 'Это данные' FROM goods WHERE id = 1
name |
This is data |
It may seem that this is not a very useful example for real development. This is not so for me. Now I actively use it in all my sql-stubs. The bottom line is simple when you return after half a year to a sql query of 40 columns, oh how the name rescues their name. Despite the fact that I did not mention SQL-92, there is a mention of double quotes in the latest edition.
2. Pseudo table. SQL-92
A bit inaccurate from the point of view of terminology, but the essence is simple - the table is the result of a subquery in the FROM section. Perhaps the most famous fact in this article.
Pseudo table
In our example, mock is a pseudo table (sometimes called a virtual table). Naturally, they are not intended to misinterpret the true meaning. An example of this.SELECT mock.nickname "Прозвище", (CASE WHEN mock.huff THEN 'Да' ELSE 'Нет' END) "Обижается?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
Nickname | Offended? |
Slippers | Yes |
Pillow | Yes |
Blanket | Yes |
Pillowcase | Not |
Sheet | Not |
3. The constructor of the data block. SQL-92
It sounds scary, simply because I did not find a good translation or interpretation. And as always, it’s easier to explain with an example:
Example data block constructor
The FROM section uses the VALUES keyword , 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, '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 connectedness of the tables and limitations.SELECT name "Имя товара", price "Цена" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)
Product Name | Price |
Slippers | 100.00 |
Pillow | 200.00 |
4. Time, Date and Time-and-Date
Probably everyone came across inquiries, with the need to indicate the time, date or date-and-time. Many DBMSs support literals t, d, and ts, respectively, for working with these types. But it’s easier to explain with an example:
Example with literal ts
For the literals d and t, everything is similar. SELECT name "Имя товара", act_time "Точное время" FROM goods WHERE act_time = {ts '2017-01-01 01:01:01.01'}
Product Name | Exact time |
Slippers | 2017-01-01 01: 01: 01.01 |
I apologize to the reader for misleading, but all that is said in paragraph 4 does not apply to the SQL language, but refers to the possibility of preprocessing queries in JDBC.
5. Denial. SQL-92
We all know about the NOT operator , but very often forget that it can be applied both to a group of predicates and to a single column:
Negation example
SELECT id, name, availability FROM goods WHERE NOT availability
-- или так
SELECT id, name FROM goods WHERE NOT (id = 1 OR id = 2 OR id = 3)
id | name | availability |
4 | Pillowcase | false |
5 | Sheet | false |
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
As can be seen from the example, the comparison of data blocks similarly compared element by element znachenie_ 1 _block_1 = znachenie_ 1 _block_2, znachenie_ 2 _block_1 = znachenie_ 2 _block_2, znachenie_ 3 _block_1 = znachenie_ 3 _block_2 using AND between them.SELECT * FROM goods WHERE (name, price, availability) = ('Наволочка', 400.00, FALSE)
-- или его аналог
SELECT * FROM goods WHERE name = 'Наволочка' AND price = 400.00 AND availability = FALSE
id | name | description | price | articul | act_time | availability |
4 | Pillowcase | Gray | 400.00 | AR-75 | 2017-01-04 04: 04: 04.04 | false |
7. Comparison operators with ANY, SOME or ALL modifiers. SQL-92
An explanation is required here. But as always, first an example
Comparison Example with ALL
What does ALL mean in this case? And it means that only those rows whose identifiers (in our case these are 4 and 5) satisfy the selection condition are larger than any of the values found in the subquery (1, 2 and 3). 4 is greater than 1 and less than 2 and less than 3. 5 likewise. What happens if we replace ALL with ANY ?SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM goods WHERE availability)
id | name |
4 | Pillowcase |
5 | Sheet |
Example comparison with ANY
What does ANY mean in this case? And it means that only those rows whose identifiers (in our case these are 2, 3, 4, and 5) satisfy the selection condition are greater than at least one of the values found in the subquery (1, 2, and 3). For myself, I associated ALL with AND , and ANY with OR . SOME and ANY analogues among themselves.SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM goods WHERE availability)
id | name |
2 | Pillow |
3 | Blanket |
4 | Pillowcase |
5 | Sheet |
8. Operators of work with inquiries / under inquiries. SQL-92
It is well known that you can combine 2 queries with each other using the UNION or UNION ALL operators . This is often used. But there are 2 more EXCEPT and INTERSECT statements .
EXCEPT example
Actually, data from the second set are excluded from the first set of values.SELECT * FROM goods EXCEPT (SELECT * FROM goods WHERE availability)
id | name | description | price | articul | act_time | act_time |
4 | Pillowcase | Gray | 400.00 | AR-75 | 2017-01-04 04: 04: 04.04 | false |
5 | Sheet | Silk | 500.00 | BR-75 | 2017-01-05 05: 05: 05.05 | false |
INTERSECT example
Actually, the first set of values and the second set intersect. SELECT * FROM goods WHERE id > 2 INTERSECT (SELECT * FROM goods WHERE availability)
id | name | description | price | articul | act_time | act_time |
3 | Blanket | Down | 300.00 | ZR-75 | 2017-01-03 03: 03: 03.03 | true |
That's all, thanks for your attention.
Sources
BNF Grammars for SQL-92, SQL-99 and SQL-2003
SQL Tutorial
Editors
N1. Thanks to streetflush for the constructive criticism. Entered the article with information about what is the standard of the language and what is not.
N2. Point 4 is corrected, with the explanation that ts / d / t is not part of the SQL language. Thank you for your kind attention Melkij.