I re-imagined SQL Joins into tarot cards! If you missed the original card deck reveal see [here].

This is part two of my little project and we dig a little more into the different joins.

DISCLAIMER 🔮

Not every database system supports all join types the same way. Think of them like tarot archetypes: some are the classic cards you’ll always pull, some are special but still standard, and a few are rare but powerful when you know them.

In this post, I’ll be discussing the nine different joins. Yes, nine.

---

THE CORE 4

Inner Join, Left Join, Right Join, and Outer Join

These are the backbone of SQL joins — supported across SQL Server, MySQL, PostgreSQL, Oracle, and almost every bootcamp, tutorial, or textbook.

Check out one of my very first blog posts on demystifying the core 4 [HERE].

---

THE STANDARD BUT LESS COMMONLY USED DUO

CROSS JOIN AND SELF JOIN

CROSS JOIN → Supported everywhere, but not used as often since it returns every possible pairing (easy to explode row counts).

If you ever find yourself in a situation where you weren’t expecting so many extra rows, make sure you’ve specified the table using ON.

If you neglect this, SQL won’t know what table you’re referring to and will default to a CROSS JOIN, hence the extra five million rows you didn’t ask for.

This is especially important in real business environments where there are A LOT of dimension tables within the database.

CROSS JOINS are a common pitfall. In some dialects (MySQL, SQLite), if you write FROM A, B without a join condition, it behaves like a CROSS JOIN.

In others (like PostgreSQL), you’re required to use CROSS JOIN explicitly, or else FROM A, B is just shorthand for the same thing.

Because it creates a Cartesian product, it’s supported everywhere but usually taught as a “special case” rather than part of the “core 4” joins.

---

SELF JOIN → Also standard, but not always taught early. It’s just a table joining to itself, like looking in a mirror. My first encounter in the real world with SELF JOIN was confusing because I thought “Why would you join a table to itself?” but it made so much sense after writing out the complex queries to pull the exact data I needed.

Eventually, you will come across a data pull or ad-hoc request where you’ll need to do a SELF JOIN to pull specific data. This is especially common in business entities where there’s a hierarchical structure or parent-child relationship stored in a single table.

For example, one of the most likely places you’ll find yourself using a SELF JOIN is when dealing with customer accounts in an ERP. This is where I encountered and discovered this join type.

In ERPs or CRMs, you often have one customer account with multiple relationships:

• Customer ID = the main account

• Bill-To ID = where invoices are sent

• Ship-To ID = where products are delivered

All three may live in the same Customers table.

A self join lets you pull the parent customer name alongside the child account details, so you can distinguish billing entities from shipping ones.

---

the red flag

natural join

NATURAL JOIN → Supported in Oracle, PostgreSQL, and MySQL, but not SQL Server (SSMS).

It auto-matches columns with the same name — which feels magical but can be dangerous in production since it might pull in new columns you didn’t expect. That’s why it’s rarely used or covered in tutorials.

Like, sorry Mr. Houdini but you’re kind of a red flag.

If someone later adds a new column with the same name, your join condition changes without you realizing it.

That’s why NATURAL JOIN is rarely taught or used day-to-day. Still, it’s a valuable teaching tool to understand SQL’s quirks — the “Magician’s trick.”

Side note: this is how I feel about natural joins…

---

THE POWERFUL JOINS YOU PROBABLY DIDN’T REALIZE WERE JOINS…

semi join and anti join

SEMI JOIN → Returns rows that exist in another table, without bringing back details.

ANTI JOIN → Returns rows that don’t exist in another table.

Not every SQL dialect has SEMI or ANTI as direct keywords.

Most SQL users learn EXISTS / NOT EXISTS and IN / NOT IN as subquery filters, not as “join types.” This is how I learned. But if you dig deeper, EXISTS, NOT EXISTS, IN, and NOT IN are ways to implement SEMI JOIN and ANTI JOIN logic.

In our tarot deck, I call them the “Rare Cards” because they’re not always explicit supported like they are in PostgreSQL or Oracle.

For most of us junior or mid-level SQL Server/MySQL folks, we see them as filters (because I still certainly do) but the more advanced SQL masters out there can probably agree that under the hood, EXISTS / NOT EXISTS and IN / NOT are ways to implement that semi and anti join logic.

---

why i included them all

Because why not?

Most people only ever see the “core 4” (and maybe cross join). But including Self, Natural, Semi, and Anti shows the full spread of how SQL handles matches and non-matches. That’s why my Tarot deck has 9 archetypes instead of 4 or 5.

And it makes this fresh new approach of learning joins WERK.

Now let’s start the tutorial!

---

tarot-themed sample tables

We’ll use a Tarot Shop as our example:

---

the tarot join spread

❤️ INNER JOIN – THE MATCHMAKER

USE CASE: Combine everything from both customers and readings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
INNER JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: Returns rows where both tables match (Luna and Sol with their readings).

---

🌱 LEFT JOIN – THE CARETAKER

USE CASE: List all customers, even if they never booked a reading.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
LEFT JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: All customers show up; Astra and Terra appear with NULLs.

---

🏛️ RIGHT JOIN – THE ALLY

USE CASE: List all readings, even if no matching customer exists.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
RIGHT JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: All readings appear; the Horoscope Reading has NULL for the customer.

---

🌍 FULL OUTER JOIN – THE WORLD

USE CASE: Combine everything from both customers and readings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
FULL OUTER JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: Shows Luna, Sol, Astra, Terra, and the null customer.

---

🎲 CROSS JOIN – THE TRICKSTER

USE CASE: Generate all possible customer–reading pairings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
CROSS JOIN Readings r;

RESULTS: 16 rows (every customer with every reading).

---

🔦 SELF JOIN – THE REFLECTION

USE CASE: Compare customers to each other (e.g., same zodiac sign).

SYNTAX

SELECT a.customer_name AS customer1,
b.customer_name AS customer2,
a.zodiac_sign
FROM Customers a
JOIN Customers b
ON a.zodiac_sign = b.zodiac_sign
AND a.customer_id < b.customer_id;

RESULTS: Pairs of customers with matching zodiac signs.

---

✨ NATURAL JOIN – THE MAGICIAN

USE CASE: Automatically match tables by columns with the same name.

SYNTAX

SELECT customer_name, spread_type, card_drawn
FROM Customers
NATURAL JOIN Readings;

RESULTS: Same as INNER JOIN, but SQL chooses join columns automatically.

---

Why Developers Avoid NATURAL JOIN

While NATURAL JOIN feels like magic, many SQL developers avoid it in real-world projects. Here’s why:

• Hidden behavior: It joins on all columns with the same name — sometimes more than you expect.
• Future risk: If a new column is added later with the same name in both tables, your query’s behavior can silently change.
• Portability: Not every SQL engine supports it (e.g., SQL Server SSMS does not).

NATURAL JOIN can feel magical, but in production it’s risky.

Production = real-world, live environments where:

• Your SQL isn’t just practice anymore — it powers dashboards, apps, or business reports.
• Mistakes can affect decision-making, break dashboards, or even crash systems.
• Small schema changes (like someone adding a new column with the same name in both tables) can suddenly change results without anyone realizing it.

This is why it is often discouraged or rarely mentioned by most SQL instructors.

---

📜 SEMI JOIN – THE SEER

USE CASE: Find customers who have at least one reading (but don’t list the reading details).

SYNTAX

SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Readings r
WHERE c.customer_id = r.customer_id
);

RESULTS: Luna and Sol only.

---

💀 ANTI JOIN – THE HERMIT

USE CASE: Find customers who never had a reading.

SYNTAX

SELECT c.customer_name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Readings r
WHERE c.customer_id = r.customer_id
);

RESULTS: Astra and Terra only.

---

CLOSING THE SPREAD

Together, these 9 joins form the Tarot SQL Join Spread:

• INNER JOIN → The Matchmaker (true matches)
• LEFT JOIN → The Caretaker (include everyone left)
• RIGHT JOIN → The Ally (include everyone right)
• FULL OUTER JOIN → The World (wholeness)
• CROSS JOIN → The Trickster (endless possibilities)
• SELF JOIN → The Reflection (introspection)
• NATURAL JOIN → The Magician (automatic shortcut)
• SEMI JOIN → The Seer (exists, but hidden)
• ANTI JOIN → The Hermit (exclude what doesn’t belong)

And there you have it — the complete Tarot SQL Join Spread.
From The Matchmaker (INNER JOIN) to The Hermit (ANTI JOIN), each card reveals a different way tables connect, overlap, or stand alone.

My hope is that next time you write a query, you’ll picture the card: Do I want true matches, inclusivity, solitude, or a little chaos? That little image might just help the SQL logic click faster.

I’d love to hear from you: Which join (or tarot card) do you use most in your daily work? Share in the comments, and let’s see which archetypes get drawn the most often.

---