What is a join

  • Interview Candidate
  • 7
  • 5084

Editorial / Best Answer

Answered by: NK

  • Jun 29th, 2005

A join is a query that combines rows from two or more tables, views, or materialized views. 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, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity. 
 
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. 
 

Showing Answers 1 - 7 of 7 Answers

NK

  • Jun 29th, 2005
 

A join is a query that combines rows from two or more tables, views, or materialized views. 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, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity. 
 
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. 
 

  Was this answer useful?  Yes

Jagdish

  • Aug 22nd, 2005
 

It joins the multiple table with the consition. 

  Was this answer useful?  Yes

Ashish Bajpai

  • Sep 27th, 2005
 

Join is a condition to retrive the data from a single or multiple table. Basiclly we can access the date from multiple table in two ways

1. with join

  • Inner

2. without join

  Was this answer useful?  Yes

Ashish Bajpai

  • Sep 27th, 2005
 

Join is a condition to retrive the data from a single or multiple table. Basiclly we can access the date from multiple table in two ways

1. with join

  • Inner Join
  • Left outer Join
  • Right outer Join
  • Full Join

2. without join

  • Cross Join

  Was this answer useful?  Yes

NARENDRA

  • Dec 11th, 2006
 

JOIN:it is nothing but  a query used to retrieve data from more than two tables.

  Was this answer useful?  Yes

csbhaskar

  • Apr 7th, 2011
 

A join is a query that results in combining rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever names of multiple tables, views or materialized views appear in the FROM clause of the query. The select list of the query is capable of selecting any columns from the tables, views or materialized views whose name appears in the FROM clause. If any two of these database objects have a common column name, then the column referenced must be qualified to avoid ambiguity.

For join, queries should have at least one join condition. If no join condition is specified the result will be a Cartesian product. Two columns, one from each table are compared when joining two tables. To execute a join of three or more tables, Oracle first joins two tables based on join conditions and the result of this join is again compared with the third table. The minimum join conditions required to combine n number of tables is n-1.


The columns in the join conditions need not necessarily appear in the select clause.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions