|
JOINS
A Join is a query that combines rows from two or more Tables, Views or Materialized Views.To join two tables their must be a common attribute in both the tables.
Types of Joins ::
1. Cartesian product
2. Self join
3. Inner Join
4. Outer Join
1. Cartesian product
If two tables in a join query have no join condition, it returns cartesian product result.For example if one table has 10 records and the other table has 20 records , cartesian product of this two tables 10*20=200.So it displays 200 records.
Syntax ::
[SELECT] <COLUMN1>....<COLUMNX>
FROM <TABLE1>..<TABLEX> ;
Example ::
SELECT CUST_NAME, CUST_DEPTNO, CUST_SALES
FROM CUSTOMER, SALES;
|
2. Self Join
A self join is a join a table to itself.Oracle combines and return the records which satisfys join condition.
Syntax ::
[SELECT] <COLUMN1>....<COLUMNX>
FROM T1.<TABLE1>,T2.<TABLE1>
WHERE T1.[<COLUMN1>|...|<COLUMNX>]=T2.[<COLUMN1>|...|<COLUMNX>];
Example ::
SELECT E1.CUST_NAME, E2.CUST_NAME
FROM E1.CUSTOMER,E2.CUSTOMER
WHERE E1.CUST_ID=E2.CUST_ID ;
|
|
