where and inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where and inner join

Author

Topic

tracmonali
Starting Member

16 Posts

Posted - 2012-05-17 : 08:07:10

I am joining 2 tables which also has a where clause. My questions is can I join with 2 conditions? one the key on which you perform join and also have the where condition as join? Is that possible? how are these 2 conditions diff?

ex:
select * from table 1
inner join table 2
on t1.col1 = t2.col1
where t1.col3 = 'some val'

is this same as
select * from table 1
inner join table 2
on t1.col1 = t2.col1 AND t1.col3 = 'some val'

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-17 : 08:35:32

Hi Both are different:

In applying inner join these will produce a same result.
But if you perfrom LEFT JOIN or Right JOIN you will see the different result

Try below code

create table #emp (emp int,ename varchar(20),deptid int)

create table #dept (dept int,Deptname varchar(20))

insert into #emp(emp,ename,deptid)
select 101,'vijay',10
union all
select 102,'vikas',12
union all
select 103,'vinod',20

insert into #dept (dept,Deptname)
select 10,'sales'
union all
select 20,'HR'
union all
select 30,'Marketing'

--select * from #emp

--select * from #dept

select * from #dept d left join #emp e on d.dept= e.deptid and e.ename ='vijay'

select * from #dept d left join #emp e on d.dept= e.deptid where e.ename ='vijay'

Vijay is here to learn something from you guys.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-17 : 10:49:40

For an INNER join putting the predicate on the join condition or in the where clause may be syntactically different. Howewver, they are semantically they are the same. As Vijays2 points out, when doing an OUTER join they are no longer semantically the same.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources