Solved

# sql left join

Posted on 2009-12-29
193 Views
In SQL when i do I a left join is this refering to the table on the left of the equals sign. For example,

Left table is 'EntID'

0
Question by:Mr_Shaw

Assisted Solution

It's not taken from join conditions. It's decided from the first portion of the query
(Exp:) Table1 On Table 2 -- Here table2 is referencing table
0

LVL 81

Assisted Solution

Hello Mr_Shaw,

Not here but

SELECT * FROM leftTable LEFT JOIN rightTable ON rightTable.ID = leftTable.ID

Regards.
0

LVL 15

Assisted Solution

It is on the left of the table joining. Consider this:

1) Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID

2) Table1 LEFT JOIN Table2 ON Table2.ID = Table1.ID

The left join is referring to Table1, NOT in the equal sign.
0

LVL 2

Assisted Solution

It refers to the table which appears first in the query.
For example.

SELECT
*
FROM
table1
LEFT OUTER JOIN
table2
ON
table1.col1 = table2.col1

In this we will get all the values from table1 even there are no mapping entry in table2. But we will not get those entries from table2 which does not have a mapping entry in table1.
0

Author Comment

Thanks guys. what happens if I have multiple tables.

For example in the second left join in the code bellow is entadd. Am I correct?

0

LVL 3

Accepted Solution

A Left join simply refers to the fact that the comparison is being made only one time the left value is = to the right, not vice versa.

Left joins are very high powered and can run much faster than your normal queries, so if you were to use the syntax

SELECT a.Name FROM People a
INNER JOIN CoolPeople b ON a.peopleID = b.peopleID

This same query can be completed using a left join but will run much faster on large data sets using

SELECT a.Name FROM People a
LEFT JOIN CoolPeople b ON a.peopleID = b.PeopleID
WHERE b.peopleID IS NOT NULL

This just grabs all of the rows where the comparison is not null thus showing you how the left join directly effects the query, and how it's comparing everything from the left table of the two.

0

LVL 3

Assisted Solution

if you have multiple left joins make sure you are specifying Not nulls and Nulls in your where statement so

SELECT a.Name FROM People a
LEFT JOIN CoolPeople b ON a.peopleID = b.PeopleID
LEFT JOIN TexasPeople c ON a.PeopleID = c.PeopleID
WHERE b.peopleID IS NOT NULL AND c.peopleID IS NOT NULL

You will find yourself with much lower processing times, and yes you are correct, but just continue to use left joins instead of the left outer would be my suggestion.
0

Author Closing Comment

thanks
0

LVL 81

Expert Comment

You're welcome!
0

## Featured Post

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
how to add IIS SMTP to handle application/Scanner relays into office 365.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦