Left Joins and column mapping - 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

Left Joins and column mapping

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-01-30 : 14:18:25

Hello,

I am trying to understand the concept of left joins. I have the following query and am not sure about the left joins.

I am familiar with joins but the left join below is a little confusing.Below it seems like a third table is involved. Is this because there is no column to map to in the from table? Also, since tables sl and sc are mapped based on the SecurityID column and sl and ex do not have any common columns, table sc is mapped to ex using the left join? Which table's data will be returned based on the left join?

I checked the column type for the Exchange column(ex.LSECode) and it appears varchar(3).

SELECT SecurityID = sl.SecurityID
, Security = RTRIM(sec.Name) + ' - ' + RTRIM(sec.Description)
, Ticker = ISNULL(ids.RIC, ids.Ticker)
, Sedol = ids.Sedol
, ISIN = ids.ISIN
, Exchange = ex.LSECode
, Country = cty.iso_code

FROM #SecList sl

JOIN SecurityClassification sc
ON sl.SecurityID = sc.securityId
AND sc.source = 99

LEFT JOIN exchange_table ex
ON sc.ExchangeID = ex.exchange

Thanks in advance!!!
novicesql123

Kristen
Test

22859 Posts

Posted - 2006-01-30 : 14:57:22

Hi sqlnovice123, Welcome to SQL Team!

A LEFT OUTER JOIN means that the row(s) in the table on the "left" must exist, but the one(s) in the table on the right are optional. Left and Right in this context are a bit iffy. LEFT means towards the WHERE statement, and has nothing to do with the order in which the ON statement's columns are arranged! (See *** below). If the "OUTER JOIN table" does not have any data matching the ON clause then its columns will appear to be NULL.

So in your example, for a given row in "SecurityClassification" SQL will try to find any matching row(s) in "exchange_table". If there aren't any then the columns in "exchange_table" will appear to be NULL, and if there are then the JOIN will be no different to a normal / INNER join.

*** Note that both sets of code below are identical:

LEFT JOIN exchange_table ex
ON sc.ExchangeID = ex.exchange

LEFT JOIN exchange_table ex
ON ex.exchange = sc.ExchangeID

a LEFT OUTER JOIN allows rows in the "ex" table to be optional, and rows in "sc" to be compulsory.

By comparison a RIGHT OUTER JOIN is the other way round - but it is very rare that people write code with a RIGHT OUTER JOIN 'coz its just plain "weird" as far as Humans are concerned!

Kristen

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-01-30 : 16:16:30

Kristen,

It appears crystal clear now.

Thanks!
sqlnovice123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources