SQL flashcards, generated for you.

SQL = Structured Query Language. It is used to query, insert, update, and delete data in relational databases.

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. SELECT is evaluated before ORDER BY, which is why you can't use column aliases in WHERE.

Common gotcha: NULL values don't match in comparisons. Use IS NULL or IS NOT NULL instead of = NULL or != NULL, since NULL compared to anything (including NULL) returns UNKNOWN, not TRUE.

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right table; unmatched right rows contain NULL.

In standard SQL (MySQL strict mode, PostgreSQL), all non-aggregated columns in SELECT must appear in GROUP BY. Otherwise, the result is ambiguous—which row's value should be returned?

WHERE filters rows before grouping; HAVING filters groups after aggregation. HAVING can reference aggregate functions; WHERE cannot.

COUNT(*) counts all rows including those with NULL values. COUNT(column_name) counts only non-NULL values in that column.

DISTINCT applies to the entire row combination, not individual columns. SELECT DISTINCT col1, col2 removes rows where both col1 AND col2 are identical; it doesn't deduplicate col1 independently.

UNION removes duplicate rows and sorts results (slower); UNION ALL keeps all rows including duplicates (faster). Use UNION ALL unless you explicitly need deduplication.

A scalar subquery (in SELECT clause) must return exactly one row and one column. If it returns zero or multiple rows, it errors. Use LIMIT 1 or aggregate functions to ensure one value, or join instead for better performance.