What are the 3 types of SQL?

I initially thought that the multiplicity of terms used to talk about SQL as a programming language was the result of overcomplication made up by bored engineers or marketers to sell the same “product” under different names. However I must admit that this is not true: there are indeed variations in the SQL programming language.

This article has several goals. First, it presents the historical background behind the creation and expansion of SQL, which forms the basis for further developments of this programming language. This will lead us to a second part aimed at better understanding the differences between the various SQL dialects. Finally, we will put these insights into perspective to help you choose the best way you can learn and use SQL as a tool to manage databases.

A bit of history: Why do different SQL dialects exist?

To understand the genesis of SQL dialects, we must go back to the foundation of relational database management (RDBM). In 1970 Edgar Frank Codd, a British computer scientist who worked at IBM, published a paper called “A Relational Model of Data for Large Shared Data Banks”. This is when he invented the relational model for database management, which has served as the theoretical basis for relational databases and relational database management systems since then.

Extract of E.F. Codd’s research paper (source)

After this groundbreaking invention, designers chose to express the relational model in two ways: as a calculus (as Ingres did) or as an algebra (as IBM did). Algebra won the battle, and this is the method that is used nowadays. Following this, some efforts were made to create one SQL standard in the late 1980s. 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.

However, new variations kept appearing as database implementers working at different vendors needed to solve new problems or circumvent already existing problems that were not addressed in the standard. This explains why a multiple of SQL dialects made their apparition and still co-exist today.

To summarize this historical introduction, the situation as of today is the following: there exists only one SQL language, but the different Database Management Systems have extended the original SQL standard to add their own functionalities or adapt the syntax to their own way of functioning. By doing so, they gave birth to a variety of SQL dialects.

SQL dialects: What are the differences?

As you understood, SQL is one programming language that is used to extract data and process data in databases. Among others, SQL Server, Oracle, MySQL, and PostgreSQL are relational database management systems (RDBMS) that have their own SQL dialect for the reasons we previously exposed.

For novice or intermediate users, the differences can mainly be seen in the syntax, and for most of us (I would include myself here) the technical reasons behind such differences are rather obscure. However this will not prevent us from using SQL in the most relevant way!

To get a better understanding of the main differences between four SQL dialects, here is an overview of some syntax particularities of PostgreSQL, MySQL, Microsoft SQL Server, and Standard SQL as it is used in Google BigQuery.

Differences in syntax between four SQL dialects (Graph by author)

To go further, you will find a relatively exhaustive inventory of differences between various RDBMS’s on a specialized Wikipedia page entitled “Comparison of relational database management systems”.

Time to choose: Which SQL dialect should you use?

As we just discussed, PostgreSQL is the closest to standard SQL, while it is a widely used SQL dialect. This is why starting by learning PostgreSQL will give you the most flexible tool to possibly adapt to other SQL dialects later on and translate your skills to other RDBMS’s.

Another approach to making a decision may be to examine the current technical skill needs in the labor market. In its latest Developer Survey (2021), Stack Overflow ranks the most popular database technologies. Top technologies all include SQL, which reinforces the necessity to learn SQL (whatever the dialect) to begin with. In this survey, MySQL largely dominates the ranking.

When looking at trends based on question tags on Stack Overflow, MySQL also ranks first before other SQL dialects. Microsoft SQL Server ranks second, as it remains widely used at big companies.

The website DB-Engines suggests another ranking order where Oracle ranks first. To establish this list on a yearly basis, they analyzed data retrieved from search engines (Google, Bing), Google Trends, IT-related websites (Stack Overflow, DBA Stack Exchange), job search engines (Indeed, Simply Hired), social networks (LinkedIn, Twitter).

Top 3 SQL dialects according to different sources (Graph by author)

Finally, let’s be pragmatic. If you work at a company that uses a specific RDBMS, then you should learn the SQL dialect associated with it. For example, Microsoft SQL Server still has large market shares in many industries. If you are looking for a job in a given industry, you can browse job openings and look at the requested skills for database management. This may give you precious indications on which SQL dialect you should start learning for your own career goal.

Final thoughts

Mastering SQL is and remains a must in database management. Regardless of the SQL dialect you will choose to start with, the fundamentals remain the same and are a key element to work with databases.

After you learn the basics and while you progress along your career path, you may have to learn several SQL dialects. My last piece of advice would be: choose a first SQL dialect and stick to it until you gain sufficient knowledge, then you will be able to adapt to other contexts where SQL is used.

As an example, I learned MySQL at university and in online courses. But since I work as a data analyst in a startup, I have switched to standard SQL as it is used in Google BigQuery. I hope that this article will help you choose the right path in your SQL journey!

What are the three types of SQL?

What are different types of SQL?

What are the 5 types of SQL?

Which are 3 different types of SQL statements that you can execute?