GK One-Line Question & Answer

Sets the column value to its defined DEFAULT value (as specified in the column definition) resetting it to the default without needing to know the actual default value

Multi-row INSERT sends one network round-trip parses one SQL statement and applies one transaction commit for all rows vs. N round-trips/parses/commits for N single-row inserts - orders of magnitude faster for bulk loads

A high-performance bulk data loading operation that reads data directly from a file (CSV TSV etc.) into a table bypassing many SQL processing overheads - typically 10-100x faster than INSERT statements for large datasets

A phenomenon where a transaction re-executes a query and finds new rows that were inserted by another committed transaction since the first read - occurs at READ COMMITTED isolation level prevented by SERIALIZABLE

EXPLAIN shows the query execution plan (without executing); EXPLAIN ANALYZE actually executes the query and shows the plan with real timing/row counts - used to diagnose slow queries check index usage identify full table scans

A stored procedure automatically executed by the DBMS in response to specific DML events (INSERT, UPDATE, DELETE) on a table either BEFORE or AFTER the event or INSTEAD OF for views

Executing 1 query to fetch N parent records then N additional queries to fetch related child records for each parent (N+1 total queries) instead of using a single JOIN - causes severe performance problems as N grows

The DBMS may either skip the update entirely (optimized) or perform a no-op update; triggers still fire in most DBMS; the behavior depends on the DBMS implementation but no data change occurs

Pessimistic: locks data when reading to prevent concurrent modifications (SELECT FOR UPDATE); Optimistic: no locks checks at update time if data changed since read (using version column or timestamp) fails with retry if another update occurred

MySQL supports DELETE FROM t WHERE condition ORDER BY col LIMIT n to safely delete/update a bounded number of rows - prevents accidentally deleting millions of rows and allows safe batch operations

It ensures that INSERT and UPDATE operations through the view only accept rows that satisfy the view WHERE clause - preventing modifications that would make the row invisible through the view

Updating an indexed column requires updating both the table row AND all indexes on that column making writes more expensive (each index requires a delete+insert in the B-tree structure) - the more indexes the slower the UPDATE

Updating many rows efficiently by using a single UPDATE with a JOIN or CASE expression to set different values per row based on a mapping table rather than executing N individual UPDATE statements

It returns the values of modified rows from INSERT/UPDATE/DELETE operations enabling patterns like: capturing deleted row data into an audit table in a single statement or retrieving auto-generated IDs after INSERT without a separate SELECT

MERGE performs INSERT UPDATE or DELETE in a single atomic statement based on whether rows match between source and target - preferred when you need to synchronize two tables and want to avoid race conditions of separate read-then-write operations

DELETE logs each individual row deletion (fully logged) enabling row-level rollback and trigger firing; TRUNCATE uses minimal logging (deallocates entire data pages) making it much faster but in most DBMS still rollbackable within an explicit transaction

It acquires an exclusive lock on the selected rows preventing other transactions from modifying or locking those rows until the current transaction commits or rolls back - used to implement pessimistic locking for critical operations

Both raise constraint violation errors but the error codes differ: NOT NULL violation raises a specific null constraint error and CHECK constraint violation raises a check constraint violation - both prevent the INSERT from completing and the transaction remains in an error state requiring rollback

Oracle-specific SQL that inserts multiple rows into multiple tables in a single statement (unconditional or conditional INSERT ALL INTO t1...INTO t2...) solving the problem of making multiple table inserts atomic and efficient from a single data scan

Nested Loop: O(n*m) good for small tables/indexed access; Hash Join: O(n+m) good for large unindexed tables (requires memory for hash table); Sort-Merge: O(n log n + m log m) good when inputs are already sorted

All rows from BOTH tables with NULLs for non-matching sides: rows matched on both sides (complete data) unmatched rows from left table (right side NULL) and unmatched rows from right table (left side NULL)

A query pattern that returns rows from the left table that have NO matching rows in the right table implemented as: LEFT JOIN WHERE right.key IS NULL or NOT EXISTS (subquery) or NOT IN (subquery)

The query optimizer moves filtering conditions (predicates) as early as possible in the execution plan - before joining - to reduce the number of rows that need to be joined dramatically reducing the join cost

Using a FROM clause with multiple tables without specifying join conditions or using CROSS JOIN - detected by the absence of WHERE/ON conditions between tables and the result set size = product of table sizes

A join using comparison operators other than equality (>, <, >=, <=, BETWEEN, LIKE) in the join condition - e.g. joining employee salary to a salary_grade table where salary BETWEEN grade_low AND grade_high

A join where the right side (subquery or function) can reference columns from tables to its left in the FROM clause - allowing correlation within the FROM clause useful for top-N per group queries

The optimal sequence in which to join multiple tables; join order is critical because different orders produce dramatically different intermediate result set sizes and the optimizer must find the order minimizing total data processed

ON: allows any join condition including different column names (ON a.dept_id = b.id); USING: shorthand for equality join on same-named columns in both tables (USING(dept_id)) - eliminates duplicate columns in result

A technique to reduce data transfer in distributed joins: instead of shipping all of relation R to the site of relation S first compute the semi-join S semi-join R (much smaller) ship that to R site compute the join there - reduces network traffic

Left-deep: each inner side of a join is a base table (chain structure: ((A join B) join C) join D) - allows pipelining but limited join orders; Bushy: inner sides can be intermediate results ((A join B) join (C join D)) - more join orders can exploit parallelism

The fraction of rows in the cross product that actually match the join condition (result_rows / (|R| times |S|)); low selectivity (few matches) is better; optimizer uses statistics to estimate selectivity and choose between join methods

A join where for each row in the outer table the DBMS uses an index on the inner tables join key to directly look up matching rows - efficient when: outer table is small inner table has an appropriate index on the join column and join selectivity is low

When the query optimizer removes a JOIN from the execution plan because it can prove the join does not affect the result (typically: joining to a unique FK parent table when only the child columns are selected) reducing unnecessary I/O

Each matching combination of rows produces a row in the result - if table A has 3 rows with dept_id=5 and table B has 2 rows with id=5 the join produces 3 times 2=6 rows for dept_id=5 (multiplicative not additive)

An optimization for queries against star schemas: filter dimension tables first (highly selective) then use bitmap indexes to intersect the fact table rows matching ALL dimension filters avoiding full fact table scans

A hash join variant that partitions BOTH relations into buckets based on hash of join key (partitioning phase) then independently hash-joins matching partition pairs that fit in memory (probing phase) - handles relations larger than RAM by avoiding the need to hold everything in memory at once

Low cardinality join columns have poor index selectivity (index not beneficial for equality joins - will return many rows) cause data skew in hash joins (one bucket overloaded) and may require special handling like bitmap indexes or skew-aware hash distribution

Automatically joins on ALL columns with the same name in both tables using equality without requiring explicit ON clause - risky because schema changes (adding a new column with same name in both tables) silently change join semantics

Build side: the SMALLER relation - loaded entirely into hash table in memory. Probe side: the LARGER relation - each row is used to probe the hash table. Optimizer assigns smaller relation as build side to minimize memory requirements

All rows from RIGHT table (with NULLs for unmatched left side) and only matching rows from LEFT table - functionally equivalent to reversing table order and using LEFT OUTER JOIN (which is more readable and universally preferred)