While working through queries in
CS50’s Introduction to Databases with SQL, an interesting question arises:

If a query joins two tables, can we swap the table in the FROM clause with the one in the JOIN clause and still get the same result?

The answer is sometimes yes — but not always. Understanding when this works reveals an important concept about how SQL joins operate.

---

The Example Query

In the DESE database exercise, we wrote a query to find the district with the least number of pupils.

Original Query

SELECT districts.name
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
WHERE expenditures.pupils = (
    SELECT MIN(pupils)
    FROM expenditures
);

This query:

1. Joins the districts table with the expenditures table
2. Matches districts using the foreign key district_id
3. Filters districts whose pupil count equals the minimum value

---

Swapping the Tables

We can rewrite the same query like this:

SELECT districts.name
FROM expenditures
JOIN districts
ON districts.id = expenditures.district_id
WHERE expenditures.pupils = (
    SELECT MIN(pupils)
    FROM expenditures
);

Notice what changed:

FROM districts
JOIN expenditures

became

FROM expenditures
JOIN districts

Yet the result remains exactly the same.

---

Why This Works

This works because the query uses an INNER JOIN.

An INNER JOIN simply returns rows where the join condition matches in both tables.

The join condition is:

districts.id = expenditures.district_id

SQL therefore finds all pairs of rows that satisfy this relationship.

Whether the database starts from:

districts → expenditures

or

expenditures → districts

the matched pairs are identical.

In other words, INNER JOIN is symmetric.

---

A Simple Mental Model

Think of two sets:

Districts
Expenditures

An INNER JOIN returns only the intersection where the relationship matches.

So reversing the order does not change the intersection.

---

When Swapping Tables Changes the Result

This symmetry does not apply to all joins.

Consider a LEFT JOIN.

Example

SELECT districts.name
FROM districts
LEFT JOIN expenditures
ON districts.id = expenditures.district_id;

Meaning:

Return all districts
even if they have no expenditure record.

If we swap the tables:

SELECT districts.name
FROM expenditures
LEFT JOIN districts
ON districts.id = expenditures.district_id;

Now the meaning becomes:

Return all expenditure rows
even if they have no matching district.

The result is completely different.

---

Join Types and Table Order

Join Type	Does Table Order Matter?
INNER JOIN	No
LEFT JOIN	Yes
RIGHT JOIN	Yes
FULL JOIN	Yes

Only INNER JOIN behaves symmetrically.

---

Best Practice in Real SQL

Even though INNER JOIN allows swapping tables, developers usually write queries with a logical flow:

Primary table → joined table

For example:

districts → expenditures

because the question is about districts.

This improves readability and makes queries easier for others to understand.

---

Key Takeaway

SQL allows flexibility, but understanding the semantics of joins is critical.

Remember:

INNER JOIN → order usually doesn't matter
LEFT / RIGHT / FULL JOIN → order matters

Recognizing this distinction helps avoid subtle bugs in complex queries.

---

Understanding join behavior is one of the moments when SQL starts to feel less like memorizing syntax and more like reasoning about relationships in data.