When to use inner join and when to left join?

[Former Member]

Hi Expert,

In My nridubai.com website,

I am searching inquiries based on event. inquiry is associated with 1 or more events.

There are 2 tables inquiry(inquiry_id,comapnyname,category,..) and inquiry_event(id,inquiry_id,event_id).

I store all inquiry information in inquiry table. and each inquiry associated event(may be one or more) storing in secondory table inquiry_event table.

now when i want to search inquiry based on event which join in useful / Inner Join or left join ?

Currently i am using inner join working fine .

eg.

SELECT *
  FROM inquiry inq
 INNER JOIN inquiry_event inqevent ON inq.inquiry_id = inqevent.inquiry_id 
 WHERE inqevent.event_id=10

Now here can i use left join ?.

Please Help me.

thanks in advance.

[MarkCulp]

First let me point out that when you say "LEFT JOIN" you are really referring to "LEFT OUTER JOIN", so your question is really asking when should you use an INNER JOIN vs an OUTER JOIN.

You did not state what the relationship is between your inquiry table an inquiry_event table but I am going to presume that inquiry_id in inquiry_event has a foreign key to associated field in the inquiry table?

So to answer your question, it all depends! (doesn't it always 🙂

An INNER JOIN will only produce a row when there is a row on both sides that match the ON condition. In this case this means that the output of the join will have a row for each matching row between the two tables. Using the above assumption about the foreign key, this means that you will not see a row for any inquiry_id in the inquiry table that does not have a row in the inquiry_event table.

A [LEFT] OUTER JOIN on the other hand will fill in NULLs for each column in the nullable side of the outer join (the right side - inquiry_event - in this case) when there is no matching row and generate a row in the result set for those missing rows from the nullable side.

So in cases that you want to see all rows from your inquiry table, even in cases when there is no inquiry_event for the inquiry_id, then you would want to use a LEFT OUTER JOIN. If you don't need to see those missing inquiry_id rows then an INNER JOIN is what you likely want.

Finally, if your situation is that every inquiry_id in the inquiry table always has at least one matching row in the inquiry_event table then there will be no difference between the results of using INNER JOIN and [LEFT] OUTER JOIN.

You can read more about joins in the documentation.