When to use inner join and when to left join?

Question

0.00/5 (No votes)

See more:

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.

Posted 18-Jul-12 21:35pm

udusat13

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-07-18T21:55:00

The LEFT OUTER JOIN includes all rows in the left table in the results, whether or not there is a match on the join column in the right table.
WHEREAS
This INNER JOIN is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.

You should read about JOINS. Start from here: MSDN: JOIN fundamentals[^]

Move on to:
MSDN: Using Outer Joins[^]
MSDN: Using Inner Joins[^]

BobJanova · Accepted Answer · 2012-07-18T22:45:00

You should use a left join where the table on the right is adding extra, optional information to the table on the left. When you use a left join, if the join-column entry doesn't exist in the right table, all the columns from the right table are filled out with nulls in that row of the result.

An inner join will eliminate those rows entirely. You should use that when you require that both tables have data for the item, for example if you're looking up dispatched orders and you are joining from an orders and deliveries table, it might make sense to inner join so that undelivered orders are not in the result.

In your case, the answer comes down to whether you want to have a result returned when an inquiry doesn't have an event associated with it yet. I'd say you probably do so I'd use a left join in this situation.

SalCon · Accepted Answer · 2012-07-18T23:54:00

Solution 3

I think joins are best understood visually.
Check these links.

1. CP[^]
2. Coding Horror[^]

Posted 18-Jul-12 23:54pm

SalCon

Comments

udusat13 19-Jul-12 8:26am

Thanks for links.

When to use inner join and when to left join?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0