One search. Connected ideas.

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.

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.

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.

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.

A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.

An ORM translates object-oriented data operations into database commands and maps results back into application objects. It reduces repetitive persistence code, but the database still executes real SQL with real locks, indexes and query costs. Understanding the generated query is essential when debugging correctness or performance.

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.

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.”

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.

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.

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.

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.

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.

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.

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.

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.”

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.

A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.

An ORM translates object-oriented data operations into database commands and maps results back into application objects. It reduces repetitive persistence code, but the database still executes real SQL with real locks, indexes and query costs. Understanding the generated query is essential when debugging correctness or performance.

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 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.

SQL & PostgreSQL learning module

SQL & PostgreSQL learning module

SQL & PostgreSQL learning module