Question 1

What does a LEFT JOIN do in SQL?

Accepted Answer

A LEFT JOIN returns all rows from the left (first) table, plus any matching rows from the right table. If there is no match in the right table, the result contains NULL for all right-table columns. Use LEFT JOIN when you want to keep every row from one table even if the other table has no match.

Question 2

What is the difference between WHERE and HAVING in SQL?

Accepted Answer

WHERE filters individual rows before they are grouped. HAVING filters groups of rows after a GROUP BY. Use WHERE to filter raw data rows, and HAVING to filter the results of aggregate functions like COUNT(), SUM(), or AVG().

Question 3

What does GROUP BY do in SQL?

Accepted Answer

GROUP BY collects rows that share the same value in the specified column(s) into a single group. It is used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) so you get one result row per group rather than one row per raw data row.

Question 4

What does SELECT DISTINCT do?

Accepted Answer

SELECT DISTINCT removes duplicate rows from the result. If the same combination of column values appears in multiple rows, DISTINCT returns it only once.

Question 5

What is a subquery in SQL?

Accepted Answer

A subquery is a SELECT statement nested inside another query. It can appear in the SELECT list, FROM clause, or WHERE clause. A subquery in FROM is called a derived table; a subquery in WHERE with EXISTS or IN is a correlated or uncorrelated subquery.

Question 6

What is a CTE in SQL?

Accepted Answer

A CTE (Common Table Expression) is defined with a WITH clause before the main SELECT. It gives a name to a subquery so you can reference it like a table in the main query. CTEs make complex queries easier to read and can be referenced multiple times.

WITH	define named subqueries (CTEs) used below
SELECT	choose which columns to return
FROM	which table to read from
JOIN	merge rows from another table
WHERE	filter rows before grouping
GROUP BY	group rows with equal values together
HAVING	filter groups after aggregation
ORDER BY	sort the result rows
LIMIT	cap the number of rows returned

INNER JOIN	only rows that match in both tables
LEFT JOIN	all left rows + matched right (null if none)
RIGHT JOIN	all right rows + matched left (null if none)
FULL JOIN	all rows from both tables (null where no match)
CROSS JOIN	every combination of left × right rows

COUNT(*)	count all rows in the group
COUNT(col)	count non-null values of col
SUM(col)	add up all values
AVG(col)	arithmetic mean of values
MAX(col)	largest value in the group
MIN(col)	smallest value in the group

= / != / <>	equals / not equals
> / < / >= / <=	numeric comparisons
IS NULL	value is missing / not set
IN (...)	value appears in a list
LIKE 'x%'	text matches a pattern (% = anything)
BETWEEN a AND b	value is within range [a, b]

SQL Explainer

SQL clause reference

Clauses, in order

JOIN types

Aggregate functions

Common operators