More dangerous subtleties of JOINs in SQL
Baron Schwartz gives two great examples of just how common - yet profoundly consequential - semantic errors in SQL can be. Seemingly trivial differences in syntax can produce results that look like they're correct - that is, what the user intended to produce - but in actuality are not. These bugs do not show up at compilation or run-time, but rather in meetings when colleagues or clients scratch their head over numbers that "don't look right." Semantic errors are especially insidious because they can remain undetected for months until people happen to look in the right places, at which point the damage may be already done. Could you imagine trying to manage your cash flow based on a completely misreported balance sheet?
A common misconception about SQL joins is that they give you as many rows as the join specifies - for example, a left join will give you the amount of rows in the left table, or an inner join will give you the amount of rows common to both tables.
This is not true: SQL joins will give you at least as many rows as the join specifies. For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after the join, but you may also have 20 or 100 depending on what you are joining to.
