The APPLY clause is a variant of the ANSI CROSS JOIN or ANSI LEFT OUTER JOIN clause and supports left correlations. APPLY has two forms: OUTER APPLY and CROSS APPLY.
CROSS APPLYis similar toCROSS JOIN, but it allows the right table to reference columns of the left table (i.e., left correlation). It returns only the rows from the left table that are generated by thetable_referenceorcollection_expression.OUTER APPLYis similar toLEFT OUTER JOIN, and it retains the rows from the left table even if there are no matching values in the right table.
Constraints on the right table of APPLY
The APPLY clause has the following constraints:
table_reference(table reference): can be a regular table, an inline view (i.e., a subquery view), or a TABLE() expression.collection_expression(collection expression): can be a subquery, a column, a function, or a collection constructor.collection_expressionmust return a collection value, that is, the type must be a nested table or a varray.
Examples
Create tables t1 and t2 and insert some data.
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (1, 10);
INSERT INTO t1 VALUES (2, 20);
INSERT INTO t1 VALUES (3, 30);
INSERT INTO t2 VALUES (1, 100);
INSERT INTO t2 VALUES (1, 101);
INSERT INTO t2 values (1, 102);
INSERT INTO t2 VALUES (2, 200);
When the right table is a regular table, the CROSS APPLY query returns the following result:
obclient > SELECT * FROM t1 cross apply t2;
+------+------+------+------+
| C1 | C2 | C1 | C2 |
+------+------+------+------+
| 1 | 10 | 1 | 100 |
| 2 | 20 | 1 | 100 |
| 3 | 30 | 1 | 100 |
| 1 | 10 | 1 | 101 |
| 2 | 20 | 1 | 101 |
| 3 | 30 | 1 | 101 |
| 1 | 10 | 2 | 200 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 2 | 200 |
| 1 | 10 | 1 | 102 |
| 2 | 20 | 1 | 102 |
| 3 | 30 | 1 | 102 |
+------+------+------+------+
When the right table is a regular table, the OUTER APPLY query returns the following result:
obclient > SELECT * FROM t1 outer apply t2;
+------+------+------+------+
| C1 | C2 | C1 | C2 |
+------+------+------+------+
| 1 | 10 | 1 | 100 |
| 1 | 10 | 1 | 101 |
| 1 | 10 | 1 | 102 |
| 1 | 10 | 2 | 200 |
| 2 | 20 | 1 | 100 |
| 2 | 20 | 1 | 101 |
| 2 | 20 | 1 | 102 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 1 | 100 |
| 3 | 30 | 1 | 101 |
| 3 | 30 | 1 | 102 |
| 3 | 30 | 2 | 200 |
+------+------+------+------+
When the right table is a subquery, the CROSS APPLY query returns the following result:
obclient> SELECT * FROM t1 cross apply (SELECT * FROM t2 WHERE t1.c1 = t2.c1 AND rownum <= 2) v;
+------+------+------+------+
| C1 | C2 | C1 | C2 |
+------+------+------+------+
| 1 | 10 | 1 | 100 |
| 1 | 10 | 1 | 101 |
| 2 | 20 | 2 | 200 |
+------+------+------+------+
When the right table is a subquery, the OUTER APPLY query returns the following result:
obclient> SELECT * FROM t1 outer apply (SELECT * FROM t2 WHERE t1.c1 = t2.c1 AND rownum <= 2) v;
+------+------+------+------+
| C1 | C2 | C1 | C2 |
+------+------+------+------+
| 1 | 10 | 1 | 100 |
| 1 | 10 | 1 | 101 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | NULL | NULL |
+------+------+------+------+
When the right table is a function expression, the query returns the following result:
-- Define a nested table type
CREATE TYPE t2_c2_ntt AS TABLE OF NUMBER;
/
-- Define a function that returns a nested table
CREATE OR REPLACE FUNCTION get_t2_c2(p_c1 INT)
RETURN t2_c2_ntt
IS
res t2_c2_ntt;
BEGIN
SELECT c2
BULK COLLECT INTO res
FROM t2
WHERE c1 = p_c1;
RETURN res;
END;
/
obclient> SELECT t1.c1, t1.c2, t2item.COLUMN_VALUE AS t2_c2 FROM t1 CROSS APPLY TABLE(get_t2_c2(t1.c1)) t2item;
+------+------+-------+
| C1 | C2 | T2_C2 |
+------+------+-------+
| 1 | 10 | 100 |
| 1 | 10 | 101 |
| 1 | 10 | 102 |
| 2 | 20 | 200 |
+------+------+-------+
When the right table is a collection constructor, the query returns the following result:
obclient> SELECT t1.c1, t1.c2, t2item.COLUMN_VALUE AS t2_c2
-> FROM t1
-> CROSS APPLY TABLE(
-> CAST(MULTISET(
-> SELECT c2 FROM t2 WHERE t2.c1 = t1.c1
-> ) AS t2_c2_ntt)
-> ) t2item;
+------+------+-------+
| C1 | C2 | T2_C2 |
+------+------+-------+
| 1 | 10 | 100 |
| 1 | 10 | 101 |
| 1 | 10 | 102 |
| 2 | 20 | 200 |
+------+------+-------+
When the right table is a JSON table, the query returns the following result:
obclient> CREATE TABLE t_json (
id NUMBER,
data CLOB -- Store JSON strings
);
obclient> INSERT INTO t_json VALUES (1, '{"items":[{"name":"A","val":10},{"name":"B","val":20}]}');
obclient> INSERT INTO t_json VALUES (2, '{"items":[{"name":"C","val":30}]}');
obclient> SELECT
-> t.id,
-> jt.name,
-> jt.val
-> FROM
-> t_json t
-> CROSS APPLY
-> JSON_TABLE(
-> t.data,
-> '$.items[*]'
-> COLUMNS (
-> name VARCHAR2(10) PATH '$.name',
-> val NUMBER PATH '$.val'
-> )
-> ) jt;
+------+------+------+
| ID | NAME | VAL |
+------+------+------+
| 1 | A | 10 |
| 1 | B | 20 |
| 2 | C | 30 |
+------+------+------+