Ben Chuanlong Du's Blog

It is never too late to learn.

SQL Equivalent

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

SQL translation is a great tool that transalte any SQL statement(s) to a different dialetc using the JOOQ Parser.

SQL Variant Code
List
databases [1] 		SQLite 3 .DATABASES
MySQL SHOW DATABASES
Spark/Hive SHOW DATABASES
SHOW DATABASES LIKE "*user*"
hdfs dfs -ls /path/to/hive/warehouse
/*
You can query the Hive Metastore DB
if you have access.
*/
Teradata SHOW DATABASES
Oracle SHOW DATABASES
MS SQL Server SHOW DATABASES
Use a
databases [1] 		SQLite 3
MySQL USE database_name
Spark/Hive
Teradata USE database_name
Oracle USE database_name
MS SQL Server USE database_name
List all
tables
in the
current
database [1] 		SQLite 3 .TABLES
SELECT name FROM db.sqlite_master WHERE type='table'
MySQL SHOW TABLES
Spark/Hive SHOW TABLES
SHOW TABLES in db_name
SHOW TABLES in db_name like '*cust*'
SHOW TABLE EXTENDED in db_name like '*cust*'
Teradata SHOW TABLES
Oracle /* All tables in a database */
SELECT *
FROM dba_tables
WHERE table_schema = 'current database name'
/* The table all_tab_cols contains information
about tables and their columns */
/* List all tables owned by the current user */
select * from user_tables
/* List all tables accessible to the current user */
select * from all_tables
MS SQL Server SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_catalog = 'current database name'
Describe
a table 		SQLite 3 .SCHEMA table_name
MySQL DESCRIBE table_name
Spark/Hive DESCRIBE [EXTENDED] table_name
Teradata HELP TABLE table_name
HELP COLUMN table_name.*
Oracle DESCRIBE table_name
MS SQL Server DESCRIBE table_name
Source code
of a table 		SQLite 3
MySQL
Spark/Hive SHOW CREATE table_name
Teradata
Oracle
MS SQL Server
List all
tables
owned
by the
current
user 		SQLite 3
MySQL
Spark/Hive
Teradata
Oracle /* there is no "owner" column in user_tables,
since all tables in user_tables are owned by the current user
*/
SELECT *
FROM user_tables
SELECT *
FROM all_tables
WHERE owner = "current_user_name"
SELECT *
FROM dba_tables
WHERE owner = "curent_user_name"
MS SQL Server
List all
tables
accessible
by the
current
user 		SQLite 3
MySQL
Spark/Hive
Teradata
Oracle SELECT *
FROM all_tables
MS SQL Server
List all
tables
in the
system 		SQLite 3
MySQL
Spark/Hive
Teradata
Oracle SELECT * FROM dba_tables
MS SQL Server
Create
a
table 		SQLite 3 CREATE TABLE IF NOT EXISTS queries (
    query Text NOT NULL PRIMARY KEY,
    timestamp Real NOT NULL,
    data Blob NOT NULL
)
MySQL CREATE TABLE IF NOT EXISTS queries (
    query VarChar NOT NULL PRIMARY KEY,
    timestamp Decimal(18, 3) NOT NULL,
    data Blob NOT NULL
)
Spark/Hive CREATE TABLE IF NOT EXISTS queries (
    query String NOT NULL,
    timestamp Double NOT NULL,
    data Binary NOT NULL
)
Teradata CREATE TABLE IF NOT EXISTS queries (
    query VarChar NOT NULL PRIMARY KEY,
    timestamp Number NOT NULL,
    data Blob NOT NULL
)
Oracle CREATE TABLE IF NOT EXISTS queries (
    query Text NOT NULL PRIMARY KEY,
    timestamp Decimal(18, 3) NOT NULL,
    data Blob NOT NULL
)
MS SQL Server CREATE TABLE IF NOT EXISTS queries (
    query VarChar NOT NULL PRIMARY KEY,
    timestamp Decimal(18, 3) NOT NULL,
    data Blob NOT NULL
)
Drop a
table
if exists 		SQLite 3
MySQL
Spark/Hive
Teradata DROP TABEL IF EXISTS table_name
Oracle IF object_id(table_name) IS NOT NULL THEN
DROP TABLE table_name
MS SQL Server
Limit
number
of
returned
rows 		SQLite 3 SELECT *
FROM table
LIMIT 5
MySQL SELECT *
FROM table_name
LIMIT 5
Spark/Hive SELECT *
FROM table_name
LIMIT 5
Teradata SELECT TOP 5 *
FROM table
Oracle SELECT *
FROM table
LIMIT 5
MS SQL Server SELECT TOP 5 *
FROM table
SELECT TOP 50 PERCENT *
FROM table
Randomly
sample
100 rows 		SQLite 3 SELECT *
FROM table
ORDER BY random()
LIMIT 100
MySQL
Spark/Hive SELECT *
FROM table
TABLESAMPLE (1 PERCENT)
LIMIT 100
SELECT *
FROM table
ORDER BY random()
LIMIT 100
/* NOTE: the following does NOT work!!!
It is equivalent to `LIMIT 100` */
SELECT * FROM table
TABLESAMPLE (100 ROWS)
Teradata SELECT *
FROM table
SAMPLE 100
Oracle
MS SQL Server
Randomly sample
rows with
acceptance
ratio 0.1 		SQLite 3 /*
Note that `random()` generates a pseudo-random integer
between -9223372036854775808 and +9223372036854775807.
*/
SELECT *
FROM table
WHERE random() % 10 = 0
MySQL
Spark/Hive SELECT * FROM table
TABLESAMPLE (10 PERCENT)
Teradata SELECT *
FROM table
SAMPLE 0.1
Oracle
MS SQL Server
Randomly
sample
buckets 		SQLite 3
MySQL
Spark/Hive SELECT * FROM table
TABLESAMPLE (BUCKET 4 out of 10)
Teradata
Oracle
MS SQL Server
Insert
multiple
rows in
one
statement 		SQLite 3
MySQL
Spark/Hive INSERT INTO tablel_name PARTITION (
    par_col_1 = pv1,
    par_col_2 = pv2
) VALUES (
    v11, v12, ...
), (
    v21, v22, ...
)
Teradata INSERT INTO table_name (
    first_name,
    last_name
) VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith')
Oracle INSERT INTO pager (
    pag_id,
    pag_parent,
    pag_name,
    pag_active
)
SELECT 8000, 0, 'Multi 8000', 1 FROM dual
UNION ALL
SELECT 8001, 0, 'Multi 8001', 1 FROM dual
INSERT ALL
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
MS SQL Server
Update SQLite 3
MySQL
Spark - not supported as of Spark 2.4.5
- Spark SQL 3.0.0 has update/delete APIs but not implemented
- Update/delete is feasible using Detal Lake
Hive
Teradata
Oracle
MS SQL Server
Insert
or
Replace 		SQLite 3 REPLACE INTO phonebook (
    name, phonenumber, validdate
) VALUES (
    'Alice', '704-555-1212', '2018-05-08'
)
MySQL
Spark - not supported as of Spark 2.4.5
- Spark SQL 3.0.0 has update/delete APIs but not implemented
- Update/delete is feasible using Detal Lake
Hive
Teradata
Oracle
MS SQL Server
Upsert SQLite 3 INSERT INTO phonebook (
    name, phonenumber, validDate
) VALUES (
    'Alice', '704-555-1212', '2018-05-08'
) ON CONFLICT (name) DO UPDATE
SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
WHERE
    excluded.validDate > phonebook.validDate
MySQL
Spark - not supported as of Spark 2.4.5
- Spark SQL 3.0.0 has update/delete APIs but not implemented
- Update/delete is feasible using Detal Lake
Hive
Teradata
Oracle
MS SQL Server
Delete SQLite 3
MySQL
Spark - not supported as of Spark 2.4.5
- Spark SQL 3.0.0 has update/delete APIs but not implemented
- Update/delete is feasible using Detal Lake
Hive
Teradata
Oracle
MS SQL Server
Insert SQLite 3
MySQL
Spark/Hive
Teradata INSERT INTO some_table
SELECT * FROM another_table
Oracle
MS SQL Server
Refresh
Table
Cache 		SQLite 3
MySQL
Spark/Hive REFRESH TABLE table_name
Teradata
Oracle
MS SQL Server
Concatenate
Strings 		SQLite 3
MySQL
Spark/Hive SELECT concat('Spark', 'SQL')
FROM table
Teradata
Oracle
MS SQL Server
Substring SQLite 3
MySQL
Spark/Hive /* substr and substring are equivalent in Spark/Hive SQL */
SELECT
    substr('Spark SQL', 5, 1) -- resulting 'k'
FROM
    table
left
right
Teradata substr
Oracle
MS SQL Server
trim SQLite 3
MySQL
Spark/Hive ltrim/rtrim/trim
Teradata trim
Oracle
MS SQL Server
substitute SQLite 3
MySQL
Spark/Hive replace/translate/regexp_replace
Teradata oreplace, otranslate
Oracle
MS SQL Server
length of string SQLite 3
MySQL
Spark/Hive
Teradata char_length
Oracle
MS SQL Server
Index of substring SQLite 3
MySQL
Spark/Hive substring_index
Teradata position('de' IN 'abcdefg')
regexp_instr('abc', 'a')
-- 1-base index
Oracle
MS SQL Server
upper case SQLite 3
MySQL
Spark/Hive ucase/upper
Teradata
Oracle
MS SQL Server
decode base64 SQLite 3
MySQL
Spark/Hive unbase64
Teradata
Oracle
MS SQL Server
decode hex SQLite 3
MySQL
Spark/Hive unhex
Teradata
Oracle
MS SQL Server
generate an uuid SQLite 3
MySQL
Spark/Hive uuid
Teradata
Oracle
MS SQL Server
reverse a string SQLite 3
MySQL
Spark/Hive reverse
Teradata
Oracle
MS SQL Server
string matching SQLite 3
MySQL
Spark/Hive /* like is Case sensitive */
like
rlike
Teradata /* Like is case-insensitive by default.
You can specify the keyword CaseSpecific
to make it case-sensitive. */
SELECT empname
FROM tbl_emp
WHERE empname (CaseSpecific) like '%JO%’
Oracle /*like is case sensitive*/
like
regexp_like(name, 'string$', 'i')
MS SQL Server
shift string SQLite 3
MySQL
Spark/Hive shiftleft
shiftright
Teradata
Oracle
MS SQL Server
ocurrence of char SQLite 3
MySQL
Spark/Hive
Teradata char_length('Teradata is Relational Database') - char_length(Oreplace('Teradata is Relational Database', 'a', ''))
Oracle
MS SQL Server
Cast to date SQLite 3
MySQL
Spark/Hive SELECT
    to_date("2000-01-01") AS date
FROM
    table
Teradata cast(str_col as date format 'YYYY-MM-DD')
Oracle
MS SQL Server
Cast to timestamp SQLite 3
MySQL
Spark/Hive SELECT
    to_timestamp("2000-01-01") AS date
FROM
    table
SELECT
    to_timestamp("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Cast to UTC timestamp SQLite 3
MySQL
Spark/Hive SELECT
    to_utc_timestamp("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Cast to Unix timestamp SQLite 3
MySQL
Spark/Hive SELECT
    to_unix_timestamp("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract year SQLite 3
MySQL
Spark/Hive SELECT
    year("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract quarter SQLite 3
MySQL
Spark/Hive SELECT
    quarter("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract month SQLite 3
MySQL
Spark/Hive SELECT
    montofyearh("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract day SQLite 3
MySQL
Spark/Hive SELECT
    day("2000-01-01") AS date
FROM
    table
SELECT
    dayofmonth("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract minute SQLite 3
MySQL
Spark/Hive SELECT
    minute("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Extract second SQLite 3
MySQL
Spark/Hive SELECT
    second("2000-01-01") AS date
FROM
    table
Teradata
Oracle
MS SQL Server
Add days to a date SQLite 3
MySQL
Spark/Hive SELECT
    date_add(current_date, 3) AS date3
FROM
    table
/* The + operator for date and int
is support since Spark 3 */
SELECT
    current_date + 3 AS date3
FROM
    table
Teradata
Oracle
MS SQL Server
Add months to a date SQLite 3
MySQL
Spark/Hive SELECT
    add_months(current_date, 3) AS date3
FROM
    table
Teradata
Oracle
MS SQL Server
Subtract days from a date SQLite 3
MySQL
Spark/Hive SELECT
    date_sub(current_date, 3) AS date3
FROM
    table
/* The - operator for date and int
is supported since Spark 3 */
SELECT
    current_date - 3 AS date3
FROM
    table
Teradata
Oracle
MS SQL Server
Diff between two dates SQLite 3
MySQL
Spark/Hive SELECT
    datediff(date1, date2) AS diff
FROM
    table
Teradata
Oracle
MS SQL Server
truncate date SQLite 3
MySQL
Spark/Hive SELECT date_trunc('2015-03-05T09:32:05.359', 'YEAR')
-- 2015-01-01T00:00:00
SELECT date_trunc('2015-03-05T09:32:05.359', 'MM')
-- 2015-03-01T00:00:00
SELECT date_trunc('2015-03-05T09:32:05.359', 'DD')
-- 2015-03-05T00:00:00
SELECT date_trunc('2015-03-05T09:32:05.359', 'HOUR')
-- 2015-03-05T09:00:00
SELECT trunc('2009-02-12', 'MM')
-- 2009-02-01
SELECT trunc('2015-10-27', 'YEAR')
-- 2015-01-01
Teradata
Oracle
MS SQL Server
sth SQLite 3
MySQL
Spark/Hive
Teradata
Oracle
MS SQL Server

[1] The terminology (database, schema or namespace) might be different for differennt databases.

A [NOT] LIKE B¶ NULL if A or B is NULL, TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character. The character in B matches any character in A (similar to . in posix regular expressions) while the % character in B matches an arbitrary number of characters in A (similar to .* in posix regular expressions). For example, 'foobar' like 'foo' evaluates to FALSE whereas 'foobar' like 'foo ' evaluates to TRUE and so does 'foobar' like 'foo%'.

A RLIKE B NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. For example, 'foobar' RLIKE 'foo' evaluates to TRUE and so does 'foobar' RLIKE '^f.*r$'.

A REGEXP B Same as RLIKE.

A || B Concatenate A and B (as of Hive 2.2.0).

References

Ten SQL Tricks that You Didn’t Think Were Possible (Lukas Eder)

Column Alias in SQL

http://www.legendu.net/misc/blog/Use-tablesample-in-sql

http://www.legendu.net/misc/blog/spark-dataframe-func-date

http://www.legendu.net/misc/blog/spark-dataframe-func-string

https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringOperators

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-RelationalOperators

Comments