Oracle cross apply: CROSS APPLY and OUTER APPLY in 12c
Содержание
Oracle Correlated inline views: Oracle Correlated inline views: outer, cross apply, join
Ratings
(3)
Most of the time in SQL, you can simply join tables or views to one another to get the result you want. Often you add inline views and scalar subqueries to the mix, and you can soon create relatively complex solutions to many problems. With analytic functions, you really start to rock ‘n’ roll and can solve almost anything.
But it can happen from time to time that you have, for instance, a scalar subquery and wish that it could return multiple columns instead of just a single column. You can make workarounds with object types or string concatenation, but it’s never really elegant nor efficient.
Also from time to time, you would really like, for example, a predicate inside the inline view to reference a value from a table outside the inline view, which is normally not possible. Often the workaround is to select the column you would like a predicate on in the inline view select list and put the predicate in the join on clause instead.
This is often good enough, and the optimizer can often do predicate pushing to automatically do what you actually wanted – but it is not always able to do this, in which case you end up with an inefficient query.
For both those problems, it has been possible since version 12.1 to solve them by correlating the inline view with lateral or apply, enabling you in essence to do your own predicate pushing.
Brewery products and sales
In the application schema of the Good Beer Trading Co, I have a couple of views (shown in Figure 1-1) I can use to illustrate inline view correlation.
Figure 1-1 Two views used in this article to illustrate lateral inline views
It could just as easily have been tables that I used to demonstrate these techniques, so for this article, just think of them as such. The internals of the views will be more relevant in later articles.
View brewery_products shows which beers the Good Beer Trading Co buys from which breweries, while view yearly_sales shows how many bottles of each beer are sold per year.
Joining the two together in Listing 1 on product_id, I can see the yearly sales of those beers that are bought from Balthazar Brauerei.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , ys.yr 6 , ys.yr_qty 7 from brewery_products bp 8 join yearly_sales ys 9 on ys.product_id = bp.product_id 10 where bp.brewery_id = 518 11 order by bp.product_id, ys.yr;
Listing 1 The yearly sales of the three beers from Balthazar Brauerei
This data of 3 years of sales of three beers will be the basis for the examples of this blog:
BREWERY_NAME P_ID PRODUCT_NAME YR YR_QTY Balthazar Brauerei 5310 Monks and Nuns 2016 478 Balthazar Brauerei 5310 Monks and Nuns 2017 582 Balthazar Brauerei 5310 Monks and Nuns 2018 425 Balthazar Brauerei 5430 Hercule Trippel 2016 261 Balthazar Brauerei 5430 Hercule Trippel 2017 344 Balthazar Brauerei 5430 Hercule Trippel 2018 451 Balthazar Brauerei 6520 Der Helle Kumpel 2016 415 Balthazar Brauerei 6520 Der Helle Kumpel 2017 458 Balthazar Brauerei 6520 Der Helle Kumpel 2018 357
At first I’ll use this to show a typical problem.
Scalar subqueries and multiple columns
The task at hand is to show for each of the three beers of Balthazar Brauerei which year the most bottles of that particular beer are sold and how many bottles that were. I can do this with two scalar subqueries in Listing 2.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , ( 6 select ys.yr 7 from yearly_sales ys 8 where ys.product_id = bp.product_id 9 order by ys.yr_qty desc 10 fetch first row only 11 ) as yr 12 , ( 13 select ys.yr_qty 14 from yearly_sales ys 15 where ys.product_id = bp.product_id 16 order by ys.yr_qty desc 17 fetch first row only 18 ) as yr_qty 19 from brewery_products bp 20 where bp.brewery_id = 518 21 order by bp.product_id;
Listing 2 Retrieving two columns from the best-selling year per beer
For the data at hand (where there are no ties between years), it works okay and gives me the desired output:
BREWERY_NAME P_ID PRODUCT_NAME YR YR_QTY Balthazar Brauerei 5310 Monks and Nuns 2017 582 Balthazar Brauerei 5430 Hercule Trippel 2018 451 Balthazar Brauerei 6520 Der Helle Kumpel 2017 458
But there are some issues with this strategy:
- The same data in
yearly_salesis accessed twice.
Had I needed more than two columns, it would have been multiple times. - Since my order by is not unique, my
fetch firstrow will return a random one (well, probably the first it happens to find using whichever access plan it uses, of which I have no control, so in effect, it could be any one) of those rows that have the highestyr_qty. That means in the multiple subqueries, I have no guarantee that the values come from the same row – if I had had a column showing the profit of the beer in that year and a subquery to retrieve this profit, it might show the profit of a different year than the one shown in the yr column of the output.
Had I needed more than two columns, it would have been multiple times.
A classic workaround is to use just a single scalar subquery like in Listing 3.
SQL> select 2 brewery_name 3 , product_id as p_id 4 , product_name 5 , to_number( 6 substr(yr_qty_str, 1, instr(yr_qty_str, ';') - 1) 7 ) as yr 8 , to_number( 9 substr(yr_qty_str, instr(yr_qty_str, ';') + 1) 10 ) as yr_qty 11 from ( 12 select 13 bp.
brewery_name
14 , bp.product_id
15 , bp.product_name
16 , (
17 select ys.yr || ';' || ys.yr_qty
18 from yearly_sales ys
19 where ys.product_id = bp.product_id
20 order by ys.yr_qty desc
21 fetch first row only
22 ) as yr_qty_str
23 from brewery_products bp
24 where bp.brewery_id = 518
25 )
26 order by product_id;Listing 3 Using just a single scalar subquery and value concatenation
The scalar subquery is here in lines 16–22, finding the row I want and then selecting in line 17 a concatenation of the values I am interested in. Then I place the entire thing in an inline view (lines 11–25) and split the concatenated string into individual values again in lines 5–10.
The output of this is exactly the same as Listing 2, so that is all good, right? Well, as you can see, if I need more than two columns, it can quickly become unwieldy code.
If I had been concatenating string values, I would have needed to worry about using a delimiter that didn’t exist in the real data. If I had been concatenating dates and timestamps, I’d need to use yr_qty and to_date / to_timestamp. And what if I had LOB columns or columns of complex types? Then I couldn’t do this at all.
So there are many good reasons to try Listing 4 as an alternative workaround.
SQL> select 2 brewery_name 3 , product_id as p_id 4 , product_name 5 , yr 6 , yr_qty 7 from ( 8 select 9 bp.brewery_name 10 , bp.product_id 11 , bp.product_name 12 , ys.yr 13 , ys.yr_qty 14 , row_number() over ( 15 partition by bp.product_id 16 order by ys.yr_qty desc 17 ) as rn 18 from brewery_products bp 19 join yearly_sales ys 20 on ys.
product_id = bp.product_id
21 where bp.brewery_id = 518
22 )
23 where rn = 1
24 order by product_id;Listing 4
Using analytic function to be able to retrieve all columns if desired
This also gives the exact same output as Listing 2, just without any scalar subqueries at all.
Here I join the two views in lines 18–20 instead of querying yearly_sales in a scalar subquery. But doing that makes it impossible for me to use the fetch first syntax, as I need a row per brewery and fetch first does not support a partition clause.
Instead I use the row_number analytic function in lines 14–17 to assign consecutive numbers 1, 2, 3 … in descending order of yr_qty, in effect giving the row with the highest yr_qty the value 1 in rn. This happens for each beer because of the partition by in line 15, so there will be a row with rn=1 for each beer.
These rows I keep with the where clause in line 23.
The effect of this is that I can query as many columns from the yearly_sales view as I want – here I query two columns in lines 12–13. These can then be used directly in the outer query as well in lines 5–6. No concatenation needed, each column is available directly, no matter the datatype.
This is a much nicer workaround than Listing 3, so isn’t this good enough? In this case it is fine, but the alternative with correlated inline views can be more flexible for some situations.
Correlating inline view
Listing 5 is yet another way to produce the exact same output as Listing 2, just this time by correlating an inline view.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , top_ys.yr 6 , top_ys.yr_qty 7 from brewery_products bp 8 cross join lateral( 9 select 10 ys.
yr
11 , ys.yr_qty
12 from yearly_sales ys
13 where ys.product_id = bp.product_id
14 order by ys.yr_qty desc
15 fetch first row only
16 ) top_ys
17 where bp.brewery_id = 518
18 order by bp.product_id;Listing 5 Achieving the same with a lateral inline view
The way this works is as follows:
- I do not join
brewery_productstoyearly_salesdirectly; instead I join to the inline viewtop_ysin line 8. - The inline view in lines 9–15 queries
yearly_salesand uses thefetch firstrow to find the row of the year with the highest sales. But it is not executed for all beers finding a single row with the best-selling year across all beers, for line 13 correlates theyearly_salesto thebrewery_productsonproduct_id. - Line 13 would normally raise an error, since it would not make sense in the usual joining to an inline view. But I placed the keyword lateral in front of the inline view in line 8, which tells the database that I want a correlation here, so it should execute the inline view once for each row of the correlated outer row source – in this case
brewery_products. That means that for each beer, there will be executed an individualfetch firstrow query, almost as if it were a scalar subquery. - I then use
cross joinin line 8 to do the actual joining, which simply is because I need no on clause in this case. I have all the correlation I need in line 13, so I need not use aninnerorouter join.
But I placed the keyword lateral in front of the inline view in line 8, which tells the database that I want a correlation here, so it should execute the inline view once for each row of the correlated outer row source – in this case
Using this lateral inline view enables me to get it executed for each beer like a scalar subquery, but to have individual columns queried like in Listing 4.
You might wonder about the cross join and say, “This isn’t a Cartesian product, is it?”
Consider if I had used the traditional join style with a comma-separated list of tables and views and all join predicates in the where clause and no on clauses.
In that join style, Cartesian joins happen if you have no join predicate at all between two tables/views (sometimes that can happen by accident – a classic error that can be hard to catch).
If I had written Listing 5 with traditional style joins, line 8 would have looked like this:
... 7 from brewery_products bp 8 , lateral( 9 select ...
And with no join predicates in the where clause, it does exactly the same that the cross join does. But because of the lateral clause, it becomes a “Cartesian” join between each row of brewery_products and each output row set of the correlated inline view as it is executed for each beer. So for each beer, it actually is a Cartesian product (think of it as “partitioned Cartesian”), but the net effect is that the total result looks like a correlated join and doesn’t appear Cartesian at all. Just don’t let the cross join syntax confuse you.
I could have chosen to avoid the confusion of the cross join by using a regular inner join like this:
.
..
7 from brewery_products bp
8 join lateral(
9 select
...
16 ) top_ys
17 on 1=1
18 where bp.brewery_id = 518
...Since the correlation happens inside the lateral inline view, I can simply let the on clause be always true. The effect is exactly the same.
It might be that you feel that both cross join and the on 1=1 methods really do not state clearly what happens – both syntaxes can be considered a bit “cludgy” if you will. Then perhaps you might like the alternative syntax cross apply instead as in Listing 6.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , top_ys.yr 6 , top_ys.yr_qty 7 from brewery_products bp 8 cross apply( 9 select 10 ys.yr 11 , ys.yr_qty 12 from yearly_sales ys 13 where ys.product_id = bp.product_id 14 order by ys.
yr_qty desc
15 fetch first row only
16 ) top_ys
17 where bp.brewery_id = 518
18 order by bp.product_id;The output is the same as Listing 2 like the previous listings, but this time I am using neither lateral nor join, but the keywords cross apply in line 8. What this means is that for each row in brewery_products, the inline view will be applied. And when I use apply, I am allowed to correlate the inline view with the predicate in line 13, just like using lateral. Behind the scenes, the database does exactly the same as a lateral inline view; it is just a case of which syntax you prefer.
The keyword cross distinguishes it from the variant outer apply, which I’ll show in a moment. Here cross is to be thought of as “partitioned Cartesian” as I discussed in the preceding text.
Note You can use the
cross applyandouter applynot only for inline views but also for calling table functions (pipelined or not) in a correlated manner.
This would require a longer syntax if you use lateral. Probably you won’t see it used often on table functions, as the table functions in Oracle can be used as a correlated row source in joins anyway, so it is rarely necessary to use apply, though sometimes it can improve readability.Outer joining correlated inline view
So far my uses of lateral and apply have only been of the cross variety. That means that in fact I have been cheating a little – it is not really the same as using scalar subqueries. It is only because of having sales data for all the beers that Listings 1-2 to 1-6 all had the same output.
If a scalar subquery finds nothing, the value in that output column of the brewery_products row will be null – but if a cross join lateral or cross apply inline view finds no rows, then the brewery_products row will not be in the output at all.
What I need to really emulate the output of the scalar subquery method is a functionality like an outer join, which I do in Listing 7. In this listing, I still find the top year and quantity for each beer, but only of those yearly sales that were less than 400.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , top_ys.yr 6 , top_ys.yr_qty 7 from brewery_products bp 8 outer apply( 9 select 10 ys.yr 11 , ys.yr_qty 12 from yearly_sales ys 13 where ys.product_id = bp.product_id 14 and ys.yr_qty < 400 15 order by ys.yr_qty desc 16 fetch first row only 17 ) top_ys 18 where bp.brewery_id = 518 19 order by bp.product_id;
Listing 7 Using outer apply when you need outer join functionality
In line 14, I make the inline view query only years that had sales of less than 400 bottles.
And then in line 8, I changed cross apply to outer apply, giving me this result:
BREWERY_NAME P_ID PRODUCT_NAME YR YR_QTY Balthazar Brauerei 5310 Monks and Nuns Balthazar Brauerei 5430 Hercule Trippel 2017 344 Balthazar Brauerei 6520 Der Helle Kumpel 2018 357
f I had been using cross apply in line 8, I would only have seen the last two rows in the output.
So outer apply is more correct to use if you want an output that is completely identical to the scalar subquery method. But just like you don’t want to use regular outer joins unnecessarily, you should use cross apply if you know for a fact that rows always will be returned.
An outer apply is the same as a left outer join lateral with an on 1=1 join clause, so outer apply cannot support right correlation, only left.
There are cases where an outer join lateral is more flexible than outer apply, since you can actually use the on clause sensibly, like in Listing 8.
SQL> select 2 bp.brewery_name 3 , bp.product_id as p_id 4 , bp.product_name 5 , top_ys.yr 6 , top_ys.yr_qty 7 from brewery_products bp 8 left outer join lateral( 9 select 10 ys.yr 11 , ys.yr_qty 12 from yearly_sales ys 13 where ys.product_id = bp.product_id 14 order by ys.yr_qty desc 15 fetch first row only 16 ) top_ys 17 on top_ys.yr_qty < 500 18 where bp.brewery_id = 518 19 order by bp.product_id;
Listing 8 Outer join with the lateral keyword
Since I use lateral in the left outer join in line 8, the inline view is executed once for every beer, finding the best-selling year and quantity, just like most of the examples in the article. But in the on clause in line 17, I filter, so I only output a top_ys row if the quantity is less than 500. It gives me this output, which is almost but not quite the same as the output of Listings 1-2 to 1-6:
BREWERY_NAME P_ID PRODUCT_NAME YR YR_QTY Balthazar Brauerei 5310 Monks and Nuns Balthazar Brauerei 5430 Hercule Trippel 2018 451 Balthazar Brauerei 6520 Der Helle Kumpel 2017 458
Normally the on clause is for the joining of the two tables (or views) and shouldn’t really contain a filter predicate.
But in this case, it is exactly because I do the filtering in the on clause that I get the preceding result. Filtering in different places would solve different problems:
- If the filter predicate is inside the inline view (like Listing 7), the problem solved is “For each beer show me the best-selling year and quantity out of those years that sold less than 400 bottles.”
- If the filter predicate is in the on clause (like Listing 8), the problem solved is “For each beer show me the best-selling year and quantity if that year sold less than 500 bottles.”
- If the filter predicate had been in the where clause right after line 18, the problem solved would have been “For each beer where the best-selling year sold less than 500 bottles, show me the best-selling year and quantity.” (And then it shouldn’t be an
outer join, but just aninnerorcross join.)
In all, lateral and apply (both in cross and outer versions) have several uses that, though they might be solvable by various other workarounds, can be quite nice and efficient.
Typically you don’t want to use it if the best access path would be to build the entire results of the inline view first and then hash or merge the join with the outer table (for such a case, Listing 4 is often a better solution). But if the best path would be to do the outer table and then nested loop join to the inline view, lateral and apply are very nice methods.
Lessons learned
In this article I’ve shown you some workarounds to some problems and then given you examples of how to solve the same using correlated inline views, so you now know about
- Using keyword lateral to enable doing a left correlation inside an inline view
- Distinguishing between cross and outer versions of joining to the lateral inline view
- Applying the
cross applyorouter applyas alternative syntax to achieve a left correlation - Deciding whether a correlated inline view or a regular inline view with analytic functions can solve a problem most efficiently
Being able to correlate inline views can be handy for several situations in your application development.
Вас заинтересует / Intresting for you:
Why Oracle DBAs learn PL/SQL a…
1405 views
Андрей Волков
Wed, 12 Sep 2018, 14:43:12
Creating Cross-Tab Reports Usi…
886 views
Ирина Светлова
Mon, 05 Jul 2021, 14:44:18
Introduction to PL/SQL
1957 views
Antoniy
Wed, 12 Sep 2018, 15:18:13
Finding Rows That Satisfy Mult…
634 views
Денис
Tue, 13 Jul 2021, 20:26:31
Oracle database
SQL (EN)
SQL.
RU | Чудесный оператор CROSS APPLY
Сегодня я хочу рассказать более подробно об операторе APPLY, а конкретнее о его типе CROSS APPLY. Этот оператор появился впервые в SQL Server 2005, но к сожалению многие так и не научились им пользоваться, возможно это из-за того, что в BOL (SQL Server Books Online) этот оператор плохо описан и имеет очень «сухие» примеры его использования. В этой статье я покажу несколько интересных демонстраций, где этот оператор может пригодиться.
Основная фича оператора заключается в том, что APPLY позволяет вызывать табличную функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Именно этот пример есть в BOL.
Оператор CROSS APPLY возвращает только строки из внешней таблицы, которые создает результирующий набор из возвращающего табличное значение функции. Оператор OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.
Для демонстрации некоторых фич оператора APPLY, создадим тестовую БД и пару таблиц:
use master
go
--Создаю тестовую БД,
--для демонстрации возможностей оператора CrossApply
if db_id ( 'CrossApplyDemo' ) is not null
drop database CrossApplyDemo
go
create database CrossApplyDemo
go
use CrossApplyDemo
go
--Создаю тестовую таблицу стран
if object_id ( 'dbo.Countries', 'U' ) is not null
drop table dbo.Countries
go
create table dbo.Countries ( CountryID int, Country nvarchar(255) )
go
--Добавим 5 стран, используя синтаксис SQL Server 2008
insert into dbo.Countries ( CountryID, Country )
values ( 1, N'Россия' ), ( 2, N'США' ), ( 3, N'Германия' )
, ( 4, N'Франция' ), ( 5, N'Италия' ), ( 6, N'Испания' )
go
--Создаю тестовую таблицу городов
if object_id ( 'dbo.Cities', 'U' ) is not null
drop table dbo.Cities
go
create table dbo. Cities ( CityID int, CountryID int, City nvarchar(255) )
go
--Добавим несколько городов
insert into dbo.Cities ( CityID, CountryID, City )
values ( 1, 1, N'Москва' ), ( 2, 1, N'Санкт-Петербург' ), ( 3, 1, N'Екатеринбург' )
, ( 4, 1, N'Новосибирс' ), ( 5, 1, N'Самара' ), ( 6, 2, N'Чикаго' )
, ( 7, 2, N'Вашингтон' ), ( 8, 2, N'Атланта' ), ( 9, 3, N'Берлин' )
, ( 10, 3, N'Мюнхен' ), ( 11, 3, N'Гамбург' ), ( 12, 3, N'Бремен' )
, ( 13, 4, N'Париж' ), ( 14, 4, N'Лион' ), ( 15, 5, N'Милан' )
go 
Cities ( CityID int, CountryID int, City nvarchar(255) )
go
--Добавим несколько городов
insert into dbo.Cities ( CityID, CountryID, City )
values ( 1, 1, N'Москва' ), ( 2, 1, N'Санкт-Петербург' ), ( 3, 1, N'Екатеринбург' )
, ( 4, 1, N'Новосибирс' ), ( 5, 1, N'Самара' ), ( 6, 2, N'Чикаго' )
, ( 7, 2, N'Вашингтон' ), ( 8, 2, N'Атланта' ), ( 9, 3, N'Берлин' )
, ( 10, 3, N'Мюнхен' ), ( 11, 3, N'Гамбург' ), ( 12, 3, N'Бремен' )
, ( 13, 4, N'Париж' ), ( 14, 4, N'Лион' ), ( 15, 5, N'Милан' )
go
Основное назначение оператора — это работа с табличными функциями. Создадим функцию, которая возвращает список городов по входному параметру @CountyID:
--Табличная функция create function dbo.GetCities( @CountyID int ) returns table as return ( select CityID, City from dbo.Cities where CountryID = @CountyID ) go
Результат вызова функции представлен ниже:
select * from dbo.
GetCities (1)
------------------------------
--Результат:
------------------------------
--CityID City
------------- ---------------------
--1 Москва
--2 Санкт-Петербург
--3 Екатеринбург
--4 Новосибирс
--5 Самара
А теперь с помощью оператора APPLY я выведу список городов для каждой страны из таблицы Countries
select * from dbo.Countries c cross apply dbo.GetCities ( c.CountryID ) ap ------------------------------ --Результат: ------------------------------ --CountryID Country CityID City ------------- --------------- ----------- --------------- --1 Россия 1 Москва --1 Россия 2 Санкт-Петербург --1 Россия 3 Екатеринбург --1 Россия 4 Новосибирс --1 Россия 5 Самара --2 США 6 Чикаго --2 США 7 Вашингтон --2 США 8 Атланта --3 Германия 9 Берлин --3 Германия 10 Мюнхен --3 Германия 11 Гамбург --3 Германия 12 Бремен --4 Франция 13 Париж --4 Франция 14 Лион --5 Италия 15 Милан
Очень удобно, но подобный пример описан в БОЛ, а я покажу, как можно ещё использовать оператор CROSS APPLY.
Часто бывает задача вывести несколько первых значений из группы. Например, как вывести по 3 города для каждой страны, отсортированных по алфавиту!? С помощью оператора APPLY это сделать достаточно легко:
select * from dbo.Countries c
cross apply ( select top 3 City from dbo.Cities
where CountryID = c.CountryID order by City
) ap
------------------------------
--Результат:
------------------------------
--CountryID Country City
------------- --------------- ---------------
--1 Россия Екатеринбург
--1 Россия Москва
--1 Россия Новосибирс
--2 США Атланта
--2 США Вашингтон
--2 США Чикаго
--3 Германия Берлин
--3 Германия Бремен
--3 Германия Гамбург
--4 Франция Лион
--4 Франция Париж
--5 Италия Милан
Теперь попробуем ещё более усложнить наш запрос.
Выведем первую букву каждого из 3х городов каждой страны и общее количество этих букв среди ВСЕХ городов текущей страны:
select * from dbo.Countries c
cross apply ( select top 3 City from dbo.Cities where CountryID = c.CountryID order by City
) ap
cross apply ( select l 'Letter', sum (cl) 'LetterCount'
from
(select left( ap.City, 1 ) l,
len( City ) - len ( replace ( City, left( ap.City, 1 ) ,'' ) ) cl
from dbo.Cities where CountryID = c.CountryID
) t
group by l
) apLetters
------------------------------
--Результат:
------------------------------
--CountryID Country City Letter LetterCount
------------- --------------- --------------- ------ -----------
--1 Россия Екатеринбург Е 4
--1 Россия Москва М 2
--1 Россия Новосибирс Н 3
--2 США Атланта А 5
--2 США Вашингтон В 1
--2 США Чикаго Ч 1
--3 Германия Берлин Б 3
--3 Германия Бремен Б 3
--3 Германия Гамбург Г 2
--4 Франция Лион Л 1
--4 Франция Париж П 1
--5 Италия Милан М 1
Кроме того оператор может пригодиться для распарсивания значений из строки с разделителем.
Предположим, что у нас есть таблица-помойка в которой хранится информация о каких-то людях: фамилия, Имя, Отчество, год рождения и город в котором он проживает. Все параметры разделены запятой, при этом в строке могут храниться не все эти значения.
--Создаю ещё одну тестовую таблицу if object_id ( 'dbo.TestTable', 'U' ) is not null drop table dbo.TestTable go create table dbo.TestTable ( val nvarchar(1024) ) insert into dbo.TestTable select N'Иванов,Иван,Иванович,1980,Москва' union all select N'Петров,,,1988' union all select N'Сидоров,Иван,Юрьевич,,Саратов' union all select N',Степан,,,Екатеринбург' union all select N'Кузнецов,,Иванович' union all select N'Путин' select * from dbo.TestTable ------------------------------ --Результат: ------------------------------ --val ----------------------------------- --Иванов,Иван,Иванович,1980,Москва --Петров,,,1988 --Сидоров,Иван,Юрьевич,,Саратов --,Степан,,,Екатеринбург --Кузнецов,,Иванович --Путин
Задача: вытащить каждое значение в отдельную колонку, своего рода попытка нормализации.
Вариантов для распарсивания этих строк много, но мы попробуем это сделать с помощью оператора CROSS APPLY. Для начала мы дополним каждую строку несколькими запятыми в конце строки, а именно 5 (по максимальному кол-ву параметров в строке):
select string from dbo.TestTable cross apply ( select string = val + ',,,,,' ) f1 ------------------------------ --Результат: ------------------------------ --string ----------------------------------- --Иванов,Иван,Иванович,1980,Москва,,,,, --Петров,,,1988,,,,, --Сидоров,Иван,Юрьевич,,Саратов,,,,, --,Степан,,,Екатеринбург,,,,, --Кузнецов,,Иванович,,,,, --Путин,,,,,
А теперь объясню, как это нам поможет. Дополнив строку запятыми мы можем однозначно вытаскивать значения, делать мы это будем с помощью (опять же) CROSS APPLY и строковой функции CHARINDEX. Для окончательного разрезания строки, необходимо получить позицию (порядковый номер в строке) каждой запятой:
select p1, p2, p3, p4, p5 from dbo.
TestTable
cross apply ( select string = val + ',,,,,' ) f1
cross apply ( select p1 = charindex( ',', string ) ) ap1
cross apply ( select p2 = charindex( ',', string, p1 + 1 ) ) ap2
cross apply ( select p3 = charindex( ',', string, p2 + 1 ) ) ap3
cross apply ( select p4 = charindex( ',', string, p3 + 1 ) ) ap4
cross apply ( select p5 = charindex( ',', string, p4 + 1 ) ) ap5
------------------------------
--Результат:
------------------------------
--string
-----------------------------------
--p1 p2 p3 p4 p5
------------- ----------- ----------- ----------- -----------
--7 12 21 26 33
--7 8 9 14 15
--8 13 21 22 30
--1 8 9 10 23
--9 10 19 20 21
--6 7 8 9 10
Теперь у нас есть все, для того, чтобы разделить нашу таблицу с одной колонкой на таблицу, где каждое значение хранится в отдельной колонке.
И опять же с помощью CROSS APPLY:
select NewTable.*
from dbo.TestTable
cross apply ( select string = val + ',,,,,' ) f1
cross apply ( select p1 = charindex( ',', string ) ) ap1
cross apply ( select p2 = charindex( ',', string, p1 + 1 ) ) ap2
cross apply ( select p3 = charindex( ',', string, p2 + 1 ) ) ap3
cross apply ( select p4 = charindex( ',', string, p3 + 1 ) ) ap4
cross apply ( select p5 = charindex( ',', string, p4 + 1 ) ) ap5
cross apply ( select LastName = substring( string, 1, p1-1 )
, MiddleName = substring( string, p1+1, p2-p1-1 )
, FirstName = substring( string, p2+1, p3-p2-1 )
, Year = substring( string, p3+1, p4-p3-1 )
, City = substring( string, p4+1, p5-p4-1 )
) NewTable
В последнем, на сегодня, примере использования оператора CROSS APPLY попробуем разделить строки на отдельные слова и посчитаем их общее количество, вот тестовые данные:
declare @t table ( Message varchar(255)) insert into @t select 'Киев' union all select 'Киев Моссква' union all select 'Киев Моссква Екатеринбург' union all select 'Лондон Екатеринбург Донецк' union all select 'Моссква Самара Саратов Самара' union all select 'Киев Моссква Киев Воронеж' select * from @t ------------------------------ --Результат: ------------------------------ --Message -------------------------------- --Киев --Киев Моссква --Киев Моссква Екатеринбург --Лондон Екатеринбург Донецк --Моссква Самара Саратов Самара --Киев Моссква Киев Воронеж
Ну и сам запрос, подсчитывающий сколько каждый из городов встречается в этой таблице:
select Word, count(*) cl from @t join master.
.spt_values on substring( ' '+Message, Number, 1 ) = ' '
and Number 0
group by Word
------------------------------
--Результат:
------------------------------
--Word cl
---------------------- -----------
--Воронеж 1
--Донецк 1
--Екатеринбург 2
--Киев 5
--Лондон 1
--Моссква 4
--Самара 2
--Саратов 1
Вы можете сказать, что почти все примеры, которые я рассмотрел в этой статье, можно реализовать и без оператора CROSS APPLY. Я, конечно же с вами соглашусь, но цель была показать на сколько оператор удобен в использовании и думаю этот топик найдет своих читателей.
ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ и ВНЕШНЕЕ ПРИМЕНЕНИЕ в 12c
Привет,
Я узнал, что у нас есть ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ и ВНЕШНЕЕ ПРИМЕНЕНИЕ в 12c.
Однако я вижу, что результаты одинаковы для CROSS APPLY и INNER JOIN, OUTER APPLY и LEFT/RIGHT OUTER JOIN.
Итак, если INNER JOIN и LEFT/RIGHT OUTER JOIN являются стандартом ANSI и дают те же результаты, что и CROSS APPLY и OUTER APPLY, то почему эти два метода были введены в 12c и их цель одна и та же.
Синтаксис и использование понятны.
Не могли бы вы помочь в понимании цели.
С уважением,
Сунил Кумар Нути.
Amazing Fan of Concept KISS Series (Keep it Simple SQL) 🙂
Чтобы ответить на вопрос «почему они существуют», Алекс Кех, менеджер по программированию поставщиков .NET, сказал следующее:
Около десяти лет назад Microsoft представила новую технологию под названием Language Integrated Query (LINQ). LINQ позволял вам составить запрос один раз и заставить его работать с любым источником данных, включая Oracle DB. Для этого LINQ создает дерево выражений. Каждый провайдер источника данных берет дерево выражений и преобразует его в собственный SQL БД.
В большинстве случаев LINQ создает деревья выражений, которые можно преобразовать в стандартный SQL. Один из случаев, когда он генерирует ключевые слова CROSS APPLY. Это выражение поддерживается только SQL Server, но не Oracle.
…
Таким образом, мы поддерживаем CROSS APPLY для того, чтобы:
1) гарантировать, что клиенты LINQ могут использовать Oracle без возникновения ошибки, если этот SQL сгенерирован
2) помочь клиентам перенести свой SQL с SQL Server на Oracle
Так что на самом деле это просто для улучшения совместимости с другими системами. Если вы работаете над собственным приложением Oracle, вы, вероятно, можете их игнорировать.
Тем не менее, могут быть случаи, когда их использование может упростить понимание и/или ускорить выполнение запросов.
Например, рассмотрите следующий вопрос:
«Покажите мне двух самых высокооплачиваемых сотрудников, нанятых после 1 января 2007 г. в каждом отделе, а также сведения об отделе».
Теперь вы можете записать это с помощью объединения, например:
выбрать * из (
выберите д.*, имя, фамилию, зарплату,
row_number() over (раздел по d.department_id, порядок по описанию зарплаты) rn
от отдела кадров d
присоединиться к hr.employees e
на e.department_id = d.department_id
где e.hire_date >= date'2007-01-01'
)
где р <= 2
упорядочить по ИД_отдела, описанию зарплаты;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID FIRST_NAME LAST_NAME SALARY RN
30 Закупки 114 1 700 Карен Кольменарес 2 500 1
50 Шиппинг 121 1 500 Кевин Мургос 5 800 1
50 Доставка 121 1 500 Энтони Кабрио 3 000 2
60 IT 103 1 400 Брюс Эрнст 6 000 1
60 IT 103 1400 Диана Лоренц 4200 2
80 Продажи 145 2 500 Джеральд Камбро 11 000 1
80 Продажи 145 2 500 Элени Злоткей 10 500 2
100 Финансы 108 1,700 Луис Попп 6,900 1
Или, используя кросс-применение, вместо этого вы можете сделать следующее:
выберите d.*, имя_имя, фамилия_имя, зарплата от отдела кадров d перекрестное применение ( выберите имя, фамилию, зарплату от hr.
employees e
где e.department_id = d.department_id
и e.hire_date >= date'2007-01-01'
заказать по e.salary desc
получить только первые 2 строки
) е
заказ на 1, оклад по убыванию;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID FIRST_NAME LAST_NAME ЗАРПЛАТА
30 Закупки 114 1 700 Карен Кольменарес 2 500
50 Шиппинг 121 1 500 Кевин Мургос 5 800
50 Доставка 121 1 500 Энтони Кабрио 3 000
60 IT 103 1400 Брюс Эрнст 6000
60 IT 103 1400 Диана Лоренц 4200
80 Продажи 145 2 500 Джеральд Камбро 11 000
80 Продажи 145 2 500 Элени Злоткей 10 500
100 Финансы 108 1,700 Луис Попп 6,900
Лично я думаю, что запрос перекрестного применения немного легче понять. Но это сводится к личным предпочтениям и знаниям перекрестного применения! 😉
См. также:
https://explainextended.com/2009/07/16/inner-join-vs-cross-apply/
https://oracle-base.com/articles/12c/lateral- inline-views-cross-apply-and-outer-apply-joins-12cr1#cross-apply-join
sql server - есть ли альтернатива ВНЕШНЕМУ ПРИМЕНЕНИЮ в Oracle?
спросил
Изменено
5 лет, 8 месяцев назад
Просмотрено
11 тысяч раз
В следующем примере я передаю tbA.
ID в запрос tbC . В этом случае я использовал оператор OUTER APPLY для SqlServer.
ВЫБОР ... FROM (SELECT ID FROM TableA...) tbA ВНЕШНЕЕ ПРИМЕНЕНИЕ (ВЫБРАТЬ... ИЗ TableB tbB, ГДЕ tbA.ID = tbB.ID) tbC ...
В Oracle нет оператора OUTER APPLY. Итак, как я могу передать значение (tbA.ID) из левого запроса в правый запрос (tbC) соединения без изменения структуры моего запроса?
Есть ли альтернатива ВНЕШНЕМУ ПРИМЕНЕНИЮ в Oracle?
- sql-сервер
- оракул
- внешнее применение
5
SQL Servers внешнее применение аналогично стандартам SQL боковой . Oracle поддерживает lateral , начиная с 12c(*).
Вместо external apply вы должны использовать left join lateral в стандартном SQL или cross join lateral , если вы хотите опустить предложения ON / USING .