Elementary Queries: Reading and Filtering Data

The most common operation in any database is fetching data. However, in production, "SELECT *" is considered a bad practice.

Projections: Selecting Specific Columns

Always specify the columns you need. This reduces network overhead and prevents issues if the table schema changes (e.g., adding a massive BLOB column).

sql code
-- GOOD: Specific columns
SELECT user_id, first_name, email FROM users;

-- BAD: Selecting everything
SELECT * FROM users;

Filtering with WHERE

The WHERE clause is optimized by indexes. Common operators include =, <>, >, <, LIKE, IN, and BETWEEN.

sql code
SELECT title, price 
FROM products 
WHERE category = 'Electronics' 
  AND price BETWEEN 100 AND 500
  AND stock_quantity > 0;

Pattern Matching with LIKE

• % matches zero or more characters.
• _ matches exactly one character.

sql code
SELECT * FROM employees WHERE last_name LIKE 'Sm%';

Sorting and Limiting Results

Use ORDER BY to sort data. Important: Sorting is expensive. Ensure you have an index on the sorted column for large datasets.

sql code
SELECT name, created_at 
FROM posts 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20; -- Pagination pattern

Offset Pagination vs Cursor Pagination

While LIMIT/OFFSET is easy, it gets slower as the offset increases. High-scale apps use Cursor-based pagination (filtering by the last seen ID/Timestamp).