This content has been marked as final. Show 6 replies
Igor S. wrote:Listing comma-separated tables is Oracle join syntax. But TABLE operator makes it a join of a different flavor. Oracle will take one row from test_table1, execute function f_function, table it using table operator and join that test_table1 row to results. Then it will take next row from test_table1 and do the same.
Igor S. wrote:Try it and see.
select * from test_table1, table( f_function( null, null, id, sysdate ) ) /
So these two queries are the same?
select * from test_table1, table( f_function( null, null, id, sysdate ) xyz ) where test_table1.id = xyz.id /
You'll find that the 2nd one produces an error. But if you change it to
Then, assuming f_function produces a column called id, it will work.
select * from test_table1, table( f_function( null, null, id, sysdate ) -- No alias here ) xyz -- Alias here, instead where test_table1.id = xyz.id /
Whether it produces the same results or not depends on what the function returns, and whether either id is NULL.
If the id column that the function returns is the same as the id value that you pass to it, and is never NULL, then the 2 queries will produce the same results.
Either way, each row of test_table1 will be joined to each row that the function produces with the argument(s) from that row. The column names produced by the function and the values in those columns are determined by the function; they do not need to have anything in common with any table. In practice, a function like f_function will usually not return an column that is always identical to any of its inputs, since that value is already available from the input.