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.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.