APPLY is a variant of the ANSI CROSS JOIN or ANSI LEFT OUTER JOIN and supports left correlations. APPLY has two forms: OUTER APPLY and CROSS APPLY.
CROSS APPLYis similar toCROSS JOIN, but the right side can reference columns from the left table (i.e., left correlation). It returns only the rows from the left table where thetable_referenceorcollection_expressiongenerates a result set.OUTER APPLYis similar toLEFT OUTER JOIN, and it retains the rows from the left table even if there are no matching values on the right side.
Constraints on the right table in APPLY
APPLY has the following constraints:
table_reference(table reference): can be a regular table, an inline view (i.e., a subquery view), or TABLE().collection_expression(collection expression): can be a subquery, column, function, or collection constructor.collection_expressionmust return a collection value, i.e., 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 result is as follows:
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 result is as follows:
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 result is as follows:
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 result is as follows:
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 result is as follows:
-- 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 result is as follows:
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 result is as follows:
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 |
+------+------+------+