JOIN TYPE advice - 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)

JOIN TYPE advice

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-02-21 : 08:19:56

Hi,

I seem to be getting really confused with join types and would love a bit of advice, i'm working with a quite complicated statement which someone helped me to write a while back


SELECT * FROM (SELECT P.vehicleref,P.manufacturer,P.model,P.derivative,P.additionalfreetext,P.isLimited,P.isPriceReduction,P.isSpecial, P.created, P.updated, NMF.term, NMF.milespa, NMF.maintained, NMF.ch,p.source, ROW_NUMBER() OVER 
( PARTITION BY P.VehicleRef ORDER BY NMF.ch ) AS MinCH FROM ( SELECT * FROM vwAllMatrixWithLombardSimple WHERE source <> 'web') P INNER JOIN dbPubMatrix..tblNewMatrixFigures NMF ON P.VehicleRef = NMF. [VehicleRef] WHERE type='commercial' AND manufacturer='VAUXHALL') Q WHERE MinCH = 1

This query returns 5 records which is wrong. The query below gives the correct number of results - 7


SELECT *
FROM vwAllMatrixWithLombardSimple
WHERE (Type = 'commercial') AND (Manufacturer = 'vauxhall')

I have found, through trial and error, if I replace INNER JOIN with either LEFT JOIN or LEFT OUTER JOIN then the correct number of records are returned.

Apologies if this is a really simple question but what is the difference between these join types, and is LEFT the same as LEFT OUTER?

Thank you

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-21 : 08:23:11

LEFT is the same as LEFT OUTER.

an INNER join will only return records that match both tables on the join condition, a LEFT join will return all from the left side of the join and only those that match from tyhe right.

You might want to look at:

http://www.sqlteam.com/article/writing-outer-joins-in-t-sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources