5 Replies Latest reply: Jan 21, 2009 4:38 PM by 3520 RSS

    Difference between Inner join and Self Join

    Williams
      Hi Buddies,

      Difference between Inner join and Self Join? Can anyone tell me with an example?Plz


      Regards,
      Karthick
        • 1. Re: Difference between Inner join and Self Join
          OrionNet
          Karthick,

          An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

          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 Database combines and returns rows of the table that satisfy the join condition.

          And you can find detailed explanation here

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3173

          Regards
          • 2. Re: Difference between Inner join and Self Join
            639997
            There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN

            The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table)
            INPUT:
            SELECT *
            FROM CUSTOMER

            OUTPUT:
            NAME ADDRESS STATE ZIP PHONE REMARKS
            ========== ========== ====== ========== ========= ==========

            TRUE WHEEL 55O HUSKER NE 58702 555-4545 NONE
            BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE
            LE SHOPPE HOMETOWN KS 54678 555-1278 NONE
            AAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGR
            JACKS BIKE 24 EGLIN FL 34567 555-2314 NONE

            This table contains all the information you need to describe your customers. The items you sold would go into another table:

            INPUT:
            SELECT *
            FROM PART
            OUTPUT:
            PARTNUM DESCRIPTION PRICE
            =========== ==================== ===========

            54 PEDALS 54.25
            42 SEATS 24.50
            46 TIRES 15.25
            23 MOUNTAIN BIKE 350.45
            76 ROAD BIKE 530.00
            10 TANDEM 1200.00
            And the orders you take would have their own table:

            INPUT:
            SELECT *
            FROM ORDERS
            OUTPUT:
            ORDEREDON NAME PARTNUM QUANTITY REMARKS
            =========== ========== =========== =========== =======

            15-MAY-1996 TRUE WHEEL 23 6 PAID
            19-MAY-1996 TRUE WHEEL 76 3 PAID
            2-SEP-1996 TRUE WHEEL 10 1 PAID
            30-JUN-1996 TRUE WHEEL 42 8 PAID
            30-JUN-1996 BIKE SPEC 54 10 PAID
            30-MAY-1996 BIKE SPEC 10 2 PAID
            30-MAY-1996 BIKE SPEC 23 8 PAID
            17-JAN-1996 BIKE SPEC 76 11 PAID
            17-JAN-1996 LE SHOPPE 76 5 PAID
            1-JUN-1996 LE SHOPPE 10 3 PAID
            1-JUN-1996 AAA BIKE 10 1 PAID
            1-JUL-1996 AAA BIKE 76 4 PAID
            1-JUL-1996 AAA BIKE 46 14 PAID
            11-JUL-1996 JACKS BIKE 76 14 PAID

            Now join PARTS and ORDERS:

            INPUT/OUTPUT:
            SELECT O.ORDEREDON, O.NAME, O.PARTNUM,
            P.PARTNUM, P.DESCRIPTION
            FROM ORDERS O, PART P

            ORDEREDON NAME PARTNUM PARTNUM DESCRIPTION
            =========== ========== =========== ========= ============

            15-MAY-1996 TRUE WHEEL 23 54 PEDALS
            19-MAY-1996 TRUE WHEEL 76 54 PEDALS
            2-SEP-1996 TRUE WHEEL 10 54 PEDALS
            30-JUN-1996 TRUE WHEEL 42 54 PEDALS
            30-JUN-1996 BIKE SPEC 54 54 PEDALS
            30-MAY-1996 BIKE SPEC 10 54 PEDALS
            30-MAY-1996 BIKE SPEC 23 54 PEDALS
            17-JAN-1996 BIKE SPEC 76 54 PEDALS
            17-JAN-1996 LE SHOPPE 76 54 PEDALS
            1-JUN-1996 LE SHOPPE 10 54 PEDALS
            1-JUN-1996 AAA BIKE 10 54 PEDALS
            1-JUL-1996 AAA BIKE 76 54 PEDALS
            1-JUL-1996 AAA BIKE 46 54 PEDALS
            11-JUL-1996 JACKS BIKE 76 54 PEDALS
            ...


            Self join
            select b.empno Employee,b.job Job,a.empno Manager,a.job Job from emp a,emp b where b.mgr=a.empno
            • 3. Re: Difference between Inner join and Self Join
              554938
              For self join oracle has to open the two tables in memory , so for differentiation you need to alias them differently otherwise the records will overlap.

              like
              select a.col1,a.col2 from tab1 a, tab1 b
              where ...
              • 4. Re: Difference between Inner join and Self Join
                680893
                Hi,

                self join is subtype of inner join.
                -----
                -- We have two tables:

                CREATE TABLE department (
                id_department NUMBER(18,0),
                department_name VARCHAR2(240),
                CONSTRAINT department_pk PRIMARY KEY (id_department)
                );

                CREATE TABLE person (
                id_person NUMBER(18,0),
                id_chief NUMBER(18,0),
                id_department NUMBER(18,0),
                firstname VARCHAR2(240) NOT NULL,
                secondname VARCHAR2(240) NOT NULL,
                age NUMBER(3,0) NOT NULL,
                CONSTRAINT person_pk PRIMARY KEY (id_person),
                CONSTRAINT person_chk1 CHECK (age >= 0)
                );

                ALTER TABLE person ADD CONSTRAINT person_chief_fk1 FOREIGN KEY (id_chief) REFERENCES person(id_person);
                ALTER TABLE person ADD CONSTRAINT person_department_fk2 FOREIGN KEY (id_department) REFERENCES department(id_department);

                -- Inner join (not self, we use two different tables and join them)
                SELECT person.firstname, person.secondname, department.department_name
                FROM person
                INNER JOIN department ON person.id_department = department.id_department;

                -- Self join (it is self join but we join only one table to itself
                SELECT emp.secondname, chief.secondname
                FROM person emp
                INNER JOIN person chief
                ON emp.id_chief = chief.id_person;

                Martin
                • 5. Re: Difference between Inner join and Self Join
                  3520
                  Probably look at this [join classification diagram|http://www.gplivna.eu/papers/sql_join_types.htm] to understand what the difference is and what they have in common.

                  Gints Plivna
                  http://www.gplivna.eu

                  Edited by: gintsp on Jan 22, 2009 12:37 AM