What are the different types of joins and explain them
briefly.
- 3 Answers
- 10565 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
what is cross join? : Sql dba
Is sqlite good enough for production?
Can we use rowid as primary key?
How sql query is executed?
How many types of cursors are available in pl/sql?
What are the two virtual tables available at the time of database trigger execution?
Which command is used to call a stored procedure?
How do I view output in sql developer?
What is prepared statement in sql?
Can you call pl/sql package functions from within a fast formula?
Show how functions and procedures are called in a pl/sql block.
How to select 10 records from a table?
How delete all data from table in sql?
what are the different tables present in mysql? : Sql dba
What is difference between cursor and trigger?
