Closed Thread
Results 1 to 3 of 3

Thread: Joins

  1. #1
    NeedHelp Guest

    Joins

    Can someone explain to me what joins are, how to use them and why you would use them? I have heard of an inner join but have no idea what it is. Is this the only one?

  2. CODECALL Circuit advertisement

     
  3. #2
    brackett is offline Programmer
    Join Date
    May 2006
    Posts
    192
    Rep Power
    22
    Joins basically combine 2 tables. The three most common are:
    INNER JOIN: intersection between 2 sets
    LEFT OUTER JOIN: all rows from left side, matching or NULL rows from right
    RIGHT OUTER JOIN: same as left, just in reverse

    The others are less common, and may not be available with all platforms:
    FULL OUTER JOIN: all rows from left side, matching or NULL rows from right; plus all rows from right side, matching or NULL rows from left (basically, a UNION ALL between LEFT and RIGHT joins...in fact, if your DB doesn't support FULL joins, you can do that)
    CROSS JOIN: all possible matches (the Cartesian product) between left and right side. This one is rarely useful, IME.

    For an example, let's start with 2 simple tables:
    Code:
    Employee
    [Id]     [Name]    [SalaryId]
    1         Mark        2
    2         Jim           1
    3         Bob          6
    4         Steve       NULL
    
    Salary
    [Id]     [Amount]
    1         $100000
    2         $80000
    3         $70000
    Okay, so to get the salary of each employee, we can do:
    Code:
    SELECT Employee.Name, Salary.Amount FROM Employee INNER JOIN Salary ON Employee.SalaryId = Salary.Id
    
    [Name]    [Amount]
    Mark        $80000
    Jim           $100000
    You can see that (a) we have to give a JOIN criteria to say what links the 2 tables together (in most cases, this will be your foreign key), and (b) neither Bob nor Steve were returned because their SalaryId is not in the Salary table.

    If we wanted to see Bob or Steve in the result set, we'd need an OUTER JOIN:
    Code:
    SELECT Employee.Name, Salary.Amount FROM Employee LEFT OUTER JOIN Salary ON Employee.SalaryId = Salary.Id
    
    [Name]    [Amount]
    Mark        $80000
    Jim           $100000
    Bob          NULL
    Steve       NULL
    Now, we have Bob and Steve in the result set, but their Amounts are NULL because we don't know what they are. Notice now that we don't have our 3rd salary level, $70000, listed because no one is getting paid that.
    Code:
    SELECT Employee.Name, Salary.Amount FROM Employee RIGHT OUTER JOIN Salary ON Employee.SalaryId = Salary.Id
    
    [Name]    [Amount]
    Mark        $80000
    Jim           $100000
    NULL        $70000
    Now, we have all of the salary amounts. If we want both all the names and all the salary amounts:
    Code:
    SELECT Employee.Name, Salary.Amount FROM Employee FULL OUTER JOIN Salary ON Employee.SalaryId = Salary.Id
    
    [Name]    [Amount]
    Mark        $80000
    Jim           $100000
    Bob          NULL
    Steve       NULL
    NULL        $70000
    I can't think of many reasons to use a CROSS JOIN right now, but here's an example anyway:
    Code:
    SELECT Employee.Name, Salary.Amount FROM Employee CROSS JOIN Salary
    
    [Name]    [Amount]
    Mark        $80000
    Mark        $100000
    Mark        $70000
    Jim           $80000
    Jim           $100000
    Jim           $70000
    ...
    As you can see, we didn't have to give a JOIN criteria, because a CROSS JOIN makes all possible combinations. I didn't fully list out the result set, but you can see that it'll continue that pattern for all employees.

    To be honest, I think I have used a CROSS JOIN in one or two instances, but it's pretty rare and I can't think of why I did it right now.

  4. #3
    Saint is offline Learning Programmer
    Join Date
    Aug 2006
    Posts
    63
    Rep Power
    0
    Good description. Joins are very common in SQL.
    Hi >> Saint

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. A new moderator joins the team
    By John in forum Announcements
    Replies: 16
    Last Post: 10-25-2010, 09:28 PM
  2. Using Set Theory to Understand SQL Joins
    By WingedPanther in forum Tutorials
    Replies: 21
    Last Post: 09-23-2010, 01:42 PM
  3. SQL joins
    By chili5 in forum Database & Database Programming
    Replies: 4
    Last Post: 05-18-2009, 07:55 AM
  4. Joins vs. Separate Queries
    By BASHERS33 in forum Database & Database Programming
    Replies: 18
    Last Post: 05-07-2009, 08:32 AM

Tags for this Thread

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