SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    As the name suggests... trickie's Avatar
    Join Date
    Jul 2002
    Location
    Melbourne, Australia
    Posts
    678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    figuring out query joins

    hi
    can someone give me some insight or point me to some insight about sql joins (inner, outer, whatever)?

    I sort of understand using keys etc, does this have anything to do with them?

    cheers for any help

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2001
    Location
    Canada
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, Joins can get pretty complicated but are fairly easy once you understand the concepts. Basically if you have two tables which are somehow related (ie students table and classes table, so students take classes) and you wanted to find out students and their associated classes you could use a join. Though there will probably be other alternatives.

    The type of join you use depends on the information you're looking for. I'm most familiar with left, right and full joins.

    A left join will take every record from the left table and associate it with some record from the right table. If no record exists in the right table, it will be padded with nulls (left and right are relative to where they appear in the query). Right and Full work in a similar manner (full will pad both right and left with nulls). I suggest www.google.com and run a search for SQL Joins or something.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hey kix, that's a great explanation

    please allow me to polish it a bit

    A left outer join will return every row from the left table and associate it with row(s) from the right table. If no associated row exists in the right table for a given row in the left, the columns in the result row which would have come from the right table will be set to null.

    A right outer join is the same as a left outer join with the table names reversed.

    A full outer join is like a left and right outer join, and may contain rows where the columns from either the left or the right table can be null (but obviously not both at the same time)

    if i may, i'd like to describe some other joins --

    An inner join returns rows only for associated rows, i.e. from both left and right tables. This is the most common join type, as typified by the older "table list" syntax --
    Code:
    select a.foo, b.bar
      from tablea a, tableb b
     where a.id = b.id
    A cross join is the same as an inner join with the join condition missing (as anyone can explain who has inadvertently left off the WHERE clause in the preceding example)

    An equi-join uses equality for the join condition

    A theta join uses some other condition, typically inequality

    A natural join involves joins based on like-named columns, and is rare in the wild

    those are all the ones i can think of

    please holler if those explanations are not clear...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    forgot one --

    a self join is a table joined to itself, and will require table aliases and qualified column names

    you can combine terms, too

    for example, a ranking query, which returns the rank of a row (1st place, 2nd place, etc.) is an inner theta self-join --
    Code:
    select A.theValue
         , count(*) as theRank
      from theTable as A
         , theTable as B
    where A.theValue >= B.theValue
    group by
           A.theValue

  5. #5
    As the name suggests... trickie's Avatar
    Join Date
    Jul 2002
    Location
    Melbourne, Australia
    Posts
    678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks

    Thanks for the info guys!
    i think i understand, could you give me an example say using the above students/classes example?

    cheers


Bookmarks

Posting Permissions

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