Finding Rows Without Matches: Alternatives to INNER JOIN in MariaDB

Here's how it works:

Here's an example:

SELECT *
FROM customers c  -- Select all from customers table (left table)
LEFT JOIN orders o ON c.id = o.customer_id  -- Join with orders table based on customer ID
WHERE o.customer_id IS NULL;  -- Show customers with NO orders (o.customer_id is NULL)

This query would return all customers from the "customers" table, even those who haven't placed any orders (because their "customer_id" wouldn't be found in the "orders" table, making the "o.customer_id" column NULL).

This is the most common approach:

SELECT *
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

This query retrieves all customers (* = all columns) from the "customers" table (left table) even if they have no orders. The LEFT JOIN keeps all rows from "customers". The WHERE clause then filters to only show rows where the "customer_id" from the "orders" table (right table) is NULL, indicating no matching order.

NOT EXISTS clause:

This method checks for the absence of rows in a subquery:

SELECT *
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Here, we select all customers (*) and use the WHERE clause with NOT EXISTS. The subquery inside the parentheses tries to find any record in the "orders" table where "customer_id" matches the current customer's "id" (c.id). If such a record doesn't exist (NOT EXISTS), the customer with no order gets included in the results.

This approach excludes rows based on a list of values:

SELECT *
FROM customers c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders
);

This query selects all customers (*). The WHERE clause uses NOT IN to check if the customer's "id" (c.id) is not present in the list of "customer_id" values retrieved from the "orders" table. Customers without a matching order ID in "orders" will be included in the results.

• A RIGHT JOIN keeps all rows from the right table, similar to how LEFT JOIN keeps all from the left table. However, it's less common for the "opposite" result since you typically want unmatched rows from the left table.

Here's an example (assuming you want unmatched rows from the "orders" table):

SELECT *
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

This query keeps all rows from "orders" and joins with "customers". It then uses WHERE c.id IS NULL to find orders with no matching customer (i.e., unmatched rows in "orders").

UNION ALL:

• This approach combines results from two separate queries. It's useful when you need the unmatched rows from both tables.

(SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders))
UNION ALL
(SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers));

This query uses two subqueries. The first finds unmatched customers (no orders), and the second finds unmatched orders (no customer). UNION ALL combines both sets of unmatched rows.

EXISTS with subquery variation:

• This variation uses EXISTS with a subquery that checks for at least one matching row.

SELECT *
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
  LIMIT 1
);

This is similar to the previous NOT EXISTS example, but the subquery uses LIMIT 1. As long as there's at least one matching order (even if there are more), the subquery will return true (EXISTS), excluding that customer from the results. This approach aims to improve performance by stopping the subquery after finding a single match.