CODEWITHSUNDEEP
sqloracle-databasejoin
hydertza
hydertza

Reputation: 193

What is the difference between CROSS JOIN and CROSS JOIN TABLE

In the following code : 

SELECT ... FROM ... CROSS JOIN TABLE ... WHERE ...;

What does CROSS JOIN TABLE mean ?

I searched on the net, but all I can find is about CROSS JOIN only.

I suppose CROSS JOIN TABLE acts on a table, meaning a table like that : 

CREATE OR REPLACE ... AS TABLE OF ...;

Here is the full query :

SELECT prog.id_oct_prog_tran_a_participati, prog.code_ressource, prog.instant_depart, prog.commentaire, prog.en_hors_economie_de_comb, discr.delai, discr.valeur_point, MOD(delai, 60) AS H24
          FROM req_prog prog
            CROSS JOIN TABLE(POINTS_DISCRETS(pIdChronique=>id_chr_substitution)) discr
          WHERE horizon <= 'J1'
            AND delai > 0
          ORDER BY id_oct_prog_tran_a_participati, instant_depart, horizon, delai

POINTS_DISCRETS is a function that returns an element of type TYPE_TAB_POINT. And TYPE_TAB_POINT is a type that the DBA created as following : 

create or replace TYPE "TYPE_TAB_POINT" AS TABLE OF TYPE_POINT;

Where TYPE_POINT is a type created as following : 

create or replace TYPE "TYPE_POINT" AS OBJECT
(
ID_CHRONIQUE NUMBER,
HORIZON      VARCHAR2(2),
NUM_POINT    NUMBER(4),
DELAI        NUMBER(5),
VALEUR_POINT FLOAT
);

So, as you see here, CROSS JOIN TABLE acts on a table but not a table as we usually mean in a database, more a table like an array.

Is it the case ? And, if yes, how can it be considered like a real table ?

Upvotes: 0

Views: 80

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You're parsing the statement incorrectly, which is confusing you I think. CROSS JOIN TABLE isn't one fragment; it's aCROSS JOIN (but could be any join type) between your real table req_prog and, separately, TABLE(...) to tell the query to treat the return value of the function call as a table.

TABLE(...) is the table collection expression:

The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor.

Here POINTS_DISCRETS is the collection constructor - returning TYPE_TAB_POINT, which is a collection, most of which you already figured out.

In this case your collection is a table of objects, and treating it as a table allows you both to join and to refer to the attributes of the object that forms each 'row' as if it were a column. The TABLE() is aliased as discr, so you are able to refer to discr.delai - where delai is one of the attributes of the TYPE_POINT type. If you were using an inner or outer join, rather than a cross join, you would use the same construction in the join condition, e.g. ON discr.x = prog.x. Once you apply the table collection expression you just treat it as a table in the rest of the statement.

Upvotes: 6

Related Questions