inner join and outer join


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: inner join and outer join

Hybrid View

  1. #1
    Join Date
    Aug 2006
    Posts
    113

    inner join and outer join

    what is this inner join and outer join in mssql server

    give me some example

  2. #2
    Join Date
    Sep 2006
    Posts
    10

    hi syedwna

    Joins can be categorized as:

    Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
    Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

    Outer joins. Outer joins can be a left, a right, or full outer join.
    Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

    LEFT JOIN or LEFT OUTER JOIN
    The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

    RIGHT JOIN or RIGHT OUTER JOIN.
    A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

    FULL JOIN or FULL OUTER JOIN.
    A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

    Cross joins.
    Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

    For example, here is an inner join retrieving the authors who live in the same city and state as a publisher:

    USE pubs
    SELECT a.au_fname, a.au_lname, p.pub_name
    FROM authors AS a INNER JOIN publishers AS p
    ON a.city = p.city
    AND a.state = p.state
    ORDER BY a.au_lname ASC, a.au_fname ASC


    hope this will help
    best regards,
    rana

  3. #3
    Join Date
    Sep 2006
    Posts
    10

    here also examples to show the different outer join types

    Using Left Outer Joins

    USE pubs
    SELECT a.au_fname, a.au_lname, p.pub_name
    FROM authors a LEFT OUTER JOIN publishers p
    ON a.city = p.city
    ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

    Here is the result set:

    au_fname au_lname pub_name
    -------------------- ------------------------------ -----------------
    Reginald Blotchet-Halls NULL
    Michel DeFrance NULL
    Innes del Castillo NULL
    Ann Dull NULL
    Marjorie Green NULL
    Morningstar Greene NULL
    Burt Gringlesby NULL
    Sheryl Hunter NULL
    Livia Karsen NULL
    Charlene Locksley NULL
    Stearns MacFeather NULL
    Heather McBadden NULL
    Michael O'Leary NULL
    Sylvia Panteley NULL
    Albert Ringer NULL
    Anne Ringer NULL
    Meander Smith NULL
    Dean Straight NULL
    Dirk Stringer NULL
    Johnson White NULL
    Akiko Yokomoto NULL
    Abraham Bennet Algodata Infosystems
    Cheryl Carson Algodata Infosystems

    (23 row(s) affected)

    The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

    Using Right Outer Joins

    USE pubs
    SELECT a.au_fname, a.au_lname, p.pub_name
    FROM authors AS a RIGHT OUTER JOIN publishers AS p
    ON a.city = p.city
    ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

    Here is the result set:

    au_fname au_lname pub_name
    -------------------- ------------------------ --------------------
    Abraham Bennet Algodata Infosystems
    Cheryl Carson Algodata Infosystems
    NULL NULL Binnet & Hardley
    NULL NULL Five Lakes Publishing
    NULL NULL GGG&G
    NULL NULL Lucerne Publishing
    NULL NULL New Moon Books
    NULL NULL Ramona Publishers
    NULL NULL Scootney Books

    (9 row(s) affected)

    An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold fewer than 50 copies:

    USE pubs
    SELECT s.stor_id, s.qty, t.title
    FROM sales s RIGHT OUTER JOIN titles t
    ON s.title_id = t.title_id
    AND s.qty > 50
    ORDER BY s.stor_id ASC

    Here is the result set:

    stor_id qty title
    ------- ------ ---------------------------------------------------------
    (null) (null) But Is It User Friendly?
    (null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
    Variations
    (null) (null) Cooking with Computers: Surreptitious Balance Sheets
    (null) (null) Emotional Security: A New Algorithm
    (null) (null) Fifty Years in Buckingham Palace Kitchens
    7066 75 Is Anger the Enemy?
    (null) (null) Life Without Fear
    (null) (null) Net Etiquette
    (null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
    Mediterranean
    (null) (null) Prolonged Data Deprivation: Four Case Studies
    (null) (null) Secrets of Silicon Valley
    (null) (null) Silicon Valley Gastronomic Treats
    (null) (null) Straight Talk About Computers
    (null) (null) Sushi, Anyone?
    (null) (null) The Busy Executive's Database Guide
    (null) (null) The Gourmet Microwave
    (null) (null) The Psychology of Computer Cooking
    (null) (null) You Can Combat Computer Stress!

    (18 row(s) affected)

    For more information about predicates, see WHERE.

    Using Full Outer Joins
    To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ 2000 provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

    Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

    To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. The following is the query and results of the Transact-SQL full outer join:

    USE pubs
    SELECT a.au_fname, a.au_lname, p.pub_name
    FROM authors a FULL OUTER JOIN publishers p
    ON a.city = p.city
    ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

    Here is the result set:

    au_fname au_lname pub_name
    -------------------- ---------------------------- --------------------
    Reginald Blotchet-Halls NULL
    Michel DeFrance NULL
    Innes del Castillo NULL
    Ann Dull NULL
    Marjorie Green NULL
    Morningstar Greene NULL
    Burt Gringlesby NULL
    Sheryl Hunter NULL
    Livia Karsen NULL
    Charlene Locksley NULL
    Stearns MacFeather NULL
    Heather McBadden NULL
    Michael O'Leary NULL
    Sylvia Panteley NULL
    Albert Ringer NULL
    Anne Ringer NULL
    Meander Smith NULL
    Dean Straight NULL
    Dirk Stringer NULL
    Johnson White NULL
    Akiko Yokomoto NULL
    Abraham Bennet Algodata Infosystems
    Cheryl Carson Algodata Infosystems
    NULL NULL Binnet & Hardley
    NULL NULL Five Lakes Publishing
    NULL NULL GGG&G
    NULL NULL Lucerne Publishing
    NULL NULL New Moon Books
    NULL NULL Ramona Publishers
    NULL NULL Scootney Books

    (30 row(s) affected)



    note: all the above data are from sql help

    best regards,
    rana

  4. #4
    Join Date
    Aug 2006
    Posts
    113

    syedwna

    u mean to say like this

    student table------>st

    uid name
    1 syed


    course table--------->cr

    uid course
    1 maths

    select st.name,cr.course from st innerjoin st.uid=cr.uid

  5. #5
    Join Date
    Sep 2006
    Posts
    10
    select st.name,cr.course from st innerjoin cr on st.uid=cr.uid

    ya thats it

  6. #6
    Join Date
    Aug 2006
    Posts
    113

    syedwna

    i understood now in ur one line more than any google or yahoo stuff

    thanks thanks

    in the same way for the above example can u write for other joins,it will help not for me only but whoever reads this thread

    Regards
    Syed

  7. #7
    Join Date
    Sep 2006
    Posts
    10

    full example

    you are welcomed

    now i will post a full example on all (using your tables but with more data to show the different output)

    student table------>st

    uid name
    1 syed
    2 nana
    3 bob
    4 jack


    course table--------->cr

    uid cid course
    1 1 math
    2 1 math
    NULL 2 Physics


    innner join:
    select st.name,cr.course from st innerjoin cr on st.uid=cr.uid
    will give:
    syed math
    nana math


    left outer join:
    select st.name,cr.course from st LEFT OUTER JOINcr on st.uid=cr.uid
    will give:
    syed math
    nana math
    bob NULL
    jack NULL

    right outer join:
    select st.name,cr.course from st RIGHT OUTER JOIN cr on st.uid=cr.uid
    will give:
    syed math
    nana math
    NULL Physics


    outer join:
    select st.name,cr.course from st FULL OUTER JOINcr on st.uid=cr.uid
    will give:
    syed math
    nana math
    bob NULL
    jack NULL
    NULL Physics


    best regards,
    rana

  8. #8
    Join Date
    Aug 2006
    Posts
    113

    syedwna

    i understood complety what u wrote

    1)inner join ok
    2)outer join

    what is the use of outer join because it will give the values where the ids doesnt match whether it is left or rigth or full

  9. #9
    Join Date
    Sep 2006
    Posts
    10

    good

    good question,
    in fact it didnt happen that i have ever needed any kind of outer join,
    but being there it should have an application; right?


Similar Threads

  1. Problem with stored procedure
    By yip in forum Database
    Replies: 1
    Last Post: 09-24-2002, 01:31 AM
  2. Outer Join in SQL Server
    By Ritu in forum Database
    Replies: 1
    Last Post: 04-26-2002, 09:19 AM
  3. What is the different between an INNER and OUTER join?
    By Chris Salmon in forum Database
    Replies: 6
    Last Post: 03-07-2002, 08:23 PM
  4. Outer join query
    By Gerald in forum Database
    Replies: 2
    Last Post: 01-25-2002, 03:14 PM
  5. syntax of outer join for sybase
    By nonmin in forum Database
    Replies: 1
    Last Post: 03-15-2001, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center