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. So it can continue for a long time, until you see examples that expand the solidified 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 under 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 hand, young Padawans. But I can definitely say one thing - for the reader who is interested in SQL, who is able to complement his rich picture with small, but very interesting details. In this article there will be no kilometer pages of the sql query, a maximum of 1, 2 lines and only that which is rarely seen in my opinion. But since I want to be completely honest, 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 put a simple data plate on which certain moments and for the sake of brevity will be tested, 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, '', '', 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
one	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	A blanket	Downy	300.00	ZR-75	2017-01-03 03: 03: 03.03	true
four	Pillowcase	Gray	400.00	AR-75	2017-01-04 04: 04: 04.04	false
five	Bed sheet	Silk	500.00	BR-75	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 an sql query using double quotes? Yes, not with single, double?

Double quotes example

 SELECT name " " FROM goods

Item Name

Slippers

Pillow

A blanket

Pillowcase

Bed sheet

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

Single Quotes Example

 SELECT name ' ' FROM goods WHERE id = 1

name

This is the data

It may seem that this is not a very useful example for real development. For me, this is not the case. Now I actively use it in all of my sql blanks. The essence is simple, when you come back in half a year to a sql query of 40 columns, oh, how the name of our Native rescues. Despite the fact that I did not indicate about SQL-92, in the latest edition there is a mention of double quotes.
')

2. Pseudo table. SQL-92

A little is not accurate, in terms of terminology, but the essence is simple - the table is the resulting subquery in the FROM section. Perhaps the most famous fact in this article.

Pseudo table

 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
A blanket	Yes
Pillowcase	Not
Bed sheet	Not

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

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, the example is easier to explain:

Example of data block constructor

 SELECT name " ", price "" FROM (VALUES ('', 100.00), ('', 200.00)) AS goods(name, price)

Item Name	Price
Slippers	100.00
Pillow	200.00

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

4. Time, Date and Time-and-Date

Probably everyone faced in the requests, with the need to specify the time, date or date-and-time. Many DBMSs support literals t, d, and ts, respectively, for working with these types. But it is easier to explain with an example:

Ts literal example

 SELECT name " ", act_time " " FROM goods WHERE act_time = {ts '2017-01-01 01:01:01.01'}

Item Name	Exact time
Slippers	2017-01-01 01: 01: 01.01

For d and t literals, everything is the same.
I apologize to the reader for misleadingly, but all that said in clause 4 does not apply to the SQL language, but to the ability to preprocess queries in JDBC.

5. Denial. SQL-92

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

Negative 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
four	Pillowcase	false
five	Bed 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 data block comparison

 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
four	Pillowcase	Gray	400.00	AR-75	2017-01-04 04: 04: 04.04	false

As can be seen from the example, the comparison of data blocks is similar to the comparison of elementwise value_ 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

An explanation is required here. But as always, first an example

Comparison Example with ALL

 SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM goods WHERE availability)

id	name
four	Pillowcase
five	Bed sheet

What does ALL mean in this case? And it means that only those rows whose identifiers (4 and 5 in our case) are greater than any of the values found in the subquery (1, 2 and 3) satisfy the sampling condition. 4 more than 1 and more than 2 and more than 3. 5 similarly. What happens if we replace ALL with ANY ?

Comparison Example with ANY

 SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM goods WHERE availability)

id	name
2	Pillow
3	A blanket
four	Pillowcase
five	Bed sheet

What does ANY mean in this case? And it means that only those rows whose identifiers (in our case it’s 2, 3, 4 and 5) satisfy the condition of the sample are more 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.

8. Operators of work with requests / under requests. SQL-92

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

EXCEPT Example

 SELECT * FROM goods EXCEPT (SELECT * FROM goods WHERE availability)

id	name	description	price	articul	act_time	act_time
four	Pillowcase	Gray	400.00	AR-75	2017-01-04 04: 04: 04.04	false
five	Bed sheet	Silk	500.00	BR-75	2017-01-05 05: 05: 05.05	false

Actually, the data of the second set are excluded from the first set of values.

INTERSECT example

 SELECT * FROM goods WHERE id > 2 INTERSECT (SELECT * FROM goods WHERE availability)

id	name	description	price	articul	act_time	act_time
3	A blanket	Downy	300.00	ZR-75	2017-01-03 03: 03: 03.03	true

Actually, the intersection of the first set of values and the second set.
That's all, thank you for your attention.

Sources

BNF Grammars for SQL-92, SQL-99 and SQL-2003
SQL Tutorial

Revision

N1. Thank you streetflush for constructive criticism. Introduced an article about what is a language standard and what is not.
N2. Clause 4 has been fixed, with the explanation that ts / d / t is not part of the SQL language. Thanks for the attentiveness Melkij.

Source: https://habr.com/ru/post/321504/

All Articles