SQL Reference

sql-reference.md

SQL Reference

• For basics on Querying I recommend Become a SELECT star! zine
  • Here are some pages that are public and free
• Databricks Functions
  • Databricks DateTime Functions
  • Databricks DateTime Patterns

Time Formats

Description	ORACLE SQL	Databricks
Date	date '2020-01-01'	'2020-01-01'
Timestamp	timestamp '2020-01-01 00:00:00'	'2020-01-01 00:00:00'
To Day	trunc(<column>)	to_date(<column>)
To Hour	trunc(<column>, 'HH24')	date_trunc(<column>, 'HOUR')
To Minute	trunc(<column>, 'MI')	date_trunc(<column>, 'MINUTE')
To Second	trunc(<column>, 'SS')	date_trunc(<column>, 'SECOND')
Extract Hour	extract(hour from <column>)	extract(hour from <column>)

Performance

• Parallel /*+ parallel(8) */
• Use limiting WHERE clauses
  • >= time
• Use WITH instead of JOINs/Sub Queries
• Use EXISTS instead of IN
• Use >= and <= instead of BETWEEN
• Use Explain to get cost of query

Potential Issues

• Dates are not equal
  • The timestamp part might not be equal
• NULLs
  • Use COALESCE for default
  • Not included in NOT IN
  • Make sure to use IS and not ==
• Mixing ANDs and ORs
  • Use parenthesis to clarify order
• Read and write to same row
  • Creates deadlock
  • Don't subquery the same table being updated
  • Refer to UPDATE's FROM
• Parsing JSON
  • Use JSON_VALUE(<column>, '$.<key>')
  • Databricks get_json_object(<column>, '$.<key>')
• Parsing XML
  • Use EXTRACTVALUE(<column>,'/<entity>/@<attribute>')

Oracle Alternative To Seconds to_char(<column>, 'YYYY-MM-DD HH24:MI:SS')

Examples

With

WITH tempname (arg) as
    (sub table)
    query using sub table

Exists

UPDATE
    /*+ parallel(8) */
    table t
SET
    t.column = 'val'
WHERE
    EXISTS (
    SELECT
        1
    FROM
       table2 t2
    WHERE
        t1.id = t2.id
        AND t2.status IN ('S', 'D')
    );