SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,332
    Mentioned
    192 Post(s)
    Tagged
    4 Thread(s)

    Good grasp on the concept of Database JOIN

    I have worked with databases (or would that be databii?) of many flavors and for many, many years. And SQL is a language I am not afraid to approach. But by no means do I profess to be well versed in it.

    I have a continuous struggle fully grasping the concept (and detailed implications) of the database join. My conceptual impression is that a JOIN represents a 'grafting' of two tables, linking them by a common field.
    Is that close to correct?

    Can someone offer a clear, descriptive explanation and some good analogy to help crystallize the concept?


    I am calling on some of our Database experts, like @r937 ; to help me here.

    ATdhvaannkcse
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParkinT View Post
    My conceptual impression is that a JOIN represents a 'grafting' of two tables, linking them by a common field.
    that's pretty much it

    except that it's the rows that get grafted together, not the tables

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

  3. #3
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,332
    Mentioned
    192 Post(s)
    Tagged
    4 Thread(s)
    Thanks, @r937 ;
    But can you offer an example? A sample?
    I don't mean to appear dense but this is one of those areas where every time I attempt to apply what I think I know, it confounds me.

    I understand the idea of normalizing a relational database. So, assuming I have these fictitious tables:
    Code:
    //users
    id, fname, lname, country, fav_color
    
    //transactions
    id, order_number, user_id, item_id
    
    //items 
    id, color
    If I wanted to get the number of orders each user placed that included an item of their favorite color. Is this a good example of where to use a JOIN?

    I want to keep this discussion simple. At the same time, PLEASE correct my illustration where you can better facilitate edifying me.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParkinT View Post
    But can you offer an example? A sample?
    the illustrations from chapter 3 of my sitepoint book, if you'll excuse me for saying, are optimally simple yet comprehensive... look for the join diagrams part way through Simply SQL: The FROM Clause

    caution: code samples in that online article are b0rked, they are ~not~ the same as in the original printed book (the "leading commas" part is especially dumbfounding)


    Quote Originally Posted by ParkinT View Post
    If I wanted to get the number of orders each user placed that included an item of their favorite color. Is this a good example of where to use a JOIN?
    yes

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

  5. #5
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Another resource that might help:

    http://www.sitepoint.com/understandi...ysql-database/
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  6. #6
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,332
    Mentioned
    192 Post(s)
    Tagged
    4 Thread(s)
    Thank you both (@r937 ;, @Force Flow for your guidance. That article *was* very helpful. The simple Venn Diagrams are a great boon.

    The subtlety of LEFT versus RIGHT versus OUTER is still a bit foggy to me. It appears a choice between LEFT and RIGHT is quite arbitrary because it is relative to the order in which you list the tables.

    I need to find opportunities to practice and explore this. I suspect that is part of my problem; the need does not arise often enough for it to become 'second nature'.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParkinT View Post
    It appears a choice between LEFT and RIGHT is quite arbitrary because it is relative to the order in which you list the tables.
    well spotted

    i never use right outer join... perhaps cultures where the written language is read from right to left do so more often
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •