Thoughts on Software

How and why to use LEFT JOINs in SQL statements

with one comment

LEF T JOINs are something I’ve been using in SQL statements for literally years without thinking much about it but a few conversations recently have made me realize that with the rise of ORMs, a lot of people are a lot less SQL-savvy than they were even a few years ago to the point that JOINs are a bit of a mystery. Most people seem to be able to use INNER JOINs correctly but LEFT JOINs cause a lot of confusion and hence this post …

A LEFT JOIN is used to return data from two tables where there are definitely rows in one table and there may be corresponding rows in the other. If there are, we want to see them and if there aren’t we don’t care: that is, we don’t want to see data only where there is data present in both tables. An example of this could be a report showing all students enrolled in college course and their grades, where some students may not yet have taken any exams but we still want to see all students and then any results for exams they have taken.

So, how do we do this?

Continuing with our example, we’ll need three tables, Students, Courses and Grades with the following schema:

Students: Id, FirstName, SecondName
Courses: Id, Description
Grades: StudentId, CourseId, Grade

What we want to see is all students and every grade they have received over the year. We’ll also want to see the course description so we’ll need to JOIN all three tables. Here’s how we do it:

      s.Id, s.FirstName, s.SecondName, g.Grade, c.Description 
      Students s
LEFT JOIN Grades g ON s.Id = g.StudentId
INNER JOIN Courses c ON c.Id = g.CourseId

(And just to explain s is declared as an alias of Students so s.Id is the same as writing Students.Id, etc. …)

The LEFT JOIN means: “give me everything on the left of the equals sign and any matching rows on the right of the equals sign”. So

      Students s
LEFT JOIN Grades g ON s.Id = g.StudentId

means: “give me everything from Students and any matching rows from the Grades table”

So, why the INNER JOIN on Courses? This is what allows us to get the course description from the Courses table. We are assuming here that there should never be a grade for a course that doesn’t exist (pretty reasonable assumption!), so we are restricting the grades we are returning to only those with a matching course.

So there you have it: how to return all students and any matching grades complete with course description in one simple SQL statement.

Later in the week, I’ll be publishing a post on how to use LEFT JOINs to find missing data …

Written by andrewlocatelliwoodcock

May 30, 2012 at 22:11

Posted in Databases, mySQL, SQL, SQL Server

Tagged with , ,

One Response

Subscribe to comments with RSS.

  1. […] old friend the LEFT JOIN to the rescue. We already know from this post, that we can use LEF T JOINs to return every row from one table and any matching row from another […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: