Advice with INNER verses LEFT joins

This is probably a simple question for someone who is an sql database administrator, but I'm a C# guy who only dabbles into the databases long enough to get them working when necessary.

I've got a new database I designed that only has a very little bit of data in it.

I need a query that will be used for generating my view, but I can never seem to understand how/when to use an INNER verses the LEFT join.

A Packet can have multiple Request entries (one person requests 5 different parts), and each Request entry can have different Action entries (on hold, cancelled, special order, complete, etc).

I'd like to create a query that produces the following Data Table:

SELECT
  P.EmpID AS Requestor, P.DateStamp AS Submitted,
  T.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty,
  RT.Description AS ReasonType, S.Description AS Status, A.EmpID AS Stator, A.DateStamp AS Stated, R.MTF
FROM Packet AS P
  LEFT OUTER JOIN Request AS R ON (R.PacketID=P.ID)
  LEFT OUTER JOIN Action AS A ON (A.RequestID=R.ID)
  LEFT OUTER JOIN RequestType AS T ON (R.RequestTypeID=T.ID)
  LEFT OUTER JOIN Line AS L ON (R.LineID=L.ID)
  LEFT OUTER JOIN ReasonType AS RT ON (R.ReasonTypeID=RT.ID)
  LEFT OUTER JOIN Status AS S ON (A.StatusID=S.ID)

This returns 5 rows, but there are a few NULL entries for Status, Stator, and Stated.

So, I tried writing this with an INNER JOIN:

SELECT
  P.EmpID AS Requestor, P.DateStamp AS Submitted,
  T.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty,
  RT.Description AS ReasonType, S.Description AS Status, A.EmpID AS Stator, A.DateStamp AS Stated, R.MTF
FROM Packet AS P
  INNER JOIN Request AS R ON (R.PacketID=P.ID)
  INNER JOIN Action AS A ON (A.RequestID=R.ID)
  INNER JOIN RequestType AS T ON (R.RequestTypeID=T.ID)
  INNER JOIN Line AS L ON (R.LineID=L.ID)
  INNER JOIN ReasonType AS RT ON (R.ReasonTypeID=RT.ID)
  INNER JOIN Status AS S ON (A.StatusID=S.ID)

The NULL entries are now gone, but now I only have 3 rows returned.

How do I know which version I should be using? ...or, should I be using a combination of LEFT and INNER joins?

Answers


How do I know which version I should be using?

Only you can answer that question. Did you want the 5 rows with null or the 3 rows with no nulls?

As you noted from your results, a LEFT JOIN will return a record even when there is no matching record in the table you are joining on (hence the null values). An INNER JOIN will only return a record if there is a matching record in the table you are joining on (which is why you see 3 results instead of 5).

Take a look at this for an excellent visual explanation of joins.


How do I know which version I should be using?

When the join condition fails to give any match do you want NULL values for the missing columns or do you want the row to be absent in the result set?

  • If you want NULLs for the missing columns, use an outer join.
  • If you want the entire row to be absent, use an inner join.

Should I be using a combination of LEFT and INNER joins?

You should consider for each join whether you want it to be an inner or an outer join.


Need Your Help

FaceBook post on user wall not working in IE

php facebook facebook-graph-api azure

I am trying to post on user's wall in an facebook app, this is not working in Internet Explorer, in other browser it is working.

Display nothing when I import .DAE file from blender 2.5 to Papervision 3D 2.1 why?

flash 3d blender papervision3d

I have created a cube and applied materials and texture in blender (2.5). But when I import that model to Papervision 3d (2.1), I don't see anything - just a blank screen. On the other hand, if I u...