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`
	Teradata	`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
	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`
	MS SQL Server	`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`
	Oracle	`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')`
	Teradata	`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`
	Spark/Hive	`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`
	Oracle	`regexp_like(name, 'string$', 'i')`
	MS SQL Server
shift string	SQLite 3
	MySQL
	Spark/Hive	`shiftleft`
	Spark/Hive	`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