#leftjoin

Mastering SQL LEFT JOIN – Complete Beginner to Advanced Guide

Understanding how to combine data from multiple tables is a fundamental SQL skill for anyone pursuing a career in data analytics, data science, or database management. In this video, we explore LEFT JOIN in SQL, one of the most important join operations used to retrieve related data while preserving all records from the primary table.

This tutorial walks you through the concepts, syntax, and practical applications of LEFT JOIN, making it easier to understand how relational databases work in real-world scenarios.

⏱️ Video Timestamps

00:00 – Introduction to SQL Joins and their importance 02:22 – Customer orders across multiple tables 07:06 – Joins based on common fields (IDs) 09:53 – Querying multiple tables with joins 14:46 – Primary and foreign keys in SQL joins 17:10 – Joins vs. referential integrity 21:46 – Practical query examples using joins 23:57 – Inner join explained with customers and orders 28:47 – Overview of SQL join types 30:46 – Combining customer and order data 35:05 – Resolving column name ambiguity in joins

📌 What You’ll Learn

1️⃣ Basics of LEFT JOIN • How LEFT JOIN works in SQL • Differences between LEFT JOIN and other join types

2️⃣ Writing LEFT JOIN Queries • Syntax and structure of LEFT JOIN statements • Using table aliases to write cleaner queries

3️⃣ Advanced LEFT JOIN Techniques • Joining more than two tables • Using LEFT JOIN with filtering conditions

4️⃣ Best Practices • Optimizing SQL query performance • Maintaining data integrity with joins

🚀 Learn Data Science with Imarticus Learning

The Postgraduate Program in Data Science and Analytics (PGA) is designed for graduates and professionals who want to build strong careers in analytics and data science.

Program Highlights: • 6-month industry-focused program • 100% job assurance with 2000+ hiring partners • 300+ learning hours and 25+ hands-on projects • Training in 10+ tools including Python, Power BI, and Tableau • 52% average salary hike with top packages reaching 22.5 LPA

📚 Recommended Books for SQL Learners

• Learning SQL – Alan Beaulieu • Database Design for Mere Mortals – Michael J. Hernandez • SQL Cookbook – Anthony Molinaro

About Imarticus Learning

Imarticus Learning is a leading education company offering industry-relevant programs in finance, analytics, technology, and management. With over 1,000,000 learners impacted, expert faculty, and 3500+ global partnerships, the organization helps professionals build successful careers through practical learning and mentorship.

Key Highlights

12+ Years Legacy | 1M+ Learners Impacted | 85% Placement Record | 54% Average Salary Hike | 24 LPA Highest Salary

Awards & Recognition

La explosión de SelfService Bi ha hecho que el día día de queries y cruces de datos sea cada vez menos convencional de hacer codeando. Las nuevas tecnologías que relacionan tablas y modelos tabulares puede que nos quiten ese encaje rústico de tablas. Para ello y quienes está comprendiendo como mezclar datos entre tablas es que escribo este artículo de Joins. 

I'm going to switch to English for the sake of this post.

While playing with Play Framework, Slick and Scala recently, I came across this problem when doing a left join: 

[error] SlickException: Read NULL value for column ORDERS.GUEST_ID (TypeMapper.scala:158) [error] models.BarTables$$anonfun$currentTableStateFull$1.apply(BarTable.scala:108)

Let's see what happens here:

   for{

        (guests,orders) <- Guests leftJoin Orders on (_.id === _.guestId)

    } yield (guests,orders).list

So this is how you do a left join with Slick. Here's the two tables used in the query:

object Orders extends Table[Order]("ORDERS") with CRUDModel[Order] with Logger{

  def id = column[Long]("ID", O.PrimaryKey, O AutoInc) // This is the primary key column

  def guestId = column[Long]("GUEST_ID")

  def created = column[DateTime]("CREATED")

  def delivered = column[Option[DateTime]]("DELIVERED")

  def paid = column[Option[DateTime]]("PAID")

  def * = id.? ~ guestId ~ created ~ delivered ~ paid <> (Order.apply _, Order.unapply _)

...

object Guests extends Table[User]("GUESTS") with CRUDModel[User]{

  def id = column[Long]("ID", O.PrimaryKey, O AutoInc) // This is the primary key column

  def name = column[String]("NAME")

  def email = column[String]("EMAIL")

  def * = id.? ~ name ~ email <> (User.apply _, User.unapply _)

...

That looks quite normal I guess.

But then the problem arises when there's no orders belonging to the guests, and so all the columns selected from the Orders table are null. And guestId cannot be null, it's not defined as an Option (obviously).

What am I doing wrong?

The problem is that the default projection * is used when yielding the result:

    yield (guests,orders).list

We have to provide a projection that doesn't use the default one. So after some googling and stackoverflowing, I came across this post, and then this one and the result looks like this:

  def maybe = id.? ~ guestId.?  ~ created.?  ~ delivered  ~ paid  <> (

      tupleToOrder _,

      (order: Option[Order]) => None

  )

  def tupleToOrder(orderTuple: (Option[Long],Option[Long],Option[DateTime],Option[DateTime],Option[DateTime])) : Option[Order]= orderTuple match {

   case (Some(id),Some(guestId),Some(created),delivered,paid) => Some(Order(Some(id),guestId,created,delivered,paid))

   case (None,_,_,_,_) => None

  }

So instead of using the values as they would be not-nulls, we provide a way to pass nulls, and then get an Option[Order] instead of an Order. We only have to match for Some(id) really, since if that's a null, all the other columns are null as well.

And so the original query looks like this:

    for{

        (guests,orders) <- guests(tableId) leftJoin Orders on (_.id === _.guestId)

    } yield (guests,orders.maybe)