Connection of tables inside the brackets. Role and benefits
I've been in touch with a stranger who can describe a simple example:
WITH A AS ( SELECT 1 A, 6 B FROM DUAL UNION ALL SELECT 2 A, 5 B FROM DUAL UNION ALL SELECT 3 A, 4 B FROM DUAL UNION ALL SELECT 4 A, 3 B FROM DUAL UNION ALL SELECT 5 A, null B FROM DUAL UNION ALL SELECT 6 A, 1 B FROM DUAL ) SELECT '||A1||', A1.*, '||A2||', A2.*, '||A3||', A3.* FROM A A1 JOIN (A A2 LEFT JOIN A A3 ON A2.A=A3.B) ON A1.A = A3.B;
This code is fully operational in Oracle 12c, but it doesn't work, for example, in an impala (without DUAL, of course).
The point is, this is the first time I see
JOINinside the bracket. However, these brackets do not receive their pseudonym and are attached to other tables with reference to one of the tables inside the brackets.
I'm trying to figure out why this is done and what benefits it is. Is this a forced optimist to do first?
А3? What benefits are
JOINCan you have a traditional approach?
WITH A AS ( SELECT 1 A, 6 B FROM DUAL UNION ALL SELECT 2 A, 5 B FROM DUAL UNION ALL SELECT 3 A, 4 B FROM DUAL UNION ALL SELECT 4 A, 3 B FROM DUAL UNION ALL SELECT 5 A, null B FROM DUAL UNION ALL SELECT 6 A, 1 B FROM DUAL ) SELECT '||A1||', A1.*, '||A2||', A2.*, '||A3||', A3.* FROM A A1 JOIN A A3 ON A1.A = A3.B LEFT JOIN A A2 ON A2.A=A3.B;
Analeea last edited by
The role of the brackets in three or more tables is the same as in any composition to change the order of implementation. ♪ https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__CHDIJFDJ This is stated as follows:
When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax:
SELECT ... FROM a JOIN (b JOIN c) ...
results in a join of
c, and then a join of that result set with
Default order is right on the left. The optimist may, however, modify this order on the basis of the conditions of the connection, the presence of indices and mn.outer jointhe order will remain as the result will depend on the order of implementation.
For a better understanding, the reproduced example of the way in which the connections play a role, it changes with the conclusion of one of the brackets:
with t (id, val) as ( select rownum, column_value from sys.odciVarchar2List ('a', 'b', 'c') ) select a.id, c.val val from t a left join t b on b.id=a.id and b.val != 'c' join t c on c.id=b.id union all select null, '#1 ^^^; #2 vvv' from dual union all select a.id, c.val val from t a left join ( t b join t c on c.id=b.id ) on b.id=a.id and b.val != 'c';
1 a 2 b #1 ^^^; #2 vvv 1 a 2 b 3
SQL Oracle - grading of data on the first entry of the divider symbol
Software Programming • • Marcee
Problem with procedure stored in SQL Developer - SP is not "updated" - even after compiling
Software Programming • • Rossere
Is dmp file suitable despite ORA-01555 'snapshop too old' warnings?
SQL, Database Testing • • nishika
Request with condition that there is a connection to all the lines of the table
Software Programming • • emran
How to select by taking the record that has the maximum value of a field?
Software Programming • • shizuka
How do you make a connection to M:M, where there's another field in the connecting table?
Software Programming • • Demir