sqlcmd.net validated sql reference

Tag archive

#pitfall

Commands grouped around the same concept, pattern, or recurring problem.

8 commands with this tag Browse related patterns quickly
intermediate filtering MySQL MariaDB SQL Server PostgreSQL SQLite

Avoid Duplicate Parent Rows With EXISTS

Use `EXISTS` when you only need to test whether a related row exists, not return one output row per match.

intermediate set-operations MySQL MariaDB SQL Server PostgreSQL SQLite

Choose UNION Or UNION ALL Intentionally

`UNION` removes duplicates, while `UNION ALL` keeps them. Picking the wrong one silently changes row counts.

intermediate joining MySQL MariaDB SQL Server PostgreSQL SQLite

Filter LEFT JOIN Matches In The ON Clause

Put right-table filters in the `ON` clause when you want to keep unmatched left-side rows.

advanced windowing MySQL MariaDB SQL Server PostgreSQL SQLite

Group Consecutive Rows With A ROW_NUMBER Difference

Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.

beginner aggregation MySQL MariaDB SQL Server PostgreSQL SQLite

Understand COUNT(column) Versus COUNT(*)

`COUNT(column)` skips `NULL` values, while `COUNT(*)` counts every row that made it into the result set.

beginner pagination MySQL MariaDB SQL Server PostgreSQL SQLite

Use LIMIT Or TOP With ORDER BY For Stable Results

A row limit without an explicit sort can return different rows over time or across engines.

intermediate null-handling MySQL MariaDB SQL Server PostgreSQL SQLite

Use NOT IN Safely When NULLs Are Possible

Filter `NULL` values out of the right side before using `NOT IN`, or the predicate can exclude everything.

intermediate grouping MySQL MariaDB SQL Server PostgreSQL SQLite

Use WHERE Before GROUP BY And HAVING After

`WHERE` filters individual rows before grouping. `HAVING` filters grouped results after aggregates are computed.