Counting the rows that match a WHERE condition can be slow, and may return a very high and precise number that is useful to no one. Let’s see how to just check if a query returns more than N rows.
Tag: level-intermediate
SQL Server extended properties (comments)
Most DBMSs allow to read and write comments about tables, columns and other database objects. SQL Server has a more complex feature called Extended Properties.
MariaDB/MySQL: Comments on database objects
MariaDB and MySQL support a non-standard syntax to read and write comments for database objects.
Comments on database objects
Why and how to write comments on database, tables, and other objects on most DBMSs. How to read comments.
Make searches at the end of a text fast
Searching for a string at the end of a text column is typically slow, because no index is used. How to build a suitable index and make LIKE '%end' fast.
Db2: Copying a table
How to create a copy of a table. The copy can contain data, a selective portion of the data, or it can be empty.
Aggregate data that match a condition
How to aggregate data that match a given condition. How to perform different aggregations of data that match different conditions, in a single query. Includes convenient SQLite shortcuts.
Get distinct values that are stored in multiple columns
Getting a list of unique values from a column is easy. But if the same type of values are written on multiple columns and we want to eliminate duplicates, this requires a little trick.
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.
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.