Returns all rows when there is a match in one of the tables, including rows that do not have a match in the other table. NULL values are used where no match exists.
A FULL JOIN shows everything from both tables, highlighting where data exists in one but not the other.
Card 2
CROSS JOIN
Answer
Produces a Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.
Use CROSS JOIN with extreme caution, as it can generate very large result sets quickly, often for generating combinations or permutations.
Card 3
Scalar Subquery
Answer
A subquery that returns a single value, consisting of one row and one column. It can be used anywhere a single expression is valid.
Scalar subqueries are often used in the SELECT or WHERE clause to retrieve a single piece of information, like an average or a count.
Card 4
Index
Answer
A database object that provides fast lookup of rows in a table based on the values in one or more columns, similar to an index in a book.
Indexes dramatically speed up data retrieval operations (SELECT statements) at the cost of slightly slower data modification (INSERT, UPDATE, DELETE).
Card 5
PRIMARY KEY Constraint
Answer
A column or set of columns that uniquely identifies each row in a table. It enforces entity integrity and automatically creates a unique clustered index if one doesn't exist.
Every table should have a PRIMARY KEY to ensure data uniqueness and to serve as the main identifier for records.
Card 6
FOREIGN KEY Constraint
Answer
A column or set of columns in one table that refers to the PRIMARY KEY in another table, establishing a link between them. It enforces referential integrity.
FOREIGN KEYs are crucial for maintaining relationships between tables and preventing orphaned records.
Card 7
UNIQUE Constraint
Answer
Ensures that all values in a column or a set of columns are distinct. It allows NULL values but only one NULL value.
A UNIQUE constraint guarantees uniqueness without requiring the column to be the primary identifier for the table.
Card 8
NOT NULL Constraint
Answer
Ensures that a column cannot contain any NULL values. Every row must have a value for that column.
Use NOT NULL when a piece of information is absolutely essential for every record, preventing missing data.
Card 9
CHECK Constraint
Answer
Enforces domain integrity by limiting the range of values that can be placed in a column. It specifies a boolean expression that must be true for each row.
CHECK constraints are useful for validating data, such as ensuring a 'price' column always has a positive value.
Card 10
Query Optimizer
Answer
A component of a relational database management system (RDBMS) responsible for determining the most efficient way to execute an SQL query.
The query optimizer analyzes various execution plans based on statistics and indexes to minimize resource consumption and maximize speed.
Card 11
Execution Plan
Answer
A detailed set of steps that the database system uses to execute an SQL query. It shows operations like table scans, index seeks, and joins.
Understanding an execution plan is critical for diagnosing and improving slow-running queries.
Card 12
EXPLAIN Command
Answer
An SQL command used to display the execution plan chosen by the query optimizer for a given SQL statement without actually running the query.
EXPLAIN is the primary tool for developers and DBAs to see how their queries will perform and identify bottlenecks.
Card 13
Cardinality
Answer
In database context, it refers to the number of unique values in a specific column relative to the total number of rows in the table. High cardinality means many unique values.
High cardinality columns are generally good candidates for indexing, while low cardinality columns are often less effective for indexing.
Card 14
Data Skew
Answer
An uneven distribution of data values within a column, where certain values appear much more frequently than others. This can impact query performance.
Data skew can negatively impact query performance and index effectiveness, as the optimizer might make suboptimal choices if statistics don't accurately reflect the distribution.
Card 15
DENSE_RANK()
Answer
A window function that assigns a rank to each row within its partition, with no gaps in the ranking sequence when ties occur. Ties receive the same rank.
DENSE_RANK() is useful for ranking items where you want consecutive ranks even with duplicates, such as finding the top N performers without skipping ranks.
Card 16
Common Table Expression (CTE)
Answer
A temporary, named result set that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause.
CTEs improve query readability and modularity, making complex queries easier to understand and debug by breaking them into logical, reusable blocks.