Yes, you can perform a JOIN in MySQL without using the JOIN keyword. This older syntax uses multiple tables in the FROM clause and specifies the join condition in the WHERE clause. It works, but it behaves differently for INNER and OUTER joins.

1. INNER JOIN Using WHERE

1
• Before ANSI JOIN syntax became standard, INNER JOINs were commonly written using WHERE.
2
• This behaves exactly like an INNER JOIN.
3
4
Example:
5
6
SELECT *
7
FROM users u, orders o
8
WHERE u.id = o.user_id;
9
10
• Returns only rows where both tables have matching values.

2. Why It Does NOT Work for OUTER JOINs

1
• LEFT JOIN or RIGHT JOIN cannot be expressed correctly with WHERE conditions.
2
• The WHERE clause filters out NULLs, turning the join into an INNER JOIN.
3
• Therefore, outer joins must use explicit JOIN ... ON syntax.

3. Why Explicit JOIN Is Preferred

1
• JOIN ... ON keeps join logic separate from filtering logic.
2
• Clearer and more readable.
3
• Prevents accidental conversion of OUTER JOIN → INNER JOIN.
4
• ANSI-standard and recommended for modern SQL.

In summary: JOINs can be written using WHERE, but this is safe only for INNER JOINs. For LEFT/RIGHT OUTER JOINs, the explicit JOIN keyword must be used.

Difficulty: 4/10

Topics: implicit joins, WHERE clause, SQL readability

Scenario Questions

0-2 years experience

1
You have tables users and orders. Write a query that returns each user's name and the number of orders they placed, but you must not use the JOIN keyword. How would you do it?
2
If you write SELECT * FROM users, orders WHERE users.id = orders.user_id, what result do you expect compared to using INNER JOIN?
3
What happens if you forget the join condition in the WHERE clause when using this comma‑separated style?

2-5 years experience

1
Our reporting feature uses three tables joined via commas and WHERE conditions. After a recent schema change the query started returning duplicate rows. Walk me through how you'd debug it.
2
Explain the trade‑offs of keeping implicit joins in a codebase that many developers maintain versus switching to explicit JOIN syntax.
3
A teammate replaced an explicit LEFT JOIN with a WHERE‑based join and now the query returns fewer rows. Why might that happen?

5-8 years experience

1
We process billions of rows daily and some legacy queries use implicit joins. How would you assess the performance impact and decide whether to rewrite them to explicit JOINs?
2
Design a migration plan to refactor all implicit joins in a large monolithic service to explicit JOINs while minimizing downtime and regression risk.
3
What edge cases (e.g., outer joins, self‑joins) become problematic with WHERE‑based joins, and how would you ensure correctness at scale?

8+ years experience

1
At the organization level we're standardizing SQL style guidelines. How would you argue for or against mandating explicit JOIN syntax across all teams, considering tooling, onboarding, and optimizer behavior?
2
If we must continue supporting a legacy application that only generates implicit join queries, what architectural decisions would you make to abstract the SQL generation while allowing a future migration to modern syntax?

Follow-up Questions

❓How does the optimizer treat implicit versus explicit joins?
❓Can you describe a case where an implicit join could give wrong results?
❓What steps would you take to verify that a rewritten explicit‑JOIN query is equivalent?