If 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.
The 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.
In statistics, the mode is the most commonly occurring value in a data set. When using SQL databases, we can easily run a SQL query to find out the mode of a given column.
Below are several options we can use to calculate the mode in SQL.
Many SQL databases have a window function called NTILE() function that divides a rowset or partition into a given number of groups (buckets). The function typically returns the bucket number of the current row within its partition.
Most 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.
The LAG() function is a window function that’s available in many SQL databases. It returns the value of a given expression from the row that lags (precedes) the current row by a given number of rows within its partition.
In other words, the LAG() function returns a value from a previous row.
Many relational database management systems (RDBMSs) have a LEAD() function that allows us to retrieve a value from a following row.
The SQL LEAD() function returns the value of a given expression from the row that leads (follows) the current row by a given number of rows within its partition.
Some of the major relational database management systems (RDBMSs) have a LIMIT clause that enables us to reduce the number of rows returned by a query.
The way it works is that we provide the number of rows we want to be returned by the query. We can also provide an offset to specify which row to start the count from.
Many RDBMSs include both a rank() and a dense_rank() function in their list of window function offerings. At first glance, these functions might appear to do the same thing. However, there’s one important difference between these functions, and you will definitely need to be aware of this difference when choosing which function to use.