Tag: sql
All the articles with the tag "sql".
Why MySQL says "Unknown column" for an alias you just defined
You aliased a column in SELECT, used it in WHERE, and got ERROR 1054. ORDER BY accepts the same alias without complaint. Here's the one rule that explains both.
You created an index and the query is still slow. Here's why MySQL ignores it.
EXPLAIN says type: ALL and key: NULL even though the index exists. Four common reasons an index you just built gets skipped, and how to confirm which one you hit.
Your WHERE <> filter is silently dropping every NULL row
WHERE role <> 'TA' looks like it means everyone who isn't a TA. It means everyone we know for certain isn't a TA. Nothing errors, the result is just short.
Does MySQL need a COMMIT after INSERT? No, and that's the problem
The answer is no, MySQL autocommits by default. The interesting part is that typing COMMIT anyway raises no error, so a wrong mental model can survive for years.
Why your JOIN returns more rows than either table
A JOIN that returns 40,000 rows from two tables of 200 usually means one thing. Underneath, every JOIN is a Cartesian product with a filter on top.