Some examples of custom SQL syntax features

Introduction

About a year and a half ago, I passed the exams for OCP Advanced PL / SQL Developer, then the specifics of the work changed slightly, and after the standard production use of Oracle, I was developing the architecture of a two-level client-server system based on Oracle for the needs of computer linguistics. Next was the stage of development of the system and the solution of high-tech tasks based on it; I had to deal with the use of hierarchical queries in solving non-standard problems and other specific things. The result of deepening in the specifics was a certain “subsidence” of the base, which means that the time has come to look again at the materials used to prepare for the exams.

Below are some non-standard examples of using sql queries. Such examples usually come to mind while viewing the abstract, are checked, discussed with other specialists and forgotten. This time I wanted to save some of them in the sql file, later the need for comments for each request became clear. So this note appeared.

A few words about the specifics of the order by operator

Do you think there will be an error as a result of executing this request?

--Запрос №1
select * from dual
order by 1+2||dummy

Answer: there will be no error, because the order by specification is defined as follows:
ORDER BY {col (s) | expression | numeric_pos} [ASC | DESC] [NULLS FIRST | LAST];
Where expression - A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression.
For more details, for example, download.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html.

Thus, we sorted the dual table by the expression “3X”, which is pointless, but you can use, for example, the expression to sort with substr function. The ability to use expressions is important.

As we know, the dual table contains one column, whether the following query will return an error:

--Запрос №2
select * from dual
order by 2

And this one?

--Запрос №3
select * from dual
order by 2+0

In the first case, we are dealing with the positional indication of the columns - i.e. by reference to column No. 2, which does not exist, respectively, we get an error.

In the second case, we are dealing with an expression, i.e. this is not the column number, but sorting by the number 2, similar to sorting by the “3X” line in query No. 1. Therefore, there will be no error.

Take a look at the following example. What row order do we get as a result of this query? Will there be a mistake?

--Запрос №4
select 3, dummy from dual
union all
select 2, dummy from dual
order by "3"

Union all does not sort the rows of the union sets (unlike union), i.e. without order by, we get the rows in the order specified in the request (union all guarantees the preservation of the original row order). Now it is the turn of order by, the main question, what is “3” in this case? Since we use double quotes "rather than single quotes," 3 "is an alias of the column. As you know, operations with sets require the use of similar data types, and the column names are taken from the first query, since we did not explicitly specify the name of the first column , then, by default, it received the name of the expression, that is, “3.” Work with such aliases is shown, for example, in request No. 5 (the main thing is not to forget about the app case).

The default sort is always asc, i.e. query results # 4 are sorted in ascending order by the first column. Result: line “2, X”, then “3, X”.

--Запрос №5
select substr(dummy,1,1) from dual
order by "SUBSTR(DUMMY,1,1)" 

Let us repeat the experiment from query No. 3 on sets. What will be the result of the query?

--Запрос №6
select 3, dummy from dual
union all
select 2, dummy from dual
order by 2+0

It may seem that the result of the query will be as follows: line “3, X” then “2, X”. The prerequisites for this are as follows: this order guarantees union all, and the expression 2 + 0 will not affect the sorting of records (as shown in query No. 7). So it would be, if not for one “but”: in operations with sets order by can only be used at the end of a compound query with the names or column numbers of the first query, expressions are not allowed:
For compound queries containing set operators UNION, INTERSECT, MINUS , or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998

Therefore, request number 6 will return an error.

--Запрос №7
select * from
        (select 3, dummy from dual
         union all
         select 2, dummy from dual)
order by 2+0

Using not in

Surely, most people who have gone through various courses remember that the not in operator should be avoided, and similar functionality can be obtained using the in or exists operators. The reason for such a negative attitude towards not in lies in the specifics of his work with null values.

Determine the result of the following query:

--Запрос №1
select * from dual
where 1 not in (select 2 from dual union select null from dual) 

First, consider the following query:

--Запрос №2
select * from dual
where 2 in (select 2 from dual union select null from dual) 

There is nothing unusual in query No. 2: the subquery returns a set of two rows with the values ​​“2” and “null”, the where condition evaluates to true, the entire query No. 2 returns 1 row — standard behavior.

Now consider the difference in the behavior of in and not in, here we need knowledge of null arithmetic:
1) Any arithmetic operations with null return null
2) Boolean operators with null work as follows:

• null AND false = false
• false AND null = false
• null AND true = null
• true AND null = null
• null OR true = true
• true OR null = true
• null OR false = null
• false OR null = null

So far, everything is logical. Now let’s look at how IN works in query # 2:
2 IN (2, null) => (2 = 2) OR (2 = null) => true OR null => true
Consider NOT IN in query # 1:
1 NOT IN (2, null) => (1! = 2) AND (1! = Null) => true AND null => null

Thus, the where condition in query # 1 is converted to null if at least one operand is null, therefore All query # 1 is approximately equivalent to the following query:

--Запрос №3
select * from dual
where null=null

Obviously, query # 3 will not return a single row, respectively, query # 1 will also not return a single row.

Implicit type conversion

The topic of explicit and implicit type conversion is very extensive, therefore, without trying to cover it as a whole, I would like to consider only one example. Let today 10.09.11 10:00:00 and Oracle is configured so that the DD.MM.RR format is recognized by default, which of the queries will return a single line?

--Запрос №1
select * from dual where sysdate>'10.09.11';
--Запрос №2
select * from dual where sysdate||''='10.09.11'; 

The answer is both. Why and how is this generally possible? The answer lies in the mechanism of implicit type conversion.

Consider query # 1: in where we compare the date with the string, in this case Oracle tries to convert the string to date if the string format matches one of the default date formats (the default date format for the session can be viewed in the NLS_DATE_FORMAT parameter by running
select * from nls_session_parameters). If the string format does not correspond to the default date format, then we will get an error. In our case, the formats correspond and the string '10 .09.11 'is converted to the date 10.09.11 00:00:00, because sysdate = 09/10/11 10:00:00, then query No. 1 will return 1 line.

Consider query number 2: in where the date is concatenated with a string, the result of such an operation is a string, and when the date is implicitly converted to a string, data is truncated to the DD.MM.RR format, i.e. we compare the two lines '10 .09.11 'and '10 .09.11'. As a result, the where clause is satisfied, and the query returns one row.

Conclusion

This review does not pretend to provide any coverage of Oracle functionality; it was created for fun and is intended to demonstrate several cases of non-standard use of sql syntax features.