How to find rows that contain, or don’t contain, a value from a list. The list may be written in the query, or stored in a table.
Category: Generic SQL
Articles about standard SQL features that will work on the most common DBMSs, and articles that include information about several DBMSs.
Find rows that have a match in another table
How to SELECT rows from table A that have, or don’t have, a match in table B. While doing this, we’ll keep performance in mind.
Find the maximum text length in a column
How to get the length of the longest text in a column. Including Db2 specific syntax.
Grouping rows by a range of values
GROUP BY allows us to group identical values to aggregate data. What is not obvious is that it can be easily used to group values that belong to the same range, as well.
Create a cross-table unique ID
Most tables are created with a unique, progressive ID. But how to create an ID whose values are unique across multiple tables? For example, if value 1 is in table A, it must not be in table B.
Find invalid emails
How to select invalid email addresses from a column. How to reject attempts to insert invalid emails into a column.
Aggregate a column data
How to get statistical data from a column in standard SQL. In particular it shows how to get the minimum, maximum, average values, and the standard deviation.
Find values (not) in a given range
How to use the special SQL syntax to select rows with values in a given range, or outside of it. How to deal with bounds in a reversed order.
Count a table rows
How to count a table rows, a table distinct rows, a column values, missing values, etc.