JOINS

JOINS

1. QUALIFIER

   • common columns should be qualified to avoid ambiguity
   • Qualifier is chosen always for the column which is having less no of records.

2. CARTESIAN PRODUCT

   • Without join condition joins will produce cartesian product
   • SELECT E.ENAME,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO
     • Even Now also this query produces 56 records but because of join condition 14 records are true so they came out
       • Here joins are not reducing the cartesian product, the only thing is when you keep join condition those excess records in the cartesian product are not visible to you

3. DEFINATION

   • Join is a query that combine rows from two or more tables,views or materialized views
   • Fundametal concept of join is to merge the data from all the tables declared in the FROM clause

4. JOIN PRINCIPLE

   • The moment you keep your FROM clause with multiple number of tables then you started with a join Principle

5. MERGING OF MORE THAN 2 TABLES

   • ORACLE never joins all the 3 tables at a time, first two tables are joined then result will be joined with another table
     • Until join condition is coming into the picture it will go with its own order.

6. JOIN CONDITION

   • It should compare two columns each from different tables
   • EMP.DEPTNO=DEPT.DEPTNO
     • This is a single condition which is comparing two different columns with two different tables

7. CONDITION

   • WHERE JOB='MANAGER' AND DNAME='SALES'