I can't wrap my head around joins

Question

So, alright, I have a few tables. My current query runs against a "historical" table. I want to do a join of some kind to get the most recent status from my Current table. These tables share a like column, called "ID"

Here's the structure

ddCurrent
    -ID
    -Location
    -Status
    -Time

ddHistorical
    -CID (AI field to keep multiple records per site)
    -ID
    -Location
    -Status
    -Time

My goal now is to do a simple join to get all the variables from ddHistorical and the current Status from ddCurrent.

I know that they can be joined on ID since both of them have the same items in their ID tables, I just can't figure out which kind of join is appropriate or why?

when you say all the values from the ddHistorical, you need a left outer join that ddHistorical is in left side — , Jun 26 '13 at 15:47

score 0 · Answer 1 · answered Jun 26 '13 at 15:47

0

If there is always a current field then a simple INNER JOIN will do it

SELECT a.CID, a.ID, a.Location, a.Status, a.Time, b.Status
FROM ddHistorical a
INNER JOIN ddCurrent b
ON a.ID = b.ID

answered Jun 26 '13 at 15:47

Kickstart

21,403
2
21
33

score 0 · Answer 2 · answered Jun 26 '13 at 15:49

An INNER JOIN will omit any ddHistorical rows that don't have a corresponding ID in ddCurrent.

A LEFT JOIN will include all ddHistorical rows, even if they don't have a corresponding ID in ddCurrent, but the ddCurrent values will be null (because they're unknown).

Also note that a LEFT JOIN is just a specific type of outer join. Don't bother with the others yet - 90% or more of what you'll ever do will be INNER or LEFT.

To include only those ddHistorical rows where the ID is in ddCurrent:

SELECT h.CID, h.ID, h.Location, h.Status, c.Status, h.Time
FROM ddHistorical h
INNER JOIN ddCurrent c ON h.ID = c.ID

If you want to include ddHistorical rows even if the ID isn't in ddCurrent:

SELECT h.CID, h.ID, h.Location, h.Status, c.Status, h.Time
FROM ddHistorical h
LEFT JOIN ddCurrent c ON h.ID = c.ID

If all ddHistorical rows happen to match an ID in ddCurrent, note that both queries will return the same result.

This is an awesome answer :) Now... what does an outer join do? — user2249610, Jun 26 '13 at 15:57
There's a great visual representation of joins [here](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). Take a look at the first four, but note that what they call `OUTER` is also called a `FULL OUTER`, and that `LEFT` or `RIGHT` are also types of `OUTER`. In my example above you can use `LEFT OUTER JOIN ddCurrent` and it's the same thing as `LEFT JOIN ddCurrent` (without the `OUTER`) - the `OUTER` is optional because a `LEFT` is, by definition, `OUTER`. This can be confusing to explain in words, so definitely take a look at the pictures in the link. — Ed Gibbs, Jun 26 '13 at 16:04

score 0 · Accepted Answer · answered Jun 26 '13 at 16:15

I'm sure someone may provide a specific link that goes into great detail explaining, but I'll try to summarize it this way. When writing a query, I try to list the tables from the position of what table do I want to get data from and have that as my first table in the "FROM" clause. Then, do "JOIN" criteria to other tables based on relationships (such as IDs). In your example

FROM
   ddHistorical ddH
      INNER JOIN ddCurrent ddC
         on ddH.ID = ddC.ID

In this case, INNER JOIN (same as JOIN) the ddHistorical table is the left table(listed first for my styling consistency and indentation) and ddCurrent is the right table. Notice my ON criteria that joins them together is also left alias.column = right alias table.column -- again, this is just for mental correlation purposes.

an Inner Join (or JOIN) means a record MUST have a match on each side, otherwise it is discarded.

A LEFT JOIN means give me all records in the LEFT table (ddHistorical in this case), regardless of a matching in the right-side table (ddCurrent). Not practical in this example.

A RIGHT JOIN is the reverse... give me all records from the RIGHT-side table REGARDLESS of a matching record in the left side table. Most of the time you will see LEFT-JOINs more frequently than RIGHT-JOINs.

Now, a sample to mentally get the left-join. You work at a car dealership and have a master table of 10 cars that are sold. For a given month, you want to know what IS NOT selling. So, start with the master table of all cars and look at the sales table for what DID sell. If there is NO such sales activity the right-side table will have NULL value

select
      M.CarID,
      M.CarModel
   from
      MasterCarsList M
         LEFT JOIN CarSales CS
            on M.CarID = CS.CarID
           AND month( CS.DateSold ) = 4
   where
      CS.CarID IS NULL

So, my LEFT join is based on a matching car ID -- AND -- the month of sales activity is 4 (April) as I may not care about sales for Jan-Mar -- but would also qualify year too, but this is a simple sample.

If there is no record in the Car Sales table it will have a NULL value for all columns. I just happen to care about the car ID column since that was the join basis. That is why I am including that in the WHERE clause. For all other types of cars that DO have a sale it will have a value.

This is a common approach you will see in querying where someone looking for all regardless of other... Some use a where NOT EXIST ( subselect ), but those perform slower because they test on every record. Having joins is much faster.

Other examples may be you want a list of all employees of a company, and if they had some certification / training to show it... You still want all employees, but LEFT-JOINING to some certification/training table would expose those extra field as needed.

select
      Emp.FullName,
      Cert.DateCertified
   FROM
      Employees Emp
         Left Join Certifications Cert
            on Emp.EmpID = Cert.EmpID

Hopefully these samples help you understand better the relationship for queries, and now to actually provide answer for your needs.

If what you want is a list of all "Current" items and want to look at their historical past, I would use current FIRST. This might be if your current table of things is 50, but historically your table had 420 items. You don't care about the other 360 items, just those that are current and the history of those.

select
      ddC.WhateverColumns,
      ddH.WhateverHistoricalColumns
   from
      ddCurrent ddC
         JOIN ddHistorical ddH
            on ddC.ID = ddH.ID

I can't wrap my head around joins

3 Answers3