What are the different types of joins and explain them
briefly.
- 3 Answers
- 11094 Views
- I also Faced
- E-Mail Answers
Answer Posted / selvaraj v, anna university
Joins in Oracle 10g :
---------------------
A join is a query that combines rows from two or more
tables, views, or materialized views ("snapshots"). Oracle
performs a join whenever multiple tables appear in the
query's FROM clause. The query's select list can select any
columns from any of these tables. If any two of these
tables have a column name in common, you must qualify all
references to these columns throughout the query with table
names to avoid ambiguity.
Join Conditions
---------------
Most join queries contain WHERE clause conditions that
compare two columns, each from a different table. Such a
condition is called a join condition. To execute a join,
Oracle combines pairs of rows, each containing one row from
each table, for which the join condition evaluates to TRUE.
The columns in the join conditions need not also appear in
the select list.
To execute a join of three or more tables, Oracle first
joins two of the tables based on the join conditions
comparing their columns and then joins the result to
another table based on join conditions containing columns
of the joined tables and the new table. Oracle continues
this process until all tables are joined into the result.
The optimizer determines the order in which Oracle joins
tables based on the join conditions, indexes on the tables,
and, in the case of the cost-based optimization approach,
statistics for the tables.
In addition to join conditions, the WHERE clause of a join
query can also contain other conditions that refer to
columns of only one table. These conditions can further
restrict the rows returned by the join query.
Equijoin / Inner Join
---------------------
An equijoin is a join with a join condition containing an
equality operator. An equijoin combines rows that have
equivalent values for the specified columns.
Cartesian Product
-----------------
If two tables in a join query have no join condition,
Oracle returns their Cartesian product. Oracle combines
each row of one table with each row of the other. A
Cartesian product always generates many rows and is rarely
useful. For example, the Cartesian product of two tables,
each with 100 rows, has 10,000 rows. Always include a join
condition unless you specifically need a Cartesian product.
If a query joins three or more tables and you do not
specify a join condition for a specific pair, the optimizer
may choose a join order that avoids producing an
intermediate Cartesian product.
Outer Join
----------
An outer join extends the result of a simple join. An outer
join returns all rows that satisfy the join condition and
those rows from one table for which no rows from the other
satisfy the join condition. Such rows are not returned by a
simple join. To write a query that performs an outer join
of tables A and B and returns all rows from A, apply the
outer join operator (+) to all columns of B in the join
condition. For all rows in A that have no matching rows in
B, Oracle returns NULL for any select list expressions
containing columns of B.
Outet join has Two types :
--------------------------
1. Left Outer Join
2. Right Outer Join
Self Join
---------
A self join is a join of a table to itself. This table
appears twice in the FROM clause and is followed by table
aliases that qualify column names in the join condition. To
perform a self join, Oracle combines and returns rows of
the table that satisfy the join condition.
| Is This Answer Correct ? | 2 Yes | 2 No |
Post New Answer View All Answers
Which data type is a composite type?
Can I call a procedure inside a function?
When you have to use a default "rollback to" savepoint of plvlog?
What is right join in sql?
How many parts of a pl sql block are optional?
Is clustered index a primary key?
What is delimiter in pl sql?
What does the hierarchical profiler does?
Can we use ddl statements in stored procedure sql server?
Can we alter stored procedure?
What are local and global variables and their differences?
How do I remove sql developer from windows 10?
what is timestamp in mysql? : Sql dba
How does sql store data?
What is sql catalog?
