Some examples of non-standard Oracle SQL syntax features.

Introduction

About a year and a half ago, I passed the OCP Advanced PL / SQL Developer exams, then the specifics of work changed somewhat, and after the standard production use of Oracle, I was developing the architecture of a two-tier client-server system based on Oracle for computational linguistics. Next was the stage of development of the system and the solution of knowledge-intensive tasks based on it, it was necessary to engage in the use of hierarchical queries in solving non-standard tasks and other specific things. The result of the deepening of the specifics was some “subsidence” of the base, which means that it was time to review the materials used to prepare for the exams.

Below are a few non-standard examples of using sql queries. Such examples usually come to mind while looking at the notes, are checked, discussed with other experts and forgotten. This time I wanted to save some of them in a sql file, later the need for comments to each query 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 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 is an 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 meaningless, however, as an expression for sorting, you can, for example, use an expression with the function substr. The very possibility of using expressions is important.

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

 -- №2 select * from dual order by 2 

And so?

 -- №3 select * from dual order by 2+0 

In the first case, we deal with the positional indication of the columns — i.e. referring to column 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 a column number anymore, but sorting by number 2, similar to sorting by line “3X” in query No. 1. Therefore, there will be no error.

Look at the following example. What order of rows will we get as a result of executing 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 strings of the joined sets (as opposed to union), i.e. without order by, we will get the rows in the order specified in the request (union all ensures that the original order of the rows is preserved). Now the order by queue has come, the main question is what is “3” in this case? Since we used double quotes "rather than single quotes", "3" is an alias column. As you know, operations with sets require the use of similar data types, and column names are taken from the first query, because we did not explicitly specify the name of the first column then, by default, it received the name of an expression, that is, “3.” Work with such aliases is shown, for example, in query No. 5 (the main thing is not to forget about the apper-case).

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

 -- №5 select substr(dummy,1,1) from dual order by "SUBSTR(DUMMY,1,1)" 

Repeat the experiment from query number 3 on the sets. What will be the result of the request?

 -- №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: the string "3, X" and 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). It would have happened if it were not for one “but”: in operations with the sets, orders 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:
You can specify the order by clause to 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 returned by the entire compound query. download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998

Therefore, query 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 

Use not in

Certainly, most people who have completed 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 this negative attitude to 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 2: the subquery returns a set of two rows with the values ​​“2” and “null”, the where condition is true, the entire query No. 2 returns 1 row — the standard behavior.

Now consider the difference in the behavior of in and not in, here we need the knowledge of null-arithmetic:
1) Any null arithmetic operation returns 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 the work of NOT IN in the query number 1:
1 NOT IN (2, null) => (1! = 2) AND (1! = Null) => true AND null => null

Thus, the where clause in query No. 1 is converted to null if at least one operand is null, so the whole query No. 1 is approximately equivalent to the following query:

 -- №3 select * from dual where null=null 

Obviously, query number 3 does not return a single row, respectively, query number 1 also does not return a single row.

Implicit type conversion

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

 -- №1 select * from dual where sysdate>'10.09.11'; -- №2 select * from dual where sysdate||''='10.09.11'; 

The answer is both. Why so and how is this 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, Orakl tries to convert the string to the date if the format of the string 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 the query
select * from nls_session_parameters). If the format of the string does not match the default date format, we get an error. In our case, the formats correspond and the string '10 .09.11' is converted to the date 09/10/11 00:00:00, since sysdate = 09/10/11 10:00:00, then query number 1 will return 1 row.

Consider query # 2: where date is concatenated with a string, the result of such an operation is a string, and when the date is converted into a string by implicit data, the data is reduced to the format DD.MM.RR, i.e. we compare the two strings '10 .09.11 'and '10 .09.11'. As a result, the where clause is satisfied and the query returns a single line.

Conclusion

This review does not pretend to fully cover any Oracle functionality; it was created for fun and is intended to demonstrate several instances of non-standard use of the capabilities of the sql syntax.