Minimum Viable SQL Patterns

Question 1

What happens when you add a filter to a WHERE clause for a LEFT JOIN table in SQL?

Answer 1

Adding a filter in the WHERE clause turns a LEFT JOIN into an INNER JOIN. To preserve the LEFT JOIN, put the filter in the ON clause.

Example:

Incorrect (becomes INNER JOIN):

```sql
SELECT COUNT(*)
FROM post_history ph
LEFT JOIN users u ON u.id = ph.user_id
WHERE u.reputation >= 500000;
~~~

Correct (stays LEFT JOIN):

```sql
SELECT COUNT(*)
FROM post_history ph
LEFT JOIN
users u ON u.id = ph.user_id
AND u.reputation >= 500000;
~~~

Question 2

What is the difference between UNION and UNION ALL in SQL?

Answer 2

UNION removes duplicate rows between result sets, performing an implicit DISTINCT, which can be slower.
UNION ALL keeps all rows—including duplicates—and is faster because it skips the deduplication step.