1.
WHERE 1=1
When you code has different WHERE conditions,
having a WHERE 1=1 simplifies the logic.
This is because 1=1 is always true and doesn't affect
the query's actual result, but it allows you to more
easily append additional conditions without needing
special handling for the first condition.
It improves the overall readability of the code as well.
2. QUALIFY
It can be used to filter the results of a query based on
the result of a window function.
You don’t need nested queries making the code a lot
easier to read.
It is similar to the HAVING, but instead of filtering
after an aggregation, QUALIFY filters after the
application of window functions.
3. ROW_NUMBER
Incredibly useful when it comes to cleaning data.
ROW_NUMBER ( ) can be used to identifying and
removing duplicates, and detecting gaps in data.
It can be used to select a SINGLE row based on
conditions such as latest record, highest/lowest value
etc.
4. EXCLUDE <COL>
This not standard in SQL but is a feature found in
some SQL dialects, such as BigQuery.
It allows you to easily select all columns from a table
except one or more specified columns.
This improves readability and reduces repetitive code.
5. EXISTS
Employed when you want to check for the existence of
records in a related table or subquery. Helps when
you do your EDA.
It returns a TRUE if the subquery returns at least one
row.
It is more performant than using IN or JOIN.
6. COALESCE
This function handles NULL values gracefully.
COALESCE allows you to provide a fallback value
when encountering NULL.
Rather than using a complex CASE statement or
multiple IFNULL/ISNULL functions, COALESCE
provides a cleaner syntax.
You can use COALESCE to choose the first non-
NULL value across multiple columns.
7. TEMP TABLES
Temp tables allow you to break the query into smaller,
more manageable parts.
Trying to fit everything into a single, massive nested
statement with multiple WITH statements can make
your query too complex.
This way you can also avoid repeated calculations &
re-use queries.
Also it helps you understand, debug, and optimize
each part of the query independently.
8. SYSCAT / SYSINFO
Helps you obtain metadata on the underlying
database platform that you are using.
Querying syscat or sysinfo to find out what schemas,
tables, columns, etc are available.
For example, you can query SYS.COLUMNS to get
details about all the columns in a particular table.
SYS.KEYS and SYS.CONSTRAINTS can be used to
get info about primary keys, foreign keys, and other
constraints applied to tables.
9. LAG / LEAD
Extremely useful for performing operations that
require accessing data from previous or subsequent
rows.
If you are building a KPI dashboard and want to
calculate month-over-month or year-over-year,
then this syntax makes the calculation a lot easier.
