For an instructor lead, in-depth look at learning SQL click below.
In the world of SQL, JOIN operation is a technique that combines rows from two or more tables based on a related column. The most commonly used joins in SQL are INNER JOIN and LEFT JOIN. Understanding how they work and what the differences are is crucial in building efficient queries. In this post, we’ll explore the difference between these two.
What is an INNER JOIN?
The INNER JOIN keyword selects all rows from both tables as long as the condition is true. It gets records that have matching values in both tables. If there is no match, the result is zero records.
|
1 2 3 4 5 6 |
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column; |
This SQL code demonstrates an INNER JOIN. We are joining table1 and table2 on the condition where their matching columns have the same values. As a result, we’ll get a merged table that contains only those records where the condition is met.
What is a LEFT JOIN?
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL on the right side, if there is no match.
|
1 2 3 4 5 6 |
SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column; |
Here, LEFT JOIN is selecting all records from table1 and only those from table2 where the condition is met. If there is no match for a record in the right table (table2), the result will still include the record from the left table (table1), but with NULL in each column from table2.
Comparing INNER JOIN and LEFT JOIN
The key difference between INNER JOIN and LEFT JOIN lies in their results: INNER JOIN returns only the rows where the condition is true, while LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If no match is found, the result is NULL on the right side.
Being proficient in SQL is all about understanding these small but critical differences. INNER JOIN and LEFT JOIN may seem similar, but their differences can significantly alter your query results and impacts on your data analytics efforts.