SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a few Qs about Joins

    hi,

    i'm learning sql and i came accross the subject of join.

    i understand the concept behind joins. it's hard. you just want to join two or more tables.
    nice.

    from my understanding, left join and right join are pretty similar. they will return rows even if the left table has empty results or if it is the right table that has empty results.

    Just a little thing to clarify for me: left/right based on what? on the horizontal axis? on the order of the tables in your db? on the order in which your tables appear in your query?

    Now, more important. It seems that there a re many names for join. Inner Join, Natural Join, Join etc...

    How many Join species are there? What's their specificites.

    Sorry for these rather vague questions, but I hope you will be able to shed some lights.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, left/right depending on the sequence in which the tables are named in the FROM clause

    SELECT a.foo, b.bar FROM a LEFT OUTER JOIN b ON a.id = b.aid

    SELECT a.foo, b.bar FROM a RIGHT OUTER JOIN b ON a.id = b.aid

    in both cases, table a is on the left, table b is on the right

    there are many types of join --

    -- INNER, LEFT/RIGHT/FULL OUTER, and CROSS

    -- NATURAL any of the above to use identically-named columns to join

    -- "self join" if the table is joined to itself

    -- "theta join" if something other than equality is used as the join condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mmh... let me get it straight:

    -INNER JOIN
    -LEFT FULL
    -RIGHT FULL
    -LEFT OUTER
    -RIGHT OUTER
    -CROSS

    I still don't understand Natural though

    Why would you join a table to itself?

    Code:
    yes, left/right depending on the sequence in which the tables are named in the FROM clause
    
    1: SELECT a.foo, b.bar FROM a LEFT OUTER JOIN b ON a.id = b.aid
    
    2: SELECT a.foo, b.bar FROM a RIGHT OUTER JOIN b ON a.id = b.aid
    
    in both cases, table a is on the left, table b is on the right
    So in 1 you get all the data even when a has nothing to return, and in 2 you get all the data even if b has nothing to return?

    What's the difference between OUTER and FULL rudy?

    Could you provide a theta join example?

    lol... I bother you.

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jjshell
    Could you provide a theta join example?
    LOL

    I can provide you with two examples of theta joins
    But don't worry if you don't understand it straight away, it is a bit advanced for a beginner

  5. #5
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why don't you specify the JOIN part? You just use a coma to join your tables, which, I've been told, isn't the best practice.

    I can see subqueries and where clauses. But is the thetajoin thingy just about using >= <= > < instead of = ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jjshell
    mmh... let me get it straight:

    -INNER JOIN
    -LEFT FULL
    -RIGHT FULL
    -LEFT OUTER
    -RIGHT OUTER
    -CROSS
    small correction: there is no LEFT FULL or RIGHT FULL, there is only FULL OUTER

    a natural join will join tables based on any columns that have the same name in both tables

    note: i strongly advise people never to use natural joins

    Quote Originally Posted by jjshell
    why would you join a table to itself?
    best example: supervisor/employee (both are in the same table, each employee has a foreign key to his or her supervisor, the foreign key is null for the top boss)

    Quote Originally Posted by jjshell
    So in 1 you get all the data even when a has nothing to return, and in 2 you get all the data even if b has nothing to return?
    actually, no, it's the other way around

    Quote Originally Posted by jjshell
    What's the difference between OUTER and FULL rudy?
    there are three types of OUTER -- LEFT OUTER, RIGHT OUTER, and FULL OUTER

    Quote Originally Posted by jjshell
    Could you provide a theta join example?
    sure --
    Code:
    select ( select count(*) 
               from mytable 
              where mycol <= moe.mycol ) as rownum
         , * 
      from mytable as moe
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jjshell
    why don't you specify the JOIN part?
    Because they are not explicit "joins" the same way that a "LEFT OUTER JOIN" is a join. They are subqueries.

    Quote Originally Posted by jjshell
    You just use a coma to join your tables, which, I've been told, isn't the best practice.
    Well, there are good and bad practices. Sometimes the best people use the worst practices If it were an explicit join it would probably be best to use the "new" style syntax and use the keyword JOIN, yes. In those examples the syntax shown is possibly the only way to get the results desired using (an older) standard SQL.

    Quote Originally Posted by jjshell
    I can see subqueries and where clauses. But is the thetajoin thingy just about using >= <= > < instead of = ?
    Exactly.

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah.... now I feel that I start to get something at this Join world.

    thanks to you guys.

    What does FULL OUTER that RIGHT/LEFT OUTER don't?

    Maybe I'm asking too much, but an example of each case (data, query, stuff returned) would be great.

    No need to dig deeper into natural Joins then. I guess the exact same thing can be done with INNER/RIGHT/LEFT/FULL right?


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jjshell
    What does FULL OUTER that RIGHT/LEFT OUTER don't?
    FULL OUTER returns unmatched rows from both tables, whereas LEFT OUTER and RIGHT OUTER return unmatched rows only from one of them

    Quote Originally Posted by jjshell
    I guess the exact same thing can be done with INNER/RIGHT/LEFT/FULL right?
    the exact same thing as what?

    by the way, have you given any thought to maybe searching for a tutorial on joins?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes rudy, i even bought the SAMS teach yourself sql in 10 min book. The thing is I can't get it from the tuts I have found and the book (actually, tutorials got me confused because the book doesn't mention all the possible joins).

    That's why I ended up initiating this thread.

    But if you want to point me to a good tut, I'l be happy to follow your advice.

    Same thing as a natural join.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    here are two --

    Getting the Right Data with SQL Joins
    Discusses inner, left outer, right outer, full outer, cross, and self-joins.

    Understanding SQL Joins
    Written for MySQL but applicable to any database. Inner, cross, left and right outer joins. Doesn't actually use INNER syntax. Nice examples.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy

  13. #13
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    there are many types of join --

    -- INNER, LEFT/RIGHT/FULL OUTER, and CROSS

    -- NATURAL any of the above to use identically-named columns to join

    -- "self join" if the table is joined to itself

    -- "theta join" if something other than equality is used as the join condition
    Aren't you missing one kind of join? hehe

    UNION is a join operator, it joins two sets which have attribute equality:

    Code:
    select a, b from thisserver.dbo.tableA
    
    UNION
    
     select a, b from thatserver.dbo.tableA


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
  •