Introduction
In this post, I summarised some SQL tips that I learnt from my not-so-long usage.
UNION vs. UNION ALL
UNION: will remove duplicate rowsUNION ALL: will keep duplicate rows
COALESCE/NVL
COALESCE/NVL mostly have the same functionality, which return the first non-null value. For example:
select coalesce(NULL, 'hello', NULL, 'world')
from table
will return 'hello'.
RANK, DENSE_RANK & ROW_NUMBER
For example, we have a table:
| x |
|---|
| a |
| a |
| b |
| b |
| c |
select
x,
rank() over(order by x),
dense_rank() over(order by x),
row_number(order by x) over()
from table
will product a table
| x | rank | dense_rank | row_number |
|---|---|---|---|
| a | 1 | 1 | 1 |
| a | 1 | 1 | 2 |
| b | 3 | 2 | 3 |
| b | 3 | 2 | 4 |
| c | 5 | 3 | 5 |