What Is a SQL Dialect, and Which one Should You Learn?

SQL, standard SQL, SQL Server, MySQL, PostgreSQL, Oracle, … You’d like to learn SQL, but you feel overwhelmed with keywords and you don’t know where to start. Let’s explain what each of those terms mean.

SQL Is the Language for Talking to Databases

A database is a computer program that can store and process large amounts of data. There are many different vendors of databases. PostgreSQL, MySQL, Oracle, and SQL Server are all database products by different vendors. SQL is the programming language used to talk to these databases, and each database product has its own variant of SQL. We call these variants SQL dialects.

Are SQL dialects compatible? Learning SQL and its dialects is like learning English as a foreign language. If you learn “English,” you’ll be able to talk to everyone who speaks English, but maybe you won’t understand some idioms or regional accents. On the other hand, if you learn a regional version of English and use its idioms a lot, you may have trouble communicating with someone who speaks another variant of English. If you learn “SQL,” you’ll be able to talk to most databases. If you learn a vendor-specific dialect of SQL, then some of its syntax or functions may not work in a different database.

The SQL Standard

SQL was developed in the early 1980s. At that time, each database vendor had its own version of SQL. These SQL versions varied widely and were incompatible with each other. In the late 1980s, people started working on the “SQL standard”, a version of SQL that could be understood by most databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987.

The SQL standard has been revised multiple times since the 1980s, although the core features of the standard SQL have been stable since 1992. No database vendor is fully compatible with the standard. Most SQL scripts require at least some changes before being ported to different database systems.

SQL in Big Data and NoSQL Technologies

SQL is used not only in databases these days. Other data processing technologies routinely include SQL as the language to select and process data.

As the amount of data in databases grew, the so-called NoSQL databases started gaining popularity. MongoDB, Cassandra, and Redis are among the most widely used NoSQL tools. Interestingly, most of these tools also include the capabilities for retrieving data with SQL. For that reason, some people prefer to call it “Not only SQL” rather than “NoSQL.”

Likewise, the big data technologies, which allow you to process gigabytes of data in minutes, also offer access to data with SQL. These technologies include Presto, Hive, and Spark, among others.

Which SQL Dialect Should You Select to Learn?

If you know you need to learn SQL, then you should learn the standard SQL. However, if you already know with which specific database you will be working, it’s probably best to learn its SQL dialect and just be aware that different databases may use a slightly different syntax.

At LearnSQL.com, we offer interactive SQL courses in standard SQL, PostgreSQL and Microsoft SQL Server dialects. We plan to add courses for more SQL dialects in the future. Drop us a line if you’re interested in a specific dialect that we don’t yet cover.

If you’re a beginner, we recommend starting with our “SQL Basics” course in the variant of your choice (standard SQL, PostgreSQL, SQL Server). Read the description of our SQL Basics course in detail. Our full offering of almost 30 SQL courses is described here.

Even More Keywords

You may encounter other SQL-related keywords, such as T-SQL, PL/SQL, or PL/pgSQL. Let’s quickly explain what they are.

Transact-SQL, often abbreviated “T-SQL,” is the dialect of SQL used in the Microsoft SQL Server database. Simply put, the SQL Server’s version of SQL has its own proper name. No other database has a proper name for their SQL dialect. If you need to learn T-SQL, we recommend you try our SQL Server courses.

What about PL/SQL, or PL/pgSQL? These are languages with which to write your own functions. When you write a piece of SQL code, you usually use the constructions and functions that are predefined in your database. However, all databases allow you to create your own functions or stored procedures. These are called user-defined functions (UDF) and user-defined stored procedures (USP). UDFs and USPs are usually written by IT professionals: software engineers, SQL developers, and database administrators (DBAs). The language you use to create functions and stored procedures in Oracle is called PL/SQL (a procedural language for SQL). The language in PostgreSQL is called PL/pgSQL. PostgreSQL’s version was inspired by Oracle’s version; that’s why the names are similar. The term “Transact-SQL” or “T-SQL” is often used to mean both SQL itself as well as the procedural language in SQL Server. Other databases don’t have proper names for their procedural languages.

If you’re not an IT professional, you probably don’t need to worry about learning how to write your own functions. If you plan to be an IT professional, we have a “Writing User-Defined Functions in PostgreSQL” course that teaches how to write functions in PL/pgSQL for PostgreSQL.

SQL Dialects: Summary

To sum it all up:

• SQL is the programming language you use to talk to databases and other data processing technologies.
• SQL Server, Oracle, MySQL, and PostgreSQL are all different databases that have their own slightly different SQL dialects.
• The SQL Standard is an official ANSI/ISO document that defines the syntax of SQL. No database is fully compatible with the standard.
• Transact-SQL, or T-SQL, is the name of the variant of SQL used in the Microsoft SQL Server database.
• PL/SQL is the procedural extension to SQL in the Oracle database. It is used to write your own functions and procedures in Oracle.
• PL/pgSQL is the procedural extension to SQL in the PostgreSQL database. It is used to write your own functions and procedures in PostgreSQL.