Joins in SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Joins in SQL

Author

Topic

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-30 : 01:19:47

Select * from
table1
left join
table2
on table.id=table2.fid
where
table2.column3 >5

is there any significance in the left join
or inner join is enough

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 01:27:14

Nope no significance as long as you have the where condition. you could better do this with an inner join as always only records with matching values in table2 will be returned because of where condition. What is your reqmnt by the way?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-30 : 01:37:34

Read the books online for diff between inner join and left join.
The following example may help u understand diff between inner join and left join . Then u can know where to use inner join and left join.

DECLARE @TABLE1 TABLE ( ID INT, FNAME VARCHAR(40), LNAME VARCHAR(40))
INSERT INTO @TABLE1
SELECT 1,'rakesh','paleti' UNION ALL
SELECT 2,'raja', 'bandi' UNION ALL
SELECT 3,'durga','kadavakollu'UNION ALL
SELECT 4,'anuradha','ravala'

--SELECT * FROM @TABLE1

DECLARE @TABLE2 TABLE ( FID INT, FNAME VARCHAR(40), MOBILE VARCHAR(16))
INSERT INTO @TABLE2
SELECT 1,'rakesh','9883433430' UNION ALL
SELECT 2, 'raja','9885433847'
--SELECT * FROM @TABLE2

SELECT T1.ID,T1.FNAME,T2.MOBILE
FROM @TABLE1 T1
INNER JOIN @TABLE2 T2 ON ( T2.FID = T1.ID)

SELECT T1.ID,T1.FNAME,T2.MOBILE
FROM @TABLE1 T1
LEFT JOIN @TABLE2 T2 ON ( T2.FID = T1.ID)

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-30 : 02:37:03

I just want to confirm that if there is a filtering based on the second table only rows with values in second table will be returned and hence there is no need of left join. Inner join is enough. Am i correct ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 02:42:24

quote:
Originally posted by shobinmathew

I just want to confirm that if there is a filtering based on the second table only rows with values in second table will be returned and hence there is no need of left join. Inner join is enough. Am i correct ?

yup. thats enough. But if your requirement suggests that you need to retrieve records when field from table2 matches filter condition if one exists for join condition with table1 and also return any records in table1 which doesnt have a matching record in table2 regardless of filter then you can use like this:-

Select * from
table1 
left join
table2
on table.id=table2.fid
where
(table2.column3 >5 or table2.column3 is null)

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-30 : 05:21:19

thanks

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-30 : 07:54:55

eg1:

Select * from
table1
left join
table2
on table.id=table2.fid
where
table2.column3 >5

eg2:
Select * from
table1
inner join
table2
on table.id=table2.fid
where
table2.column3 >5

eg3:

Select * from
table1
inner join

(select * from table2 where table2.column3 >5) as table2
on table1.id=table2.fid

eg4:

Select * from
table1
inner join
table2
on table.id=table2.fid
and

table2.column3 >5

ALl the three returns same data. Of these eg3 AND 4 will be faster.

Among 3 qnd 4 which will be more faster

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources