Now, let’s talk about the two JOINs that everyone seems to find a little… odd. The RIGHT JOIN and FULL OUTER JOIN are often treated like the weird cousins at the family reunion—you know they’re important, but you’re not quite sure how to talk to them. Let’s fix that.

The truth is, a RIGHT JOIN is just a LEFT JOIN in a cheap mirror. It’s functionally identical. No, really. A RIGHT JOIN on tables A and B is literally just a LEFT JOIN on tables B and A. The only difference is the order in which you write the tables in your query. Because of this, you will almost never see a RIGHT JOIN in professional, production-grade code. It’s the syntactic equivalent of wearing your pants backwards. It works, but it confuses the hell out of everyone who sees it, including Future You at 2 AM trying to fix a bug.

Here’s the proof. These two queries are 100% identical in their result:

-- Using a LEFT JOIN (the standard way)
SELECT *
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Using a RIGHT JOIN (the confusing way)
SELECT *
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

Both return all customers, and any orders they might have. The RIGHT JOIN version just forces you to think backwards. You’re starting from the orders table and then saying, “oh, and also grab the customers that match, and by the way, also give me any customers that don’t have orders.” It’s clunky. The LEFT JOIN version is far more intuitive: “Give me all customers, and slap their order info next to them if it exists.” We think from the primary entity outward. So, my strong recommendation is to just never use RIGHT JOIN. Standardize on LEFT JOIN for clarity and consistency. Your teammates (and your sanity) will thank you.

The FULL OUTER JOIN: Actually Useful Weirdness

Now, the FULL OUTER JOIN is where things get genuinely interesting and useful. This is the JOIN you use when you want to see the entire universe of both tables, matched where possible, but preserving all rows from both sides that don’t have a match. It’s the Venn diagram where both circles are fully shaded.

Imagine you’re merging two customer lists from different departments, Sales and Support. Some customers are in both systems, some are only in Sales, and some are only in Support. A FULL OUTER JOIN is the perfect tool to see the complete picture.

SELECT
    COALESCE(sales.customer_id, support.customer_id) AS customer_id,
    sales.sales_contact,
    support.support_tier
FROM sales_customers sales
FULL OUTER JOIN support_customers support
    ON sales.customer_id = support.customer_id;

This query will return:

  1. Rows where customer_id matches, with data from both tables.
  2. Rows for customers only in the sales_customers table, with support_tier as NULL.
  3. Rows for customers only in the support_customers table, with sales_contact as NULL.

Notice the use of COALESCE here. This is a critical best practice. Since a customer missing from one table will have a NULL in its customer_id column for that side, COALESCE picks the first non-null ID from either table, ensuring you have a single, clean ID column in your result set. Forgetting to do this is a classic pitfall—you’ll end up with two nullable ID columns and a huge mess.

The NULL-Filled Wasteland and Its Pitfalls

The biggest “gotcha” with FULL OUTER JOIN is the sheer number of NULLs it can generate. Your result set is the union of a left join and a right join. This means you absolutely must write your WHERE clauses with extreme care.

A common mistake is to try and filter for a value from the left table, unintentionally converting your full outer join into a left join.

-- This is WRONG and kills the purpose of the FULL JOIN
SELECT *
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id
WHERE a.some_column = 'value'; -- This filters out all unmatched rows from table_b!

The rows from table_b that had no match in table_a will have NULL for all a.* columns. Your filter a.some_column = 'value' will evaluate to NULL = 'value', which is not TRUE, so those rows get unceremoniously dumped. Poof. There goes your complete picture.

The correct way to filter is to move the condition to the ON clause if it’s related to the join, or use an OR IS NULL construct if you’re truly desperate. But most of the time, you just need to be acutely aware that you’re dealing with two sets of NULLs and plan your analysis accordingly. It’s a powerful tool, but it demands respect and careful handling. Use it when you need a true reconciliation between two datasets, but don’t be surprised when it hands you a report filled with existential voids.