The following articles mainly describe the table connection statements for Oracle Outer join and cross join. Oracle database connections include the inner join statements) oracle Outer join, full join), left join), right join, and cross join ).
The outer join is different from the inner join. The returned results of the outer join include not only the rows that meet the conditions, but also the left table (left Outer Join) and the right table (right Outer Join) or all non-conforming data rows in two connected tables (all external connections.
1. left join (left [outer] join)
The left Outer Join combines all data in the left table with each data entry in the right table. The returned results include non-conforming data in the left table in addition to the data in the inner join, enter the null value in the corresponding column of the right table.
The SQL statement is as follows:
- select * from mt_pb_org o left join mt_pb_orgframe f on o.PB_ORGFRAMEID = f.PB_ORGFRAMEID;
Equivalent statement:
- select * from mt_pb_org o,mt_pb_orgframe f where o.pb_orgframeid = f.pb_orgframeid(+);
2. right join (right [outer] join)
The outer right join combines all the data in the right table with each data entry in the left table, and the returned results include non-conforming data in the right table in addition to the data in the inner join, enter the null value in the corresponding column of the Left table.
The SQL statement is as follows:
- select * from mt_pb_org o right join mt_pb_orgframe on
o.pb_orgframeid = f.pb_orgframeid;
Equivalent statement:
- select * from mt_pb_org o,mt_pb_orgframe f where o.
pb_orgframeid(+) = f.pb_orgframeid;
3. full Oracle outer join (full [outer] join)
A full outer join is to concatenate all data in the left table and each data entry in the right table respectively. The returned results include non-conforming data in the two tables except the data in the inner join, enter the null value in the corresponding column of the Left or Right table.
The SQL statement is as follows:
- select * from mt_pb_org o full join mt_pb_orgframe
o.pb_orgframeid = f.pb_orgframeid;
4. cross join)
A crossover clause without a WHERE clause returns the Cartesian product of all data rows in the two joined tables, the number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The SQL statement is as follows:
- select * from mt_pb_org o cross join mt_pb_orgframe f;