Hello everyone! I am a Team Lead and Senior Oracle Developer, I have been working with OeBS for 12 years and mainly write SQL queries. I would like to tell you how my approach to writing SQL queries has changed during this time.
In the beginning there was a word, or rather a request. Let's say
select name from user where id = 1
It's almost impossible to write such a request. It works equally well in every database I know. And I only know oracle: W But I suspect that in other relational too, everything will be ok.
So what happened? The problems began when there were two tables:
select name from user u, rest r where u.id = 1 and u.id = r.user_id
This code gave me more questions. For example, how should these tables be joined? It would seem that it is easier id = user_id
, but I didn’t like something. In the where clause, I was missing a clear separation between filter conditions and table joins. When the query contained 2 tables, it was still normal, but when the number of tables reached 5, everything fell apart. Looking at the query, I could not immediately understand how the tables were connected and whether some link was missing. And everyone lived fine with it, but I could not. One day, as a young June, I came across ANSI syntax.
select name from user inner join rest on u.id = r.user_id where u.id = 1
, , SQL . , - . . SQL. - . . ANSI , .
select u.name, r.resp_name
from user u
left join resp r on u.id = r.user_id and r.end_date > sysdate
where id = 1
, . , , . . . with.
select resp_q as (
select resp_name, userid
from resp where r.end_date > sysdate)
,main_q as (
select u.name, r.respname
from user u
left join resp_q r on u.id = r.userid
where id = 1)
select * from main_q
, with “”, . : “ . . . , .” . WET, .. , . , . from . , , with , hint MATERIALIZE. . . , .. + . , , 10 , with.
- . , , , - . , . unit , . . 100, 120. ? … , , , . ( ).
select * from document where xxstorno(id) = 'Y'
10 . , - . , . . , , , . , 5-7 , .
with test_case as (
select 10 id, 'Y' storno from dual
union all
select 5 id, 'N' storno from dual)
, run_test as (
select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result
from test_case tc
left join document d on d.id = tc.id)
select * from run_test
, - , . , . , ! , . , . and id = 5--6 7 10 135 1345
in which different values were simply substituted by brute force and what and how it should return with hands looked. Since that day, I have written several developments, and for each of them I have already prepared my own test script. I really liked this style and now I am trying to instill it in my developers. So that they don't have to travel 12 years to write beautiful SQL queries.
As a result, almost nothing new has been happening in the SQL world for many years, however, it is always nice to find opportunities to improve your queries.