SQL Fundamentals

SELECT, WHERE, ORDER BY

A query reads, left to right in intent (though not in execution order): pick some columns, from some table, keeping only rows that match a condition, sorted some way.

SELECT name, price
FROM products
WHERE price > 10 AND category = 'electronics'
ORDER BY price DESC
LIMIT 10;

ORDER BY without it, row order is unspecified — not "insertion order" or anything else predictable. If order matters to your application, you need an explicit ORDER BY, full stop.

NULL is not a value — it's the absence of one

SQL uses three-valued logic: every comparison is TRUE, FALSE, or UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN, and WHERE only keeps rows where the condition is TRUE — UNKNOWN rows are silently dropped.

SELECT * FROM users WHERE age = NULL;      -- returns ZERO rows, always — not an error, just always false
SELECT * FROM users WHERE age IS NULL;   -- the correct way to test for NULL
SELECT * FROM users WHERE age <> NULL;     -- also always UNKNOWN, same trap

This is the single most common beginner bug in SQL. If a query is mysteriously returning fewer rows than expected, check for a NULL hiding in a comparison.

comparison & logical operators

Operator	Notes
`BETWEEN a AND b`	inclusive on both ends — equivalent to `col >= a AND col <= b`
`IN (1, 2, 3)`	shorthand for chained `OR` equality checks; also accepts a subquery
`LIKE 'A%'`	`%` matches any sequence, `_` matches exactly one character; case-sensitive
`ILIKE 'a%'`	PostgreSQL-specific case-insensitive `LIKE`

Operator precedence: NOT binds tighter than AND, which binds tighter than OR. When mixing them, parenthesize — relying on precedence rules you have to look up is how bugs happen.

basic data types (portable across engines)

Category	Examples
Numeric	integer, decimal/numeric, float
String	char(n) fixed-width, varchar(n)/text variable-width
Date/time	date, time, timestamp
Boolean	boolean (not all engines have a native one — MySQL historically faked it with tinyint)

PostgreSQL's specific type system, including the ones with no real equivalent elsewhere (jsonb, arrays, ranges), is covered in Data Types & Schema Design.

where to go from here

PostgreSQL Fundamentals — get a real database running and connect to it.
Joins Explained — the next thing you need once data lives in more than one table.