• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

sql left join

In SQL when i do I a left join is this refering to the table on the left of the equals sign. For example,

ON EntID.ID = entadd.ID

Left table is 'EntID'

0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
  • 2
  • 2
  • +3
6 Solutions
 
nagaraj_mCommented:
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
 
leakim971PluritechnicianCommented:
Hello Mr_Shaw,

Not here but

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

Regards.
0
 
Faiga DiegelCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mabbj747Commented:
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
 
Mr_ShawAuthor Commented:
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?

left outer JOIN entadd ON EntID.EntSys = entadd.EntSys left outer join dbo.SOA_LSOA on SOA_LSOA.POSTCODE = entadd.ZIP
0
 
tsqlguyCommented:
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
 
tsqlguyCommented:
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
 
Mr_ShawAuthor Commented:
thanks
0
 
leakim971PluritechnicianCommented:
You're welcome!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now