Filtering Data in SQL

CodingDatabases One can use the WHERE clause in a query to filter rows based on specified conditions. By utilizing the following operators, one can have more precise control over such filtering operations:

• AND/OR - combine multiple filtering conditions
• BETWEEN a AND b - filter on an inclusive range of values
• IN (x_1, x_2, ..., x_N) - filter based on a set of values
• LIKE '_%' - filter based on a pattern (arbitrary first character _ followed by an arbitrary number of arbitrary characters %)
• OFFSET M - skip the first rows in the returned data
• LIMIT N - limit the returned data to rows
• SELECT DISTINCT - return only distinct values (no repeats) from a column
• CASE - conditional statement to conditionally filter data or to create new columns via conditional aggregation

A generic query might look like

SELECT [DISTINCT] column FROM table
WHERE column1 BETWEEN a AND b
AND (
	column2 IN (x_1, x_2, ..., x_N) OR
	column3 LIKE '_%
	)
AND CASE
	WHEN condition1 THEN result_1
	WHEN condition2 THEN result_2
	ELSE result_3
END
OFFSET M
LIMIT N;

Here the CASE statement evaluates the conditions row-wise, and rows meeting the conditions are returned.

In order to filter aggregated data one must use the HAVING clause rather than the WHERE clause. The WHERE clause filters values before grouping based on individual rows while HAVING filters values after grouping based on aggregated values from groups of rows:

SELECT [DISTINCT] column1,
	[DISTINCT] column2,
	SUM([DISTINCT] column3) AS sum_col3,
	MIN(column3) AS min_col3,
	MAX(column3) AS max_col3,
	AVG([DISTINCT] column3) AS avg_col3,
	COUNT([DISTINCT] column3) AS count_col3
	CASE
		WHEN condition1 THEN result_1
		WHEN condition2 THEN result_2
		ELSE result_3
	END AS new_column
FROM table
GROUP BY column1, column2
HAVING min_col3 >= L AND max_col3 <= U;

Here the CASE statement is evaluated value-wise, i.e. sequentially. Again, one could also specify the grouping by index of the queried columns, or GROUP BY 1,2 for the above example. The HAVING clause in the query above only returns the rows where the minimum value of column3 is at least and the maximum of column3 is at most .