Every SQL course teaches joins as a way to combine tables for analysis. Customers in one table, orders in another; join them to see who bought what. That's a real use case and it matters.
But in practice, I use joins just as often to explore my data and find problems.
Mismatched rows.
Missing records.
Inconsistent keys.
When something looks off in your results, switching join types is one of the fastest ways to figure out why.
This post walks through both uses. Using data from this Pixar films dataset, we'll combine two tables, hit an unexpected mismatch, diagnose it with left and right joins, then use a union to see the full picture.
Inner Joins: The Starting Point
An inner join combines two tables and returns only the rows where there's a match in both. With a films table and a reviews table that both have a film column, the join looks like this:
SELECT *
FROM films f
INNER JOIN reviews r ON f.film = r.film
Giving tables short aliases (f and r) keeps the code cleaner as queries get more complex. Run this and you might expect to see all 28 Pixar films. The result shows only 25.
Something is wrong. Three films aren't matching. Before assuming anything, check the row counts: both tables have 28 rows. So the issue isn't missing data. It's mismatched keys.
Left and Right Joins: Tools for Diagnosing Mismatches
A left join returns all rows from the left table and fills in NULLs where there's no match in the right table. Swapping to a left join and filtering for NULLs in the right table column reveals which films exist in the films table but not in reviews:
SELECT *
FROM films f
LEFT JOIN reviews r ON f.film = r.film
WHERE r.film IS NULL
A right join does the reverse: all rows from the right table, NULLs where the left has no match. Filter for NULLs in the left table to see which films are in reviews but not in films.
Both queries return three films each. Looking at the values side by side, the films are the same titles. They just have different punctuation in each table. One version has it, the other doesn't. That's the mismatch.
Unions: Stacking Results for a Complete View
A union stacks two result sets on top of each other vertically, rather than combining them side by side. To see all mismatched rows from both join directions at once:
SELECT f.film AS films_table_film, r.film AS reviews_table_film
FROM films f
LEFT JOIN reviews r ON f.film = r.film
WHERE r.film IS NULL
UNION
SELECT f.film, r.film
FROM films f
RIGHT JOIN reviews r ON f.film = r.film
WHERE f.film IS NULL
The union output shows all six problem rows together. Same titles, different punctuation formatting. Now you know exactly what to fix.
Fixing the Mismatch with a Smarter Join Condition
Once you've identified the issue, you can use REGEXP_REPLACE() in the join condition itself to strip punctuation temporarily and force a match:
INNER JOIN reviews r
ON REGEXP_REPLACE(f.film, '[[:punct:]]', '') = r.film
This works. All 28 films now match. But this is where it's worth pausing, because that solution comes with a tradeoff.
Query Execution Plans: Measuring What You Can't See
"First write working code, then make it optimal."
Every time SQL runs a query, it executes a series of internal steps to produce your output. You can inspect those steps using EXPLAIN ANALYZE before your query. The output is dense and honestly not very readable as a human. I paste it straight into ChatGPT and ask for a plain-language breakdown. That makes it useful.
Comparing the standard film = film join against the REGEXP_REPLACE version tells the story clearly. The standard join completes in roughly 0.1ms. The regex join takes around 0.57ms, about five times longer. For 28 rows that's imperceptible. On a million-row table, it adds up fast.
The right fix for production code is to normalize the data at the source: make sure both tables use the same punctuation convention before you ever run a join. Use the regex join when you're exploring or prototyping. When in doubt, test it out — the execution plan will tell you what's actually happening.
Key Takeaways
Joins combine tables side by side. Use them for analysis, but also use them to find data quality issues.
Unions stack result sets vertically. Use them when you need to see output from multiple queries together.
Left and right joins reveal mismatches. Filter for NULLs after a left or right join to find rows that don't have a match in the other table.
Join conditions can be complex. You're not limited to column = column. You can use functions, inequalities, and expressions; just know that complexity has a performance cost.
EXPLAIN ANALYZE shows what's happening under the hood. Pair it with ChatGPT to make the output readable. Use it to compare approaches before committing to one.
Ready to go deeper? My Advanced SQL Querying course covers joins, subqueries, CTEs, and window functions with hands-on projects.
Alice Zhao
Lead Data Science Instructor
Alice Zhao is a seasoned data scientist and author of the book, SQL Pocket Guide, 4th Edition (O'Reilly). She's an adjunct lecturer for Northwestern University's Machine Learning and Data Science program, where she teaches Python, SQL, R, data warehousing and data visualization.
Frequently Asked Questions
What is the difference between a JOIN and a UNION in SQL?
A JOIN combines tables horizontally — it adds columns from one table alongside columns from another, matching rows based on a shared key. A UNION combines tables vertically — it stacks rows from one result set on top of rows from another. JOINs require a matching condition; UNIONs require both queries to have the same number and type of columns.
When should I use a LEFT JOIN instead of an INNER JOIN?
Use a LEFT JOIN when you want to keep all rows from the left table, even if there's no matching row in the right table. Unmatched rows will show NULL for the right table's columns. This is especially useful for finding data quality issues: filter WHERE right_table.column IS NULL to see which rows have no match.
What does EXPLAIN ANALYZE do in MySQL?
EXPLAIN ANALYZE shows how MySQL executes a query step by step, including estimated and actual row counts, execution time for each step, and which indexes were used. It's the primary tool for diagnosing slow queries and comparing the performance of different approaches. The output can be verbose — pasting it into ChatGPT with a request to summarize it is a practical shortcut.
Can you join on something other than equal values in SQL?
Yes. Join conditions can use functions, inequalities, or any valid expression, not just column = column. For example, you can join on REGEXP_REPLACE(a.film, '[[:punct:]]', '') = b.film to strip punctuation before matching. The tradeoff is that complex join conditions are more computationally expensive than simple equality joins, so use them for exploration rather than production queries on large tables.
What causes mismatched rows in a SQL join?
Common causes include inconsistent formatting (punctuation differences, capitalization, extra spaces), different data types (joining an integer to a string representation), NULL values in key columns, and duplicate values that create unintended many-to-many matches. Left and right joins are the fastest way to surface these problems: switch join types and filter for NULLs to see exactly which rows aren't matching.
