SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
WHERE vs HAVING: what is the difference?
WHERE filters individual rows before groups are built, while HAVING filters the groups produced by aggregation. They exist at different logical stages of a query. Use WHERE for conditions on source rows and HAVING for conditions such as “the group's total is greater than 1000.”
What is the difference between the join types?
A join combines rows from two table expressions according to a relationship or condition. Inner joins keep matching pairs, while outer joins also keep unmatched rows from specified sides with NULL values for the missing side. The correct join type follows the business requirement about which records must remain visible.
Why does my join return duplicate rows?
A join returns one output row for each matching combination, so repeated parent values are often the correct result of a one-to-many relationship. Unexpected duplicates usually mean the query's relationship or desired result level was misunderstood. The fix is to align the join or aggregation with whether the output should represent a customer, an order or an order item.
Why does a LEFT JOIN behave like an INNER JOIN?
A LEFT JOIN preserves left-side rows by filling unmatched right-side columns with NULL. A later WHERE condition can remove those rows if it requires a right-side value to match. This makes the final result look like an inner join even though the join itself was outer.
How should I compare NULL values?
SQL NULL represents missing or unknown information, not an ordinary value such as zero or an empty string. Comparisons involving NULL usually produce unknown rather than true or false, and WHERE keeps only true conditions. Special predicates such as IS NULL make the intended check explicit.
Subquery vs EXISTS vs CTE?
A subquery supplies a result inside another query, EXISTS checks whether a matching result exists, and a CTE gives a named query expression that can be referenced later. These are ways to structure SQL and express relationships. The optimizer and data determine performance, so syntax alone does not tell you which is fastest.
When should I use a recursive CTE or set operation?
A recursive CTE repeatedly extends an initial result to walk relationships such as a manager hierarchy. Set operations combine compatible result sets by keeping all rows, common rows or differences. They solve different needs: recursion follows links, while set operations compare or combine complete results.
GROUP BY vs a window function?
GROUP BY collapses source rows into one row per group, while window functions calculate across related rows without removing the individual rows. A window is defined by partitions, ordering and sometimes a frame. This makes windows useful when a report needs both detail and comparisons such as department rank.
ROW_NUMBER vs RANK vs DENSE_RANK?
These window functions assign positions within an ordered partition. ROWNUMBER gives each row a different number, RANK leaves gaps after ties, and DENSERANK does not leave gaps. The desired meaning of “top N” determines which one is correct.
What are LAG, LEAD and running totals?
LAG and LEAD read values from earlier or later rows within an ordered window. A running total uses an aggregate window that expands through the current row. These calculations support trends, comparisons and cumulative reports while preserving detail records.
What are primary keys, foreign keys and constraints?
Keys identify records and relationships, while constraints tell the database which stored states are allowed. A primary key identifies each row, a foreign key validates a relationship, and other constraints enforce required or unique values and conditions. Database enforcement protects data even when different application paths write concurrently.
What are 1NF, 2NF and 3NF?
Normalization organizes tables so each fact is stored in an appropriate place and changes do not create inconsistent copies. The first three normal forms progressively address repeating groups, dependence on only part of a key, and indirect dependence through other non-key facts. This reduces update, insert and delete anomalies.
What does ACID mean?
ACID describes four properties used to reason about reliable transactions: atomicity, consistency, isolation and durability. Together they address partial changes, preserved rules, concurrent work and persistence after commit. They do not automatically supply correct business rules or the strongest isolation level.
What are isolation levels and read anomalies?
Isolation defines how one transaction can observe and interfere with concurrent transactions. Different levels allow different anomalies to balance correctness, concurrency and retry cost. PostgreSQL uses snapshots under MVCC, but even a stable snapshot does not automatically make every business decision safe against concurrent writes.
Optimistic vs pessimistic locking?
Optimistic locking checks whether data changed before committing an update, often by comparing a version number. Pessimistic locking acquires a database lock before performing a critical change. The first avoids waiting when conflicts are rare; the second coordinates competing writers but can increase waits and deadlocks.
What is a deadlock, and how do I handle it?
A deadlock is a cycle of waiting transactions: each holds a lock another needs to continue. The database detects the cycle and cancels a participant so the others can proceed. Applications must treat that cancellation as a failed transaction and decide whether to retry it.
Which indexes should I know?
An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.
EXPLAIN vs EXPLAIN ANALYZE?
EXPLAIN shows the execution plan the database expects to use. EXPLAIN ANALYZE actually executes the statement and adds observed rows and timing, which helps reveal bad estimates or expensive steps. Since it runs the statement, write queries and expensive reads need appropriate care.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.