SQL flashcards, generated for you.

Structured Query Language. It's used to query, insert, update, and delete data in relational databases using declarative statements.

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Note: SELECT is evaluated after WHERE and GROUP BY, not before.

WHERE filters rows before aggregation (GROUP BY). HAVING filters groups after aggregation. WHERE works on raw columns; HAVING works on aggregate functions like COUNT(), SUM().

Non-aggregated columns in SELECT must appear in GROUP BY or be wrapped in an aggregate function. Without aggregation logic, the database doesn't know which row value to return for that column per group.

INNER: returns only matching rows from both tables. LEFT: returns all rows from left table + matching rows from right (NULLs for non-matches). FULL OUTER: returns all rows from both tables with NULLs where no match.

NULL comparisons always evaluate to UNKNOWN, not TRUE or FALSE. WHERE NULL = NULL returns UNKNOWN (not TRUE). Use IS NULL or IS NOT NULL to check for NULLs explicitly.

UNION removes duplicate rows from the result set and sorts data (slower). UNION ALL keeps all rows including duplicates (faster). Both require same number and types of columns in each SELECT.

IN checks if a value exists in a returned list; executes the subquery fully. EXISTS stops checking once a match is found (more efficient). EXISTS is preferred for large subqueries as it short-circuits.

They skip/ignore NULL values in calculations. COUNT(column) ignores NULLs; COUNT(*) counts all rows. SUM(NULL) + 5 = NULL (NULL propagates). Use COALESCE() to handle NULLs in aggregates.

WHERE UPPER(name) = 'JOHN' prevents index usage on the name column because the index stores original values. Write WHERE name = 'JOHN' instead, or use functional indexes if available.