Answer / hindol

equi join ,
non-equi join,
self join,
outer join,
cross join.

Answer / rahul koshti

There are 2 main type of joins :
Equijoin : Join N number of tables for equality =.
Non-Equijoin : Join N number of tables for other than
equality such as <>,<,>,IN,BETWEEN etc.

Technically there are total 3 different types of joins:
Cross Join: When join condition is missing then all rows
from one table gets joined with all rows from other table..
Inner Join: If a row has a NULL value in any one of the
columna of join conditin then that row will not fetched.
Outer Join: Even if the NULL values are present in any
columns of join condition that record will get fetched.
There are again 3 types of outer joins: Left, Right, Full.
Self Join : A table is joined with self.

Answer / 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.

What is a parameter query?

0 Answers  

---

Where can I learn sql for free?

0 Answers  

---

How to write a query to show the details of a student from students table whose

0 Answers  

---

How do I run a sql script?

0 Answers  

---

How to convert lowercase letters to uppercase and uppercase letters to lowercase in a string. (ex, AbcdEfG should convert as aBCDeFg)

3 Answers   TCS,

---

---

Can we use ddl commands in pl sql?

0 Answers  

---

What is trigger types in sql?

0 Answers  

---

List out the acid properties and explain?

0 Answers  

---

What is the maximum number of rows in sql table?

0 Answers  

---

there is A table and B table in A table there 5 rows and in b table there are 2 rows i am firing query select * from a,b what will be the output?

7 Answers   Cognizant,

---

Can we create index on primary key?

0 Answers  

---

What are two virtual tables available during database trigger execution ?

2 Answers  

---