How can I show records that have no related row in another table?

That’s an anti-join: you’re asking for rows in one table for which there is no related row in another. Suppose, for illustration, the tables are members(member_id, name) and payments(payment_id, member_id), and payments.member_id refers to members.member_id.

A LEFT JOIN keeps every member. When there is no matching payment, the payment-side columns in that result are NULL. Filtering for NULL on the payment side identifies those unmatched members. [S1]

SELECT m.member_id, m.name
FROM members AS m
LEFT JOIN payments AS p
  ON p.member_id = m.member_id
WHERE p.payment_id IS NULL;

This version assumes payment_id cannot be NULL in a real payment row. Choose a right-table column that is guaranteed non-NULL for genuine matches; otherwise a matched row with a stored NULL in that column could look unmatched. [S1]

Assume these illustrative rows:

• Members: (1, 'Ari'), (2, 'Bea'), (3, 'Cal')
• Payments: (501, 1), (502, 1), (503, 3)

The query returns (2, 'Bea'): Ari has two matching payments, Cal has one, and Bea has none. The LEFT JOIN can produce multiple joined rows for Ari, but those rows fail the NULL filter because their payment IDs are present.

p.payment_id is a clear indicator here because the example assumes every real payment has a non-NULL payment ID. You could instead check p.member_id only if that field is also guaranteed non-NULL on every matching payment. If your schema allows NULL there, the test could misclassify a match. The sample and schema are assumptions; adjust them to your actual data.

Yes. A correlated subquery checks for a payment whose member key matches the current member. NOT EXISTS keeps the member when that subquery finds no row. In SQLite, EXISTS is true if the subquery returns one or more rows, and its selected values do not affect the result. [S2]

SELECT m.member_id, m.name
FROM members AS m
WHERE NOT EXISTS (
  SELECT 1
  FROM payments AS p
  WHERE p.member_id = m.member_id
);

With the illustrative rows above, this also returns (2, 'Bea'). The inner query refers to m.member_id from the outer query, so it checks payments for each member. Many readers find this wording direct: keep a member if no matching payment exists. Use the actual relationship columns in both versions; the example does not establish your schema or the contents of your database.

First identify the precise relationship, such as orders.customer_id = customers.customer_id; don’t join on names just because they look similar. Then use either pattern, consistently substituting your table and key names. For the LEFT JOIN version, select a right-side column that cannot be NULL in a real matching row and test that column. For NOT EXISTS, put the matching-key condition inside the subquery.

A compact adaptation template is:

-- LEFT JOIN pattern
SELECT l.*
FROM left_table AS l
LEFT JOIN right_table AS r
  ON r.related_key = l.key
WHERE r.non_null_on_match IS NULL;

-- NOT EXISTS pattern
SELECT l.*
FROM left_table AS l
WHERE NOT EXISTS (
  SELECT 1
  FROM right_table AS r
  WHERE r.related_key = l.key
);

Replace every placeholder with your real identifiers; they are not literal table or column names. Try both on a small, known example and compare the returned rows. These examples explain the query logic, not performance, schema design, or whether a particular relationship is correct.