Logo

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

In SQL, JOIN clauses allow you to fetch data from multiple tables in a single query. However, choosing the right type of join—INNER, LEFT, RIGHT, or FULL—is crucial for getting the desired result set. Below is a clear breakdown of how each join works and when to use it.

1. INNER JOIN

What It Does

  • Returns rows only when there is a matching value in both tables.
  • Excludes any row where the join condition is not met.

Example

SELECT A.column1, B.column2 FROM TableA A INNER JOIN TableB B ON A.shared_key = B.shared_key;
  • Here, a row from TableA merges with a row from TableB if they have the same shared_key. If A.shared_key doesn’t exist in TableB, that row won’t appear in the results.

Common Use Cases

  • Retrieving matching data between two or more tables.
  • When you only care about rows that have related data in all tables involved.

2. LEFT JOIN

What It Does

  • Returns all rows from the left (or “first-mentioned”) table, along with matching rows from the right table.
  • If no match is found in the right table, NULL values appear for the columns from the right table.

Example

SELECT A.column1, B.column2 FROM TableA A LEFT JOIN TableB B ON A.shared_key = B.shared_key;
  • The result set shows every row in TableA. If TableB lacks a matching shared_key, the columns from TableB will be NULL.

Common Use Cases

  • Reporting or auditing scenarios where you need to see which items in the left table have no corresponding entries in the right table.
  • Data analysis tasks that require a “master” list of entities from the left table.

3. RIGHT JOIN

What It Does

  • Returns all rows from the right (or “second-mentioned”) table, plus the matching rows from the left table.
  • If no match is found in the left table, NULL values appear for columns from the left table.

Example

SELECT A.column1, B.column2 FROM TableA A RIGHT JOIN TableB B ON A.shared_key = B.shared_key;
  • The result set includes every row in TableB. Rows in TableA that don’t match a row in TableB produce NULL for A’s columns.

Common Use Cases

  • Similar to LEFT JOIN, but you start with a “master” list in the right table instead of the left.
  • Not as common as LEFT JOIN in many coding practices (some developers just switch the table order and use LEFT JOIN instead).

4. FULL JOIN (FULL OUTER JOIN)

What It Does

  • Returns all rows from both tables.
  • Rows that have matching values across tables are merged. For rows without a match, you’ll see NULL in the columns from the other table.

Example

SELECT A.column1, B.column2 FROM TableA A FULL JOIN TableB B ON A.shared_key = B.shared_key;
  • Every row in TableA and TableB shows up at least once. Where a match is found, columns merge; where no match is found, columns from the non-existing side are NULL.

Common Use Cases

  • Comprehensive data analysis or debugging scenarios, where you need a complete snapshot of rows from both tables.
  • Less common in everyday OLTP (Online Transaction Processing) work due to the large, potentially sparse result sets.

5. Quick Comparison

  1. INNER JOIN

    • Only matching rows are returned.
    • Result set is typically the smallest among all join types (assuming the same join condition).
  2. LEFT JOIN

    • Returns all rows from the left table, matched or not.
    • Unmatched rows in the left table get NULL for right-side columns.
  3. RIGHT JOIN

    • Returns all rows from the right table, matched or not.
    • Unmatched rows in the right table get NULL for left-side columns.
  4. FULL JOIN

    • Returns all rows from both tables, matched or not.
    • Potentially the largest result set. Each table’s unmatched rows get NULL in the other table’s columns.

6. Performance Considerations

  1. Query Plans

    • The database’s optimizer looks at factors like indexing and filtering to decide the best strategy for joins.
    • Proper indexing on join columns can greatly improve speed.
  2. Data Volume

    • FULL JOIN can generate huge results if both tables are large. Use it only when truly needed.
    • INNER JOIN is often faster when you only need matching rows.
  3. NULL Handling

    • LEFT, RIGHT, and FULL can produce NULL values. Ensure your application logic (or follow-up queries) handle them correctly.

7. Level Up Your SQL Skills

Whether you’re starting out or preparing for a high-stakes interview, a solid grasp of JOIN operations is just one step toward SQL mastery. Here are some courses from DesignGurus.io to help you excel:

8. Prepare for Real-World and Interview Scenarios

  • Mock Interviews: Get personalized feedback on your SQL or System Design skills from ex-FAANG engineers.
  • Interview BootCamp: For a guided, step-by-step path, check out Interview BootCamp to sharpen your coding, system design, and behavioral interview skills.
  • DesignGurus YouTube Channel: Find tutorials, Q&A sessions, and detailed deep dives on topics like database design and coding interviews:
    DesignGurus YouTube Channel.

Conclusion

Understanding how INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN differ is fundamental for writing efficient and accurate SQL queries. Each join type serves a unique purpose—from focusing on strictly matched data to getting a complete combined view of both tables. By mastering these concepts and applying best practices, you’ll be well-prepared to tackle real-world database challenges and excel in technical interviews.

For deeper exploration, advanced examples, and a structured learning path, explore the courses on DesignGurus.io. Combining this knowledge with regular practice and mock interviews will elevate your SQL game for any data-driven role or project.

CONTRIBUTOR
TechGrind