May 25, 2012 at 7:06 am
Hi All
I am having an issue grasping the behaviour of Joins.
DDL:
CREATE TABLE [dbo].[T1](
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL)
GO
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (1, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (2, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (3, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (4, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (5, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (22, 44)
GO
CREATE TABLE [dbo].[T2](
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL
)
GO
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (1, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (2, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (3, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (4, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (5, 2)
Ok, I understand the behaviour of
select * from T1
inner/left outer/right outer join T2
on T1.Col1 = T2.Col1
I am having a problem with
select * from T1
inner join T2
on T1.Col1 = '1'
How does SQL Server process a Query with a join when you have on column = an actual values(s)
Thanks
May 25, 2012 at 7:36 am
select * from T1
inner join T2
on T1.Col1 = '1'
It will give cross join..with all rows with value 1 for col1 will cross join with table t2..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 25, 2012 at 8:10 am
When you're defining a join, you should define the columns that join the two tables together. So that you're ON condition is something along the lines of
A.ID = B.ID
If you don't provide some type of criteria for the second table, you just get everything from there, combined with whatever you filtered on in the first table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2012 at 11:20 am
Grant Fritchey (5/25/2012)
When you're defining a join, you should define the columns that join the two tables together. So that you're ON condition is something along the lines ofA.ID = B.ID
If you don't provide some type of criteria for the second table, you just get everything from there, combined with whatever you filtered on in the first table.
Thanks
So in the case of where A.ID = 'a value(s)'
The results will be everything from B and the results from A, minus what wouldn't return from A, based on my
on A.ID = 'value(s)'
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply