Compare

Side-by-side answers to the SQL questions that get Googled in the form "X vs Y." When two constructs do almost the same thing, the interesti...

Short answer: INNER JOIN returns only rows where both tables match; LEFT JOIN returns every row from the left table and pads NULL where the ...

Short answer: WHERE filters rows before grouping; HAVING filters groups after grouping. Use WHERE for conditions on raw columns, HAVING for ...

Short answer: UNION removes duplicate rows from the combined result; UNION ALL keeps every row. UNION ALL is faster because it skips the ded...

Short answer: DELETE removes specific rows and the table stays intact. TRUNCATE empties the table fast, page-level, no WHERE clause. DROP TA...

Short answer: All three name an intermediate result so you can use it later. CTEs (WITH ... AS) are scoped to one query and read top-to-bott...

Short answer: COUNT() and COUNT(1) count every row in the group. They are identical in modern engines, both in result and in performance. CO...

Short answer: on Postgres, VARCHAR (with no length), VARCHAR(n), and TEXT are all stored identically — performance is the same, the only pra...

Short answer: DECIMAL and NUMERIC are synonyms on every major engine: both are exact, fixed-precision types. FLOAT (and REAL, DOUBLE) is a b...

Short answer: the names mean different things on different engines, and that's the source of most timezone bugs. The only durable framing: t...

Short answer: CHAR(n) is a fixed-length string: every value is padded with spaces to exactly n characters. VARCHAR(n) is variable-length, wi...

Short answer: SMALLINT is 2 bytes (~±32K), INT is 4 bytes (~±2.1B), BIGINT is 8 bytes (~±9.2 quintillion). Use INT for almost everything; us...