SQL Basics: SELECT, WHERE, ORDER BY

An UPSERT inserts-or-updates in one atomic statement, eliminating the race condition that exists when you SELECT first and then INSERT or UPDATE separately.

Use MERGE for multi-action ETL (insert, update, and delete in one pass) and INSERT ON CONFLICT for simple single-row upserts – and expect sequence gaps because auto-increment is consumed before a conflict is detected.

Candidate keys are all the ways to identify a row; the natural key is a real-world one, the surrogate is an invented meaningless one, the chosen one is primary and the rest are alternate keys – production prefers the surrogate because it never changes and leaks nothing.

Prevent lost updates by choosing the right concurrency control – pessimistic locks, optimistic version checks with retry, atomic CAS, database MVCC with isolation, or distributed Redis locks – according to contention and scope.

`SELECT ... FOR UPDATE` locks the read rows until commit so a read-check-then-update is atomic against competitors – use it sparingly because each lock adds contention.

DELETE removes chosen rows transactionally, TRUNCATE empties the whole table fast, and DROP destroys the table itself.

`UNION` merges and deduplicates, `UNION ALL` merges keeping duplicates and is faster, and `INTERSECT` returns only the rows common to both queries.

A leading wildcard forces a full scan; a tsvector plus GIN inverted index turns word search into an index lookup, and Elasticsearch is for distributed, rich, large-scale search.