I am looking for the best alternatives on mapping Cross APPLY to SF.
Something like:
select department_name, employee_id, employee_name
from departments d
cross apply (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
The ANSI SQL equivalent of CROSS APPLY is JOIN LATERAL:
select department_name, employee_id, employee_name
from departments d
join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
Output:
and for OUTER APPLY is LEFT JOIN LATERAL () ON TRUE:
select department_name, employee_id, employee_name
from departments d
left join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id) ON TRUE
order by 1, 2, 3;
Output:
For source data:
CREATE OR REPLACE TABLE departments(department_id INT, department_name TEXT,
deparment_location TEXT)
AS
SELECT 1, 'HR', 'London' UNION
SELECT 2, 'SALES', 'Berlin' UNION
SELECT 3, 'RESEARCH', 'Paris';
CREATE OR REPLACE TABLE employees(employee_id INT, employee_name TEXT,
salary INT, department_id INT)
AS
SELECT 100, 'John', 2000, 1 UNION
SELECT 101, 'Anna', 4000, 2;
Related: CROSS/OUTER APPLY in MySQL
What I have seen is that the same results can be achieved with a CROSS JOIN LATERAL
using this testdata from here:
create table departments (
department_id number(2) ,
department_name varchar2(14),
location varchar2(13)
);
insert into departments values (10,'ACCOUNTING','NEW YORK');
insert into departments values (20,'RESEARCH','DALLAS');
insert into departments values (30,'SALES','CHICAGO');
insert into departments values (40,'OPERATIONS','BOSTON');
create table employees (
employee_id number(4) ,
employee_name varchar2(10),
job varchar2(9),
manager_id number(4),
hiredate date,
salary number(7,2),
commission number(7,2),
department_id number(2)
);
insert into employees values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into employees values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into employees values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into employees values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into employees values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into employees values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into employees values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into employees values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into employees values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into employees values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into employees values (7876,'ADAMS','CLERK',7788,to_date('13-6-87', 'dd-mm-yyyy')-51,1100,NULL,20);
insert into employees values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into employees values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into employees values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
As mentioned in the question, the following:
select department_name, employee_id, employee_name
from departments d
cross join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
is equivalent, but is it the best option?