CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-databasepostgresql
roman
roman

Reputation: 117345

Analog of OUTER APPLY in other RDBMS (not SQL Server)

I'm using SQL Server at work, and I have some nice tricks with OUTER APPLY clause which helps me do not repeat code. For example, if I have a table like this:

create table Transactions
(
    ID bigint identity(1, 1) primary key, [Date] datetime, Amount decimal(29, 2), Amount2 decimal(29, 2)
)

insert into Transactions ([Date], Amount, Amount2)
select getdate(), 100.00, null union all
select getdate(), 25.00, 75.00

and I want to select data from it such as I will have row for each not null amount, I can do query like this:

select
  T.ID,
  T.[Date],
  OA.Amount
from Transactions as T
  outer apply (
      select T.Amount as Amount union all
      select T.Amount2 as Amount
  ) as OA
where OA.Amount is not null

instead of using union:

select
  T.ID,
  T.[Date],
  T.Amount
from Transactions as T
where T.Amount is not null

union all

select
  T.ID,
  T.[Date],
  T.Amount2 as Amount
from Transactions as T
where T.Amount2 is not null

So I wonder - are other RDBMS have such a possibility?

SQL FIDDLE

Upvotes: 1

Views: 2285

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

In Oracle a lateral join is a cartesian join with a result set that is dependent upon the values of the row. No new keyword has been introduced yet (SQLFiddle):

SQL> CREATE OR REPLACE TYPE number_nt AS TABLE OF NUMBER;
  2  /

Type created
SQL> SELECT t.id, t.dt, u.column_value amount
  2    FROM Transactions t
  3   CROSS JOIN TABLE(number_nt(t.amount, t.amount2)) u;

        ID DT                AMOUNT
---------- ----------- ------------
         1 05/06/2013           100
         1 05/06/2013  
         2 05/06/2013            25
         2 05/06/2013            75

Oracle appears to use the LATERAL keyword internally though.

Upvotes: 1

Related Questions