Advice with INNER verses LEFT joins

Question

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?

Answer 1

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.