The SQL COUNT() function is a handy tool for telling us how many rows would be returned in a query. We can pass a column name to the function or we can pass the asterisk (*) wildcard to indicate all columns.
If a column contains NULL values, we could get different results, depending on whether we use the column name or the asterisk (*).
The SQL UNION operator concatenates the results of two queries into a single result set. By default it returns distinct rows (i.e. it removes any redundant duplicate rows from the result set). But we can also use UNION ALL to return non-distinct rows (i.e. retain duplicates).
When it comes to NULL values, it’s pretty straight forward. SQL treats two NULL values as non distinct values. In other words, they’re duplicates.
Continue readingIf you’ve ever used the UNION clause in a SQL query, chances are you used UNION ALL or just UNION by itself. But some RDBMSs also accept a UNION DISTINCT option.
The UNION DISTINCT option is basically the equivalent of UNION by itself. That is, it removes redundant duplicate rows.
When writing SQL queries, we’ll often use a WHERE clause or HAVING clause to narrow the results down to just those rows that we’re interested in.
But sometimes we might want to reduce the number of rows returned without adding extra filtering criteria. Sometimes we might just want to see a handful of rows, without hundreds, thousands or even millions of rows being returned.
Continue readingThe SQL DISTINCT clause allows us to remove redundant duplicate rows from our query results. For example if two rows contain the same values, then only one of them is returned.
But how does DISTINCT handle NULL values?
Let’s find out.
Continue readingSuppose you’re trying to query column, but you need to set a minimum value to be returned, even if the column contains values that are less than that minimum. For example, you want a minimum value of 50 to be returned, even if the column contains values that are less than 50.
We can use the GREATEST() function to build such a query.
Most major RDBMSs support the DISTINCT clause, which allows us to get unique – or “distinct” – rows from our SQL queries. But PostgreSQL’s implementation of this clause has an extra option that most other RDBMSs don’t include.
PostgreSQL allows us to include the ON() option when using DISTINCT. This enables us to specify exactly which columns should be evaluated by the DISTINCT modifier, while simultaneously allowing us to return columns that aren’t evaluated.
Many RDBMSs have a LIMIT clause that allows us to limit the rows returned by a query to a specified number. SQL Server doesn’t have a LIMIT clause but it does have a TOP() function that allows us to do the same thing.
But Oracle Database has neither.
Fortunately, Oracle does have a number of options that allow us to get the same result.
Continue readingMost of the major relational database management systems (RDBMSs) allow for a DISTINCT clause to be included in our SQL queries.
We use the DISTINCT keyword to return only unique rows. It eliminates duplicates from the results. If we have two or more rows with exactly the same data, we’ll only see one row in the results.
In Oracle Database, we can use the OFFSET clause to make a SELECT statement skip a certain number of rows in its result set.
The OFFSET clause is defined as part of the FETCH row limiting clause, which allows us to limit the number of rows returned by a query.
We can therefore use both OFFSET and FETCH to limit the output to just the specified number or percentage of rows, at a specified offset.