LEFT JOIN in SQL – SQL Tutorial for Beginners

The LEFT JOIN is one of the most commonly used SQL joins. It returns all records from the left table, and the matching records from the right table. If no match exists, SQL returns NULL for the right table.

In this beginner-friendly SQL tutorial, you will learn:

What LEFT JOIN is
How LEFT JOIN works
Syntax and examples
LEFT JOIN vs INNER JOIN
Real-world use cases
Best practices

🔹 What Is LEFT JOIN?

A LEFT JOIN returns:

All rows from the left table
Matching rows from the right table
NULL values when no match exists

✔ Important:

LEFT JOIN never removes rows from the left table.

🔸 LEFT JOIN Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Here, table1 is the left table, table2 is the right table.

🔸 Example Tables

🧑 users table

id	name	city
1	Amit	Delhi
2	Neha	Mumbai
3	Rahul	Bengaluru
4	Sara	Jaipur

📦 orders table

id	user_id	product
1	1	Mobile
2	3	Laptop

🔸 Basic LEFT JOIN Example

Fetch all users, including those without orders:

SELECT users.name, orders.product
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

✔ Output:

name	product
Amit	Mobile
Neha	NULL
Rahul	Laptop
Sara	NULL

The NULL values indicate users who have not placed any orders.

🔸 LEFT JOIN with Additional Conditions

Get all users and their orders from Delhi only:

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE u.city = 'Delhi';

🔸 LEFT JOIN with WHERE vs ON

Condition in ON clause (recommended):

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id AND o.product = 'Laptop';

This keeps unmatched rows.

Condition in WHERE clause:

WHERE o.product = 'Laptop'

This can convert LEFT JOIN into INNER JOIN.

🔸 LEFT JOIN Multiple Tables

SELECT u.name, o.product, p.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN payments p ON o.id = p.order_id;

🔹 LEFT JOIN vs INNER JOIN

Feature	INNER JOIN	LEFT JOIN
Returns matching rows only	✔	❌
Keeps unmatched left table rows	❌	✔
Shows NULLs for missing values	❌	✔

🔸 Real-World Examples

✔ Users with or without orders

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;

✔ Employees and their departments (including those not assigned)

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.id;

✔ Students and assigned courses

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

🔹 Best Practices

✔ Use table aliases to simplify long queries
✔ Avoid putting right-table conditions in the WHERE clause
✔ Index joined columns for faster performance
✔ Use LEFT JOIN when you want all records from the left table

Summary

In this tutorial, you learned:

What LEFT JOIN does
How it returns all rows from the left table
How NULL values indicate missing matches
LEFT JOIN vs INNER JOIN differences
Real-world examples and best practices

LEFT JOIN is essential in relational databases where you want complete information, including unmatched records.