22  SQL dialects

Even though SQL are set of standards, different database engines and companies may implement these standards differently.

An example of these differences is basic command SELECT. If select works only with from or without from.

From modern-sql website select without from:

Let’s get that straight from the beginning: select without from is not standard conforming SQL. Full stop.

But select without from works in SQL server and sqlite. See below example queries

sqlite

SELECT DATE('now');

Sql Server

SELECT GETDATE()  

But in oracle, to get same information, you need to use dummy table called dual. You cannot run select without from in Oracle database.

Oracle

SELECT CURRENT_DATE FROM dual;

They may also add extensions to SQL standard like procedural language elements.

22.1 SQL dialect sqlite

Even though, sqlite has no formal name for its extensions, commands like .schema, .table, .explain allows us to manage sqlite databases and could be described as sqlite dialect.

22.2 SQL dialect Transact-SQL

Transact-SQL is the procedural extensions that Microsoft and Sybase have added to the SQL standard.

From their website:

T-SQL is central to using Microsoft SQL products and services. All tools and applications that communicate with a SQL Server database do so by sending T-SQL commands.

22.3 SQL Dialect PL/SQL

PL/SQL by Oracle is procedural extensions to SQL language by Oracle. See their definition below:

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.