Which is best Inner join or Left outer join
-
hope i get a solution here
This is my DB structure
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[AName] [varchar](50) NULL,
[address] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TableB](
[Aid] [int] NOT NULL,
[BName] [varchar](50) NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TableB] WITH CHECK ADD CONSTRAINT [FK_TableB_TableA] FOREIGN KEY([Aid])
REFERENCES [dbo].[TableA] ([id])
GO
ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [FK_TableB_TableA]
GO
Shortly TableB Has a reference to TableA
one of my senior has write a query like this
Which is better
select * from tableB b left outer join Tablea a on
a.id =b.Aid
or
select * from tableB b Inner join Tablea a on
a.id =b.Aid
#Edited
forgive me the column Aid is not null
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
The answer depends on what you're trying to accomplish with the query.
a LEFT join merely tells SQL server to return ALL record from the table that's on the left side of the join whether its key matches the key in the table that's right of the join.
See the following link for a great explanation of SQL joins.
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
true,
it's all depend on what result you want from query.
-
for give me i changed my post previous post
since the column 'Aid' is not null
Then every record in TableB has a Aid which is strictly in TableA
now which is better a
left outer join
or
inner join
seems Left outer join is little bit slower than inner join
am i right(please correct me if i am wrong)
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (11/22/2012)
for give me i changed my post previous postsince the column 'Aid' is not null
Then every record in TableB has a Aid which is strictly in TableA
now which is better a
left outer join
or
inner join
seems Left outer join is little bit slower than inner join
am i right(please correct me if i am wrong)
Take for example the following
Table A has the following ID's, 1,2,3,4,5,6,7,8,9
Table B has the following ID's 1,2,3,4,5
Now what do you want to see as your result?
Everything from TableA and then show me if there are any matches, if so Left Outer Join
So you get the following output
TableA TableB
1, 1
2, 2
3, 3
4, 4
5, 5
6, NULL
7, NULL
8, NULL
9, NULL
Or do you just want to show the matching rows, where it has to be in TableA and in TableB is so INNER JOIN
Which gives the following output
TableA, TableB
1, 1
2, 2
3, 3
4, 4
5, 5
It all totally depends on what you want as the output as to what type of join you use.
-
Thanks for your response but what i am going to do is
TableB TO TableA
From your example Every record in TableA is in TableB
when i want to do is
Take all the records in TableB with tableA
what is the Best option
Select * from TableB Left outerJoin TableA on
Tableb.Aid = TableA.ID
is it right
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Well Table B has to have a value it its ID Column which exists in Table A you would an inner join.
If you want TableA to TableB then you would inner or outer depending on your result requirements.
-
Please provide sample data for the tables in question and what you want the output to be based on that sample data so we can get you off on to the right track.
-
What is better "green" or "blue" colour?
Please read and try to understand what different JOIN types do, Then you will be able to answer your question what is "better" INNER or OUTER join.
http://msdn.microsoft.com/en-us/library/ms177634.aspx
<join_type>
Specifies the type of join operation.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.
FULL [ OUTER ]
Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.
LEFT [ OUTER ]
Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
RIGHT [OUTER]
Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.
-
anthony.green (11/22/2012)
Well Table B has to have a value it its ID Column which exists in Table A you would an inner join.If you want TableA to TableB then you would inner or outer depending on your result requirements.
so inner join is the best option isn't it
yes that's what now i am going to do
anthony.green (11/22/2012)
Please provide sample data for the tables in question and what you want the output to be based on that sample data so we can get you off on to the right track.my mistake, In my first post i thought, that the structure of the table is enough next time i will came with Data also
hi "Eugene Elutin"
Thanks for your response
i know very well about joins
please read my scenario and give an opinion about this
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (11/22/2012)
anthony.green (11/22/2012)
Well Table B has to have a value it its ID Column which exists in Table A you would an inner join.If you want TableA to TableB then you would inner or outer depending on your result requirements.
so inner join is the best option isn't it
yes that's what now i am going to do
anthony.green (11/22/2012)
Please provide sample data for the tables in question and what you want the output to be based on that sample data so we can get you off on to the right track.my mistake, In my first post i thought, that the structure of the table is enough next time i will came with Data also
hi "Eugene Elutin"
Thanks for your response
i know very well about joins
please read my scenario and give an opinion about this
It is not a question of which is better - but a question of which is correct for the results you want. In your example, since TableB will always have a value that exists in TableA because of the relationship - an outer join from TableB to TableA would never return anything other than the same results as an inner join.
Going the other direction - from TableA to TableB, an outer join would be valid since you could have rows in TableA without a corresponding row in TableB - and if the results you want need to show everything in TableA then the outer join would be correct.
You should not be looking at which join to be used to 'improve' performance. The decision on which type of join you use is going to be based on what the correct results are for that query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I agree with the poster in this example. A left join in the other direction might make some sense, but B lj A makes no sense given the direction of the foreign key. For this reason, in this case, yes, an inner join is BETTER! As there is no (data) scenario where it will give a different result to the Left Join.
-
thava (11/22/2012)
...hi "Eugene Elutin"
Thanks for your response
i know very well about joins
please read my scenario and give an opinion about this
Hi Thava,
I have read your scenario few times, and I couldn't find any single place where you have stated what you really want your query to do.
So, your concern about JOIN performance is quiet irrelevant so far, as it would be in a question "what is faster a Rocket or a Car".
Yes, good space rocket is much faster then car, however it may be no use if thing you want is it get home from work...
EDITED: I have removed the rest after reconsideration of your scenario...
I wouldn't use word "better".
Taking in count direction of your FK, use of LEFT JOIN is inappropriate as it will only be able to produce exactly same results as INNER JOIN if you go from "child" to "parent".
Actually using RIGHT OUTER JOIN would make it quite different business;-)
-
David McKinney (11/22/2012)
I agree with the poster in this example. A left join in the other direction might make some sense, but B lj A makes no sense given the direction of the foreign key. For this reason, in this case, yes, an inner join is BETTER! As there is no (data) scenario where it will give a different result to the Left Join.FK direction makes use of LEFT JOIN absolutely irrelevant for posted query example, as it will behave exactly the same way as INNER. Does it make INNER join better?
Ok, I guess you may say so, however still, for me, the word "better" sounds a bit inappropriate here...
Something like this:
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id
WHERE t2.id IS NOT NULL
Is INNER JOIN would be "better" there? You can say yes, I would say it's inappropriate use of OUTER JOIN!
It is all semantics...
-
hi Eugene Elutin
thanks for your response i think according to you putting a left join is completely wrong approach there, this is what you said, may i right?
well may be i think i need to change lot of procedures like that, thanks for each every one who took part in this conversation,
Well i Need to know one more thing how To thank people who helped me here, if there is such one i will thank all the posters who participated in this conversation
thanks a lot guys
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply