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);
    

    idnamedescriptionpricearticulact_timeavailability
    1SlippersSoft100.00TR-752017-01-01 01: 01: 01.01true
    2PillowWhite200.00PR-752017-01-02 02: 02: 02.02true
    3BlanketDown300.00ZR-752017-01-03 03: 03: 03.03true
    4PillowcaseGray400.00AR-752017-01-04 04: 04: 04.04false
    5SheetSilk500.00BR-752017-01-05 05: 05: 05.05false


    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
    SELECT mock.nickname "Прозвище", (CASE WHEN mock.huff THEN 'Да' ELSE 'Нет' END) "Обижается?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
    NicknameOffended?
    SlippersYes
    PillowYes
    BlanketYes
    PillowcaseNot
    SheetNot
    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.

    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
    SELECT name "Имя товара", price "Цена" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)
    Product NamePrice
    Slippers100.00
    Pillow200.00
    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.

    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
    SELECT name "Имя товара", act_time "Точное время" FROM goods WHERE act_time = {ts '2017-01-01 01:01:01.01'}
    Product NameExact time
    Slippers2017-01-01 01: 01: 01.01
    For the literals d and t, everything is similar.
    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)
    idnameavailability
    4Pillowcasefalse
    5Sheetfalse


    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) = ('Наволочка', 400.00, FALSE)
    -- или его аналог
    SELECT * FROM goods WHERE name = 'Наволочка' AND price = 400.00 AND availability = FALSE
    idnamedescriptionpricearticulact_timeavailability
    4PillowcaseGray400.00AR-752017-01-04 04: 04: 04.04false
    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.

    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)
    idname
    4Pillowcase
    5Sheet
    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 ?
    Example comparison with ANY
    SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM goods WHERE availability)
    idname
    2Pillow
    3Blanket
    4Pillowcase
    5Sheet
    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.

    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
    SELECT * FROM goods EXCEPT (SELECT * FROM goods WHERE availability)
    idnamedescriptionpricearticulact_timeact_time
    4PillowcaseGray400.00AR-752017-01-04 04: 04: 04.04false
    5SheetSilk500.00BR-752017-01-05 05: 05: 05.05false
    Actually, data from 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)
    idnamedescriptionpricearticulact_timeact_time
    3BlanketDown300.00ZR-752017-01-03 03: 03: 03.03true
    Actually, the first set of values ​​and the second set intersect.
    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.

    Also popular now: