Logo
Back

What is the difference between JOIN and INNER JOIN?

In most SQL dialects, JOIN is simply a shorthand for INNER JOIN, meaning both return only the rows where there is a matching condition in all the tables involved. However, understanding the nuances can help clarify why you might see different keywords in practice and how SQL engines interpret them.

JOIN vs. INNER JOIN

SQL Standard and Common Practice

  • INNER JOIN is explicitly defined in the SQL standard to return rows that satisfy the matching criteria across both tables.
  • JOIN (without a prefix) is generally understood to mean INNER JOIN in many SQL engines like MySQL or Microsoft SQL Server.

Why Both Terms Exist

  • Historical/Readability Reasons: Over time, SQL has evolved to include explicit join types (INNER, LEFT, RIGHT, FULL). The word “JOIN” alone often defaults to INNER, reflecting older SQL syntax when only “join” meant an inner join.
  • Self-Documentation: Writing INNER JOIN instead of JOIN can be clearer when reading a query, especially in codebases where multiple types of joins (LEFT, RIGHT, FULL) are used frequently.

Potential Dialect Differences

  • In most major relational databases (e.g., MySQL, PostgreSQL, SQL Server), JOIN defaults to INNER JOIN.
  • Some SQL dialects might be stricter or more explicit, but this is rare. Always refer to the specific SQL dialect documentation to confirm default behaviors.

Example

Using JOIN:

SELECT e.name, d.department_name
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id;

Using INNER JOIN:

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.department_id;

Both queries will return the same result set, including only those employee records that have a matching department.

When to Use Which

  • JOIN: If brevity is preferred and your team or codebase accepts that “JOIN” always means “INNER JOIN.”
  • INNER JOIN: When you want to be explicit about the type of join. This can improve readability in queries that mix multiple join types (e.g., a mixture of INNER, LEFT, and RIGHT joins).

Recommended Courses

  1. Grokking Database Fundamentals for Tech Interviews

  2. Relational Database Design and Modeling for Software Engineers

  3. Grokking System Design Fundamentals

Conclusion

Functionally, JOIN and INNER JOIN are identical in most SQL systems, as “JOIN” defaults to an inner join. The choice between them often comes down to readability and consistency in a given codebase.

CONTRIBUTOR
TechGrind