What are the different types of joins and explain them
briefly.
- 3 Answers
- 10579 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 are system versioned tables?
What is memory optimized?
What does dml mean?
What is trigger explain with example?
What is index example?
What are the types of subqueries?
what is sql profiler? : Sql dba
Which column in the user.triggers data dictionary view shows that the trigger is a pl/sql trigger?
Are stored procedures faster than queries?
How do I remove all records from a table?
Table1: Col1 col2 1 2 10 3 4 89 5 6 Table:2 Col1 col2 3 2 9 5 4 7 6 87 With the help of table1 and table2 write a query to simulate the fallowing results. Output1: Col1 col2 1 2 2 3 3 4 4 5 5 6 Output2: Col1 col2 2 3 10 4 5 89 6 7 1.Write query for single row to multiple row using sql statements. Eg:a,b,c,d,e,f Change to A B C D E F 2. Write query for multiple row to single row using sql statements. Eg2 A B C D E F Change to Eg:a,b,c,d,e,f Table1: Col1 col2 8 5 2 9 4 2 5 1.Write a query to select all the rows from a table1,if the value of A is null then corresponding B’s value should be printed in A’s value.if the value of A is null in that table then corresponding B’s value should be printed as 30. 2. write a query to find the sum of A and B .display the max among both. 3.write a query to find total number of rows from table 1. Note: if any column value is null in a row then that row should be considered as 2 rows. 4.write a query to display all the records of table1 except A containg 2 as well B containg 5. 5.rewrite the fallowing without using join and group by. Select b.title,max(bc.returneddate –bc.checkoutdate)” mostdaysout” From bookshelf_checkout bc, Book shelf B Where bc.title(+)=b.title Group by b.title. 6.rewrite fallowing query Select id_category from category_master X where exists (select 1 from sub_category Y where X.id_category=Y.id_category) Customer: Name phone1 phone2 phone3 bitwise A 23456 67890 12345 --- B 67459 89760 37689 --- Don’t_call Col1 67890 37689 1.q) update the customer table of bitwise with 1 or 0. Exists in don’t_call table menas show -1 Other wise -0. Output. Name bitwise A 010 B 010
difference between anonymous blocks and sub-programs.
Can two tables have same primary key?
What is procedure explain with example?
What are the basic techniques of indexing?
