SELECT Command

Overview

The SELECT statement is the primary way to read data from MaazDB. It supports complex filtering, sorting, and pagination. MaazDB includes a special optimization engine for Primary Key lookups.

Syntax

SELECT <columns> FROM <table_name>
[WHERE <condition>]
[ORDER BY <column> ASC|DESC]
[LIMIT <n> OFFSET <n>];

Basic Examples

Select All Columns

Retrieve every row and column from a table:

SELECT * FROM users;

Select Specific Columns

Retrieve only specific fields to reduce data transfer:

SELECT username, email, age FROM users;

Filtering (WHERE)

Use the WHERE clause to filter results. MaazDB supports standard comparison and logical operators.

Comparison Operators

-- Numeric comparison
SELECT * FROM products WHERE price > 50.00;

-- String matching
SELECT * FROM users WHERE username = 'Alice';

-- Boolean check
SELECT * FROM tasks WHERE completed = TRUE;

Logical Operators (AND / OR)

-- Both conditions must be true
SELECT * FROM users WHERE age > 21 AND active = TRUE;

-- Either condition can be true
SELECT * FROM users WHERE role = 'admin' OR role = 'moderator';

Complex Grouping

Use parentheses to control operator precedence:

SELECT * FROM orders 
WHERE (status = 'pending' AND total > 100) 
   OR (status = 'urgent');

NULL Checks

-- Find rows with missing values
SELECT * FROM users WHERE phone_number IS NULL;

-- Find rows with values present
SELECT * FROM users WHERE email IS NOT NULL;

Sorting & Pagination

ORDER BY

Sort results by a specific column. Default is ASC (Ascending).

-- Sort by age (Ascending)
SELECT * FROM users ORDER BY age;

-- Sort by price (Descending)
SELECT * FROM products ORDER BY price DESC;

LIMIT and OFFSET

Essential for implementing pagination in applications.

-- Get the first 10 rows
SELECT * FROM users LIMIT 10;

-- Get the next 10 rows (Skip 10, take 10)
SELECT * FROM users LIMIT 10 OFFSET 10;

Performance Optimization

Optimized Query

This query runs in constant time regardless of table size:

SELECT * FROM users WHERE id = 54321;

Standard Scan

This query requires scanning rows (O(n)) to find matches:

SELECT * FROM users WHERE email = 'john@example.com';

Aggregates & Expressions

Aggregates perform calculations on a set of values and return a single result.

Counting Rows

Count the total number of rows matching a condition:

SELECT COUNT(*) FROM users WHERE active = TRUE;

Math Aggregates

Calculate the sum, maximum, or minimum values of a column:

SELECT SUM(salary) FROM employees;
SELECT MAX(score) FROM players;
SELECT MIN(price) FROM products;

Evaluating Expressions

MaazDB can evaluate mathematical and logical expressions directly:

SELECT 10 * 5, 'Hello' + ' World', TRUE AND FALSE;

DML Comparison