The difference between LEFT JOIN, INNER JOIN & RIGHT JOIN: & which SQL defaults to...

When working with SQL in Snowflake, joins are essential for combining data from multiple tables.

Three of the most commonly used join types are RIGHT JOIN, INNER JOIN and LEFT JOIN.

But, what's the difference between them? And what join does SQL default to if you don't specify one?

Let's break it down.

INNER JOIN- Matching Rows Only

An INNER JOIN returns only the rows that have matching values in both tables.

If there is no match, the row is excluded from the result.

Example:

Let’s say you have two tables: Employees and Salaries.

Employees Table:

id | name

1 | Alice
2 | Bob
3 | Carol

Salaries Table:

id | salary

1 | 5000
2 | 6000

If we run an INNER JOIN like this:

SELECT e.id, e.name, s.salary
FROM Employees e
JOIN Salaries s
ON e.id = s.id;

Result:

id | name | salary

1 | Alice | 5000
2 | Bob | 6000

Explanation: Only the employees who have matching salary records are included. Carol, who doesn't have a salary record, is excluded from the result.

LEFT JOIN – All Rows from the Left Table, Plus Matching Rows from the Right Table

A LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. If there is no match, it will still return the row from the left table, with NULL for the columns from the right table.

Example:

If we run a LEFT JOIN like this:

SELECT e.id, e.name, s.salary
FROM Employees e
LEFT JOIN Salaries s
ON e.id = s.id;

Result:

id | name | salary

1 | Alice | 5000
2 | Bob | 6000
3 | Carol | NULL

Explanation: All employees are included in the result, even Carol, who doesn't have a corresponding salary. Since Carol doesn’t have a salary record, the result for her salary is NULL.

RIGHT JOIN – All Rows from the Right Table, Plus Matching Rows from the Left Table

A RIGHT JOIN (also called RIGHT OUTER JOIN) works similarly to a LEFT JOIN, but it returns all rows from the right table and matching rows from the left table. If there’s no match, it will return NULL for the left table's columns.

Example:

Let’s run a RIGHT JOIN like this:

SELECT e.id, e.name, s.salary
FROM Employees e
RIGHT JOIN Salaries s
ON e.id = s.id;

Result:

id | name | salary

1 | Alice | 5000
2 | Bob | 6000
NULL | NULL | 6000

Explanation: This query returns all rows from the Salaries table, even if there’s no corresponding employee. For example, if there’s a salary record with no matching employee, the result for the employee's id and name will be NULL.

Snowflake Default Join Type

In Snowflake, if you use the JOIN keyword without specifying whether it's an INNER JOIN, LEFT JOIN, or any other type, Snowflake defaults to an INNER JOIN.

So, if you just write:

SELECT e.id, e.name, s.salary
FROM Employees e
JOIN Salaries s
ON e.id = s.id;

It’s the same as explicitly writing:

SELECT e.id, e.name, s.salary
FROM Employees e
INNER JOIN Salaries s
ON e.id = s.id;

Snowflake will only return the rows that have matching values in both tables, excluding any rows that don’t have a match.

Quick Recap:

• INNER JOIN: Returns only the rows that match in both tables.
• LEFT JOIN: Returns all rows from the left table, plus matching rows from the right table. If no match, it returns NULL for the right table’s columns.
• RIGHT JOIN: Returns all rows from the right table, plus matching rows from the left table. If no match, it returns NULL for the left table’s columns.
• Snowflake Default: Snowflake defaults to INNER JOIN when you simply write JOIN.

Conclusion

When working with SQL joins, understanding the difference between an INNER JOIN, LEFT JOIN, and RIGHT JOIN is essential for getting the results you need. Snowflake defaults to INNER JOIN if you don’t specify the type, meaning you’ll only get rows that have matching values in both tables. If you need to include all rows from one table, even if there’s no match, use LEFT JOIN or RIGHT JOIN accordingly.