- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL LEFT JOIN: Syntax, Usage, and Examples
The SQL LEFT JOIN
keyword allows you to combine rows from two tables while preserving all records from the left (or first) table, even if there’s no matching record in the right (or second) table. LEFT JOIN SQL
is commonly used to fetch related data while maintaining unmatched entries from the primary dataset.
How to Use SQL LEFT JOIN
The general syntax for a SQL LEFT JOIN
is:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
This syntax returns all rows from table1
and the matched rows from table2
. If there is no match, the result is NULL
for columns from table2
.
Example: Basic LEFT JOIN
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
This query shows all customers, even those who haven’t placed any orders. The order date will be NULL
for such customers.
When to Use LEFT JOIN SQL
The left join in sql
is ideal when:
- You want to preserve all records from one table regardless of matching values in the second
- You need to identify missing relationships (e.g., customers without orders)
- You want to include optional or non-mandatory associations
- You're auditing, debugging, or analyzing relational integrity
It’s commonly used in reporting, data warehousing, and troubleshooting.
Scenario: Find Employees Without Assigned Projects
SELECT employees.name, projects.project_name
FROM employees
LEFT JOIN projects
ON employees.id = projects.employee_id;
Unassigned employees will still appear with NULL
for project_name
.
LEFT JOIN SQL Examples
Example 1: LEFT JOIN with WHERE Clause
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_id IS NULL;
This shows customers who have never placed an order. The sql where left join
pattern is powerful for detecting gaps in data.
Example 2: LEFT OUTER JOIN SQL
SELECT p.name, c.category_name
FROM products p
LEFT OUTER JOIN categories c
ON p.category_id = c.id;
The LEFT OUTER JOIN
is functionally the same as LEFT JOIN
—the keyword “OUTER” is optional.
Example 3: LEFT JOIN vs RIGHT JOIN SQL
Understanding the difference between left and right joins is key:
LEFT JOIN SQL
keeps all records from the left table.RIGHT JOIN SQL
keeps all records from the right table.
-- LEFT JOIN
SELECT a.name, b.info
FROM A a
LEFT JOIN B b ON a.id = b.a_id;
-- RIGHT JOIN (same result, flipped)
SELECT b.info, a.name
FROM B b
RIGHT JOIN A a ON a.id = b.a_id;
Use the one that aligns better with how you structure your query.
Example 4: LEFT JOIN with Multiple Conditions
SELECT s.student_name, e.exam_score
FROM students s
LEFT JOIN exams e
ON s.id = e.student_id AND e.subject = 'Math';
This returns all students and their Math exam scores—NULL
if they didn’t take it.
Learn More About LEFT JOIN in SQL
LEFT JOIN vs INNER JOIN
An INNER JOIN
returns only matching rows from both tables. A SQL LEFT JOIN
includes all rows from the left table, even without a match.
-- INNER JOIN
SELECT a.name, b.detail
FROM A a
INNER JOIN B b ON a.id = b.a_id;
-- LEFT JOIN
SELECT a.name, b.detail
FROM A a
LEFT JOIN B b ON a.id = b.a_id;
The left join retains all A
entries—even if B
has no match.
LEFT JOIN vs RIGHT JOIN SQL
These are mirror opposites. If you switch the order of tables, a left join becomes a right join:
-- LEFT JOIN A to B
SELECT A.id, B.info
FROM A
LEFT JOIN B ON A.id = B.a_id;
-- RIGHT JOIN B to A (same outcome)
SELECT A.id, B.info
FROM B
RIGHT JOIN A ON A.id = B.a_id;
Both are useful; choose based on query readability.
SQL LEFT JOIN on Multiple Tables
You can chain left joins to bring data from several tables:
SELECT e.name, d.department_name, l.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN locations l ON d.location_id = l.id;
This shows employees with their department and location, even if some data is missing.
Filtering After a LEFT JOIN
Be cautious with WHERE
clauses. Applying conditions directly after a LEFT JOIN
may filter out null-matching records:
-- Might exclude left-only rows
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.type = 'premium';
-- Safer version
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id AND B.type = 'premium';
Move filters into the ON
clause to preserve left-side rows.
Performance and Indexing Tips
- Use indexes on joined columns to speed up
LEFT JOIN SQL
queries. - Avoid unnecessary joins when only looking for unmatched records—consider
NOT EXISTS
orNOT IN
for better performance on large datasets. - Review execution plans in tools like SSMS or EXPLAIN to identify slow joins.
Use LEFT JOIN in Data Cleanup
This query finds orphaned records—entries in one table with no parent:
SELECT o.id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
Perfect for identifying and deleting invalid or outdated data.
LEFT JOIN with Aggregates
SELECT c.name, COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
Even customers with zero orders are included.
LEFT JOIN on Views
You can use LEFT JOIN SQL
with views just like with tables:
SELECT e.name, v.salary
FROM employees e
LEFT JOIN employee_summary_view v ON e.id = v.employee_id;
This is useful for abstracting complexity and keeping queries clean.
The SQL LEFT JOIN
keyword is a flexible, essential part of querying relational databases. It allows you to maintain visibility into unmatched records, analyze relationships, and build comprehensive reports that reflect all entries from your main dataset. From identifying missing data to combining multiple tables and even comparing left join vs right join sql
, mastering this technique makes your queries more robust and insightful. Use LEFT JOIN SQL
to explore associations fully while keeping your data integrity intact.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.