When using local database SQLite in Flutter, what is the difference between INNER JOIN vs LEFT JOIN?

Overview

When working with SQLite in Flutter (using the

sqflite

INNER JOIN

LEFT JOIN

INNER JOIN

Returns only rows that have matching records in both tables. If a row in the left table has no corresponding row in the right table, it is excluded from the results.

dartCopy
Future<List<Map<String, dynamic>>> getUsersWithOrders() async {
  final db = await database;
  return db.rawQuery('''
    SELECT users.name, orders.product, orders.amount
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
  ''');
}
// Only users who have placed orders appear in results

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, regardless of whether a match exists in the right table. Non-matching right-side columns are filled with

NULL

dartCopy
Future<List<Map<String, dynamic>>> getAllUsersWithOrders() async {
  final db = await database;
  return db.rawQuery('''
    SELECT users.name, orders.product, orders.amount
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
  ''');
}
// ALL users appear — those without orders have NULL for product/amount

Practical Example

Consider two tables —

users

orders

dartCopy
// Setup tables
await db.execute('''
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  )
''');

await db.execute('''
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
''');

// Data: Alice has 2 orders, Bob has 1 order, Charlie has 0 orders

INNER JOIN result:

Charlie is excluded — no matching orders.

LEFT JOIN result:

Charlie is included — with

textCopy

NULL

for order columns.

Finding Records with No Match

A powerful

LEFT JOIN

dartCopy
Future<List<Map<String, dynamic>>> getUsersWithNoOrders() async {
  final db = await database;
  return db.rawQuery('''
    SELECT users.name
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
    WHERE orders.id IS NULL
  ''');
}
// Result: [{ name: Charlie }]

This pattern is not possible with

textCopy

INNER JOIN

.

Key Differences

NULL

WHERE right.col IS NULL

LEFT OUTER JOIN

When to Use Each

Use

INNER JOIN

• You only want records with valid relationships in both tables
• Displaying orders with user details (skip invalid references)
• Building reports that exclude incomplete data

Use

LEFT JOIN

• You need all records from the primary table regardless of matches
• Finding records with no related data (orphan detection)
• Building dashboards where missing data should show as zero/N/A
• Optional enrichment — joining with supplementary data that may not exist

Note: SQLite does not support

textCopy

RIGHT JOIN

or

textCopy

FULL OUTER JOIN

. To achieve right-join behavior, swap the table order and use

textCopy

LEFT JOIN

. For full outer join, use a

textCopy

UNION

of two left joins.

Learn more at sqflite package documentation and SQLite JOIN syntax.