SQL-Joins

Ø  The purpose of a join is to combine the data across tables.

Ø  A join is actually performed by the where clause which combines the specified rows of tables.

Ø  If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

       Equi join

       Non-equi join

       Self join

       Natural join

       Cross join

       Outer join

Ø  Left outer

Ø  Right outer

Ø  Full outer

       Inner join

       Using clause

       On clause

Assume that we have the following tables.

SQL> select * from dept;

    DEPTNO DNAME      LOC

     ------ ---------- ----------

        10            mkt        hyd

        20            fin        bang

        30            hr         bombay

SQL> select * from emp;

       EMPNO   ENAME      JOB       MGR     DEPTNO

      ---------- ---------- ---------- ---------- ----------

       111         saketh     analyst           444         10

       222         sudha     clerk                333         20

       333         jagan      manager         111         10

       444         madhu    engineer         222         40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

          EMPNO     ENAME      JOB    DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

ON CLAUSE

SQL>  select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno >

             d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

       222    sudha      clerk          mkt        hyd

       444    madhu     engineer   mkt        hyd

       444    madhu     engineer   fin          bang

       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where

             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO

     ---------- ---------- ---------- ----------

       111          jagan      analyst         10

       222          madhu      clerk           40

       333          sudha      manager      20

       444          saketh     engineer      10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC

    ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst     mkt        hyd

       333          jagan      manager   mkt        hyd

       222          sudha      clerk         fin          bang

CROSS JOIN

This will gives the cross product.

Ex:

     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       111     saketh   analyst      mkt        hyd

       222     sudha    clerk          mkt        hyd

       333     jagan     manager   mkt        hyd

       444     madhu   engineer   mkt        hyd

       111     saketh   analyst      fin          bang

       222     sudha    clerk          fin          bang

       333     jagan     manager   fin          bang

       444     madhu   engineer   fin          bang

       111     saketh   analyst      hr           bombay

       222     sudha    clerk          hr           bombay

       333     jagan     manager   hr           bombay

       444     madhu   engineer   hr           bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d

             on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where

             e.deptno=d.deptno(+);

                             EMPNO     ENAME   JOB       DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh    analyst       mkt        hyd

       333          jagan      manager    mkt        hyd

       222          sudha     clerk           fin          bang

       444          madhu    engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d

              on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =

             d.deptno;

     EMPNO    ENAME     JOB      DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt        hyd

       333          jagan       manager   mkt        hyd

       222          sudha      clerk          fin          bang

                                                       hr           bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       333     jagan     manager    mkt        hyd

       111     saketh   analyst       mkt        hyd

       222     sudha    clerk           fin        bang

       444     madhu   engineer

                                                   hr         bombay

INNER JOIN

This will display all the records that have matched.

Ex:

     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt       hyd

       333          jagan       manager   mkt       hyd

       222          sudha      clerk          fin         bang

SQL Joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

                                                                 ( or)

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

The Syntax for joining two tables is:

SELECT col1, col2, col3...
FROM table_name1, table_name2 
WHERE table_name1.col2 = table_name2.col1; 

If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Lets use the below two tables to explain the sql join conditions.

database table "product";

product_id	product_name	supplier_name	unit_price
100	Camera	Nikon	300
101	Television	Onida	100
102	Refrigerator	Vediocon	150
103	Ipod	Apple	75
104	Mobile	Nokia	50

database table "order_items";

order_id	product_id	total_units	customer
5100	104	30	Infosys
5101	102	5	Satyam
5102	103	25	Wipro
5103	101	10	TCS

SQL Joins can be classified into Equi join and Non Equi join.

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.

For example: You can get the information about a customer who purchased a product and the quantity of product.

2) SQL Non equi joins

It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=