Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Question Unanswered: different between inner join , self join& outer join

    hi;
    anyone can please tell me the different between inner join, self join & outer join in tables while doing SQL.
    thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: different between inner join , self join& outer join

    Originally posted by lina sam
    hi;
    anyone can please tell me the different between inner join, self join & outer join in tables while doing SQL.
    thanks
    /* COPY MESSAGE TEXT TO SQL QUERY ANALYZER AND RUN */
    /*
    FIRST - RUN INITIALIZATION
    THEN TRY SELECTS BETWEEN COMMENTS, THEY'RE SIMPLE
    LAST - RUN FINALIZATION TO CLEAN UP
    */
    /* Description
    tblA(Col)=array(1,2)
    tblB(Col)=array(2,3)
    tblARev=recusive (tree) table
    1
    / \
    2 3
    */

    --INIALIZATION
    create table tblA(Col int not null)
    create table tblB(Col int not null)
    create table tblARev(Col int not null,ColUp int null)
    GO
    insert tblA(Col) values (1)
    insert tblA(Col) values (2)
    insert tblB(Col) values (2)
    insert tblB(Col) values (3)
    insert tblARev(Col,ColUp) values (1,null)
    insert tblARev(Col,ColUp) values (2,1)
    insert tblARev(Col,ColUp) values (3,1)

    --TEST SELECTS
    --I. inner join
    select * from tblA inner join tblB on tblA.Col=tblB.Col
    -- ...to columns from tblA selects columns from tblB's records matching ON clause.
    -- reduces vieved data from both tables

    --II. self join
    select * from tblARev A1 inner join tblARev A2 on A1.Col=A2.ColUp
    -- ...inner join on the same table with aliases (A1,A2)
    /* used for hierarchies, binding chained records ... */

    --III. outer join - with 3 variants:

    -- 1) full outer join
    select * from tblA full outer join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from both tables, missing values are replaced by NULL values

    -- 2) left join
    select * from tblA left join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from left table (tblA), missing values are replaced by NULL values

    -- 3) right join
    select * from tblA right join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from right table (tblB), missing values are replaced by NULL values

    /*
    SELECT with OUTER JOIN has special WHERE clause able to use condition on resulting NULL values
    -> useful to find missing joins
    */

    --FINALIZATION
    drop table tblA
    drop table tblB
    drop table tblARev

  3. #3
    Join Date
    Dec 2002
    Posts
    3

    Wink Re: different between inner join , self join& outer join

    thanks alot.
    Originally posted by ispaleny
    /* COPY MESSAGE TEXT TO SQL QUERY ANALYZER AND RUN */
    /*
    FIRST - RUN INITIALIZATION
    THEN TRY SELECTS BETWEEN COMMENTS, THEY'RE SIMPLE
    LAST - RUN FINALIZATION TO CLEAN UP
    */
    /* Description
    tblA(Col)=array(1,2)
    tblB(Col)=array(2,3)
    tblARev=recusive (tree) table
    1
    / \
    2 3
    */

    --INIALIZATION
    create table tblA(Col int not null)
    create table tblB(Col int not null)
    create table tblARev(Col int not null,ColUp int null)
    GO
    insert tblA(Col) values (1)
    insert tblA(Col) values (2)
    insert tblB(Col) values (2)
    insert tblB(Col) values (3)
    insert tblARev(Col,ColUp) values (1,null)
    insert tblARev(Col,ColUp) values (2,1)
    insert tblARev(Col,ColUp) values (3,1)

    --TEST SELECTS
    --I. inner join
    select * from tblA inner join tblB on tblA.Col=tblB.Col
    -- ...to columns from tblA selects columns from tblB's records matching ON clause.
    -- reduces vieved data from both tables

    --II. self join
    select * from tblARev A1 inner join tblARev A2 on A1.Col=A2.ColUp
    -- ...inner join on the same table with aliases (A1,A2)
    /* used for hierarchies, binding chained records ... */

    --III. outer join - with 3 variants:

    -- 1) full outer join
    select * from tblA full outer join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from both tables, missing values are replaced by NULL values

    -- 2) left join
    select * from tblA left join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from left table (tblA), missing values are replaced by NULL values

    -- 3) right join
    select * from tblA right join tblB on tblA.Col=tblB.Col
    -- does not reduce vieved data from right table (tblB), missing values are replaced by NULL values

    /*
    SELECT with OUTER JOIN has special WHERE clause able to use condition on resulting NULL values
    -> useful to find missing joins
    */

    --FINALIZATION
    drop table tblA
    drop table tblB
    drop table tblARev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •