The SELECT statement is the most fundamental SQL command used to retrieve data from one or more database tables. It allows you to specify which columns to return, filter rows with conditions, sort results, and combine data from multiple tables. Basic syntax: SELECT column1, column2 FROM table_name WHERE condition;

SQL supports several JOIN types: INNER JOIN (returns only matching rows from both tables), LEFT JOIN (returns all rows from the left table + matches from right), RIGHT JOIN (all rows from right + matches from left), FULL OUTER JOIN (all rows from both tables with NULLs where no match), and CROSS JOIN (Cartesian product). INNER JOIN and LEFT JOIN are the most commonly used in real-world applications.

GROUP BY groups rows that share the same values in specified columns, allowing you to apply aggregate functions (COUNT, SUM, AVG, MAX, MIN) to each group. For example, SELECT department, AVG(salary) FROM employees GROUP BY department; calculates the average salary per department. Every non-aggregated column in the SELECT clause must appear in the GROUP BY clause.

WHERE filters individual rows before aggregation (GROUP BY), while HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE; use HAVING instead. Example: SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 50000;

A subquery (or nested query) is a SQL query embedded inside another query. It can appear in SELECT, FROM, or WHERE clauses. Subqueries in WHERE often use IN, EXISTS, or comparison operators. Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Correlated subqueries reference the outer query and execute once per outer row.

Window functions (like ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER) perform calculations across a set of rows related to the current row without collapsing them into groups. They use the OVER() clause with optional PARTITION BY and ORDER BY. Example: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

Key optimization strategies: (1) Use indexes on columns used in WHERE, JOIN, and ORDER BY; (2) Avoid SELECT * — select only needed columns; (3) Use EXPLAIN to analyze query execution plans; (4) Avoid functions on indexed columns in WHERE; (5) Use proper JOIN types; (6) Consider query caching; (7) Break complex queries into smaller parts when beneficial; (8) Regularly update table statistics.

UNION combines results from multiple SELECT statements and removes duplicate rows (implicit DISTINCT), which adds overhead. UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL when you don't need deduplication or are certain there are no duplicates. Both require the same number and compatible data types of columns in each SELECT.

A CTE is a temporary named result set defined using the WITH clause, existing only for the duration of the query. CTEs improve readability and can be referenced multiple times. Syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name; Recursive CTEs are powerful for hierarchical data like org charts or category trees.

NULL represents missing/unknown data. Use IS NULL or IS NOT NULL (not = or !=). Functions like COALESCE() return the first non-NULL value, IFNULL() (MySQL) or NVL() (Oracle) provide defaults. Aggregate functions ignore NULLs. Be cautious: NULL in comparisons yields UNKNOWN, which can affect JOIN and WHERE results unexpectedly.

Indexes are data structures (B-trees, hash tables) that speed up data retrieval. Like a book's index, they let the database find rows without scanning the entire table. Create indexes on columns frequently used in WHERE, JOIN, and ORDER BY. However, indexes slow down INSERT/UPDATE/DELETE operations and consume storage, so balance is key. Use composite indexes for multi-column queries.

Aggregate functions perform calculations on a set of rows and return a single value. Common ones: COUNT() (row count), SUM() (total), AVG() (average), MAX() (maximum), MIN() (minimum). They're often used with GROUP BY. COUNT(*) counts all rows including NULLs; COUNT(column) counts non-NULL values only.