Ruby.CodeCompared.To/SQL

SQL can evaluate expressions without referencing any table. The AS keyword gives the result column a name. In Ruby you compute expressions directly.

SQL's built-in math functions — abs, round, sqrt, ceil, floor, pow, log — map closely to Ruby's Numeric methods and the Math module.

SQLite string functions (upper, lower, trim, length, substr, replace, instr) correspond directly to Ruby's String methods. SQL uses positional arguments; Ruby uses methods on the receiver.

SQL uses || for string concatenation; Ruby uses + or string interpolation. The || operator is defined by the SQL standard; some databases use CONCAT() instead, but SQLite uses ||.

SQLite stores dates as text in ISO 8601 format (YYYY-MM-DD). The date(), time(), and datetime() functions accept modifier strings like '+1 month', '-7 days', and 'start of year'. Ruby's Date and Time classes offer equivalent arithmetic.

Employee.all in ActiveRecord generates SELECT * FROM employees. The * wildcard selects every column; in production code, prefer naming columns explicitly to avoid surprises when the schema changes.

Employee.select(:name, :salary) in ActiveRecord. Selecting only the columns you need reduces memory usage and network transfer — especially important when tables have many columns or large text/blob fields.

Employee.select("name, salary / 12.0 AS monthly_salary") in ActiveRecord. The AS keyword renames a column in the result set — it does not affect the underlying data. Any expression can be aliased: arithmetic, function calls, or CASE expressions.

Employee.distinct.pluck(:department) in ActiveRecord. DISTINCT removes duplicate rows from the result. It applies to the entire row, not just one column — SELECT DISTINCT first_name, last_name returns unique (first, last) pairs.

Employee.where(department: 'Engineering') in ActiveRecord. The WHERE clause filters rows before they are returned. String literals in SQL use single quotes; double quotes are for identifiers (table and column names).

Employee.where('salary > ?', 80_000) in ActiveRecord. SQL comparison operators — >, <, >=, <=, =, != (or <>) — work on numbers, dates, and strings.

Employee.where(department: 'Engineering').or(Employee.where('salary > ?', 90_000)) in ActiveRecord. Use parentheses to control precedence when mixing AND and OR: (A OR B) AND C behaves differently from A OR (B AND C).

Employee.where(department: ['Engineering', 'Finance']) in ActiveRecord. IN is equivalent to a series of OR equality checks. NOT IN excludes those values — but watch out: NOT IN (list) returns no rows if NULL is in the list.

Employee.where(salary: 75_000..87_000) in ActiveRecord. BETWEEN low AND high is inclusive on both ends — equivalent to salary >= 75000 AND salary <= 87000. Ruby's Range#cover? is also inclusive by default for numeric ranges.

Employee.where("name LIKE ?", "Alice%") in ActiveRecord. In LIKE patterns, % matches any sequence of characters and _ matches exactly one character. Use ILIKE (or LIKE with LOWER()) for case-insensitive matching — SQLite's LIKE is case-insensitive for ASCII letters by default.

Employee.where(manager_id: nil) in ActiveRecord. In SQL, NULL represents an unknown or absent value. Never use = NULL or != NULL — they always evaluate to UNKNOWN, not TRUE. Always use IS NULL or IS NOT NULL. In Ruby, nil behaves like a normal falsy value — there is no such gotcha.

Employee.order(salary: :desc) in ActiveRecord. Without ORDER BY, the row order returned by a SQL query is undefined — do not rely on insertion order. ASC (ascending) is the default; DESC must be stated explicitly.

Employee.order(department: :asc, salary: :desc) in ActiveRecord. Multiple ORDER BY columns are evaluated left to right — rows are first sorted by department, then within each department by salary descending.

Employee.order(salary: :desc).limit(3) in ActiveRecord. LIMIT should almost always be used with ORDER BY — without ordering, which rows you get is undefined and can vary between queries.

Employee.order(salary: :desc).limit(2).offset(2) in ActiveRecord. OFFSET n skips the first n rows. This "skip-limit" pagination works well for small datasets but becomes slow on large tables because the database still scans all skipped rows. For large datasets, keyset pagination (WHERE id > last_seen_id) is more efficient.

Employee.count and Employee.distinct.count(:department) in ActiveRecord. count(*) counts all rows including those with NULL values. count(column) counts only non-NULL values in that column.

Employee.sum(:salary) and Employee.average(:salary) in ActiveRecord. avg() ignores NULL values — a row with NULL salary is not counted. This differs from dividing sum by count(*), which would treat missing salaries as zero.

Employee.minimum(:salary) and Employee.maximum(:salary) in ActiveRecord. min() and max() work on strings and dates too — SQLite compares date strings lexicographically, which works correctly for ISO 8601 format (YYYY-MM-DD).

Employee.group(:department).count and Employee.group(:department).average(:salary) in ActiveRecord. Every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function — this rule trips up many SQL newcomers.

Employee.group(:department).having("count(*) > 1").count in ActiveRecord. HAVING filters after grouping — it is for aggregate conditions what WHERE is for row conditions. You cannot use WHERE to filter on count(*) because WHERE runs before grouping.

A CASE WHEN ... THEN ... END expression inside an aggregate function is a powerful pattern — it counts or sums only rows that satisfy a condition, all in a single pass over the data. This is sometimes called "pivot aggregation" and has no direct ActiveRecord equivalent; it requires raw SQL or Arel.

Employee.joins(:department).select('employees.*, departments.name AS dept_name') in ActiveRecord. An INNER JOIN returns only rows where the join condition matches in both tables — employees with no matching department (or with a NULL department_id) are excluded from the result.

Employee.left_outer_joins(:department) in ActiveRecord. A LEFT JOIN keeps all rows from the left table even when there is no match in the right table — the unmatched right-side columns are NULL. Use a LEFT JOIN combined with WHERE right.id IS NULL to find records that lack an association.

Joining and then grouping is one of the most common SQL patterns. Using a LEFT JOIN here ensures that departments with no employees still appear in the results with a count of zero — an INNER JOIN would silently omit them.

A self join joins a table to itself using table aliases. It is the standard way to query hierarchical data — org charts, category trees, threaded comments — stored as an adjacency list in a single table. In ActiveRecord: belongs_to :manager, class_name: 'Employee' and has_many :reports, class_name: 'Employee', foreign_key: :manager_id.

Employee.create(name: 'Bob', department: 'Marketing', salary: 75_000) in ActiveRecord. The column-list form of INSERT INTO table (col1, col2) VALUES (...) is preferred in production — it remains correct when columns are added or reordered later.

Product.insert_all([...]) in ActiveRecord inserts multiple rows in a single SQL statement — far more efficient than calling .create in a loop. A multi-row INSERT sends one round-trip to the database instead of one per record.

Employee.where('salary < ?', 85_000).update_all('salary = salary * 1.1') in ActiveRecord. UPDATE ... SET ... WHERE modifies every matching row in one SQL statement without loading records into memory. Omitting the WHERE clause updates every row in the table.

Employee.where(active: false).delete_all in ActiveRecord skips callbacks and deletes directly. Use destroy_all when before_destroy / after_destroy callbacks must run — but destroy_all loads every record into memory first, making it much slower on large tables.

Setting.upsert({ key: 'theme', value: 'dark' }, unique_by: :key) in ActiveRecord. SQLite's INSERT OR REPLACE deletes the conflicting row and inserts a new one (resetting auto-increment IDs and all columns). The SQL standard form, INSERT INTO ... ON CONFLICT(key) DO UPDATE SET value = excluded.value, updates only specific columns and is supported in SQLite 3.24+.

Rails migrations abstract the SQL DDL — create_table generates the CREATE TABLE statement for whatever database is configured. Rails automatically adds the id column (INTEGER PRIMARY KEY AUTOINCREMENT) unless you specify id: false.

SQLite uses dynamic "type affinity" — a column declared as TEXT can store an integer. SQLite 3.37+ supports STRICT tables that enforce declared types. Rails maps database types to Ruby types automatically (e.g., :decimal → BigDecimal, :boolean → true/false).

Database constraints are enforced regardless of which application (or which language) accesses the database. ActiveRecord model validations (like validates :score, numericality: { greater_than_or_equal_to: 0 }) are application-level only — a direct SQL INSERT bypasses them. Rely on database constraints for true data integrity.

In SQLite, foreign key enforcement is disabled by default and must be enabled with PRAGMA foreign_keys = ON at the start of each connection. Rails 5+ sets this pragma automatically via the SQLite3 adapter. Without it, the REFERENCES declaration is parsed but not enforced.

SQLite's ALTER TABLE is more limited than other databases: before SQLite 3.35.0 you could not drop columns, and changing a column's type or constraints still requires recreating the table. Rails migrations handle this complexity automatically — the migration DSL generates the correct SQL for each database adapter, including table rebuilds when necessary.

Indexes dramatically speed up queries that filter, sort, or join on the indexed column. The trade-off is slightly slower writes and more storage. Index every foreign key column and any column that frequently appears in WHERE or ORDER BY clauses. A composite index (a, b) can also serve queries on a alone but not queries on b alone.

A unique index is both a performance index and a data integrity constraint. ActiveRecord's validates :email, uniqueness: true checks uniqueness at the application level, but it has a race condition in concurrent environments. The database-level unique index is the only reliable guarantee — it is enforced atomically by the database engine.

EXPLAIN QUERY PLAN shows whether SQLite uses an index (SEARCH ... USING INDEX) or performs a full table scan (SCAN employees). A full scan on a large table with a selective WHERE clause usually indicates a missing index. ActiveRecord's .explain method runs the equivalent and formats the output.

ActiveRecord::Base.transaction { ... } wraps the block in BEGIN/COMMIT. Any exception raised inside the block automatically triggers ROLLBACK. Transactions guarantee atomicity — either all operations succeed together or none of them take effect. Without a transaction, a server crash between the two UPDATEs would leave the accounts in an inconsistent state.

Inside a Rails transaction block, raise ActiveRecord::Rollback to cancel the transaction without propagating the exception to the caller. Any other exception both rolls back the transaction and propagates upward. After a ROLLBACK, the database is exactly as it was before the BEGIN.

Nested ActiveRecord::Base.transaction { ... } blocks use savepoints automatically. Raising ActiveRecord::Rollback inside a nested transaction rolls back only that nested scope without affecting the outer transaction. RELEASE SAVEPOINT (like committing a nested transaction) merges the savepoint's work into the surrounding transaction.

A scalar subquery is a SELECT that returns exactly one row and one column, used inline as a value expression. The database evaluates it once and substitutes the result. When a scalar subquery could return more than one row, the query fails at runtime with an error.

Employee.where(department_id: Department.where(profitable: true).select(:id)) in ActiveRecord composes the two queries into a single efficient SQL statement. The inner SELECT is executed by the database engine without loading intermediate results into Ruby memory.

EXISTS stops scanning as soon as it finds one matching row — it is often more efficient than IN for large subsets because it short-circuits. NOT EXISTS finds records that lack an association. The conventional SELECT 1 inside EXISTS is idiomatic; any expression works since only the existence of a row matters.

A correlated subquery references a column from the outer query (outer_row.department here). It runs once for each outer row. For the "top earner per group" pattern, a window function — RANK() OVER (PARTITION BY department ORDER BY salary DESC) — is often cleaner and more efficient.

A CTE (Common Table Expression) names a subquery so it can be referenced like a table elsewhere in the query. CTEs make complex queries readable by giving intermediate results meaningful names — like assigning a computed value to a variable before using it. In ActiveRecord, CTEs require raw SQL via .from(Arel.sql("WITH ... SELECT ...")) or the activerecord-cte gem.

Multiple CTEs are separated by commas and evaluated in order. A later CTE can reference earlier ones by name. Think of each CTE as a named pipeline stage — totals computes department summaries, then ranked adds rank numbers. This "step-by-step assembly" style is far more readable than equivalent deeply nested subqueries.

A recursive CTE has two parts joined by UNION ALL: the anchor (the base case — top-level rows where manager_id IS NULL) and the recursive member (which references the CTE itself to add one more level). This is the standard SQL way to walk tree structures stored as adjacency lists — org charts, category hierarchies, folder trees.

Window functions compute a value for each row using a set of related rows — without collapsing them into groups the way GROUP BY does. PARTITION BY divides rows into independent windows (one per department here); ORDER BY inside the window function determines the ranking order. ActiveRecord has no direct equivalent; use .select(Arel.sql(...)) or the window_functions gem.

RANK() skips numbers after ties — two players tied at rank 1 means the next player is rank 3. DENSE_RANK() never skips — the next distinct score gets the next consecutive rank. ROW_NUMBER() assigns unique sequential numbers with no ties, even among equal values.

SUM(amount) OVER (ORDER BY month) accumulates the sum row by row — a running total. The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW frame limits the window to the current row and the two rows before it, producing a 3-month moving average. Expressing these patterns with ActiveRecord alone requires raw SQL — they are impractical with the query DSL.

LAG(column, n) accesses the value n rows back; LEAD(column, n) accesses n rows ahead. Both return NULL when the offset falls outside the window. Month-over-month percentage change is one of the most common analytics patterns — it requires a window function or a self join and has no clean ActiveRecord equivalent.