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.

asked 17 Aug '12, 01:42

Uday's gravatar image

Uday
30112
accept rate: 0%

edited 17 Aug '12, 02:49

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297


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.

permanent link

answered 17 Aug '12, 02:45

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25

question asked: 17 Aug '12, 01:42

question was seen: 11,456 times

last updated: 17 Aug '12, 02:49