Monday, February 7, 2011

JOINS IN ORACLE


Cross-join or Cartesian product.
Merges all data selected from both tables into a single result set.
Inner join.
Combines rows from both tables using matching column
names and column values. The result set includes only rows that
match.
Outer join.
Selects rows from both tables as with an inner join but
including rows from one or both tables that do not have matching
rows in the other table. Missing values are replaced with null values.
Left outer join.
All rows from the left table plus all matching rows from the right table. Column values from the right table are
replaced with null values when the matching right-side row does not exist in the left-side table.
Right outer join.
All rows from the table on the right plus matching rows from the left table, the opposite of the left outer join.
Full outer join.
All rows from both tables, with null values replacing missing values.
Self-join.
This joins a table to itself.
Equi-joins, anti-joins, and range joins.An equi-join combines table data based on equality (=), an anti-join matches data based on inequality (!=, <> or NOT), and a range join compares data using a range of values (<, > or BETWEEN).
Mutable and complex joins.
A mutable join is a join of more than two tables. A complex join is a mutable join with added filtering.
Unrelated-SETS
Natural-join
LEFT-OUTER-JOIN RIGHT-OUTER-JOIN FULL-OUTER-JOIN

No comments:

Post a Comment