Sep 12, 2024 by Robert Gravelle

In the mid nineties, Sun Microsystems came out with a language that you could "write once, [and] run everywhere." That language was, of course, Java. And, while it did go on to be one of the most popular programming languages until this day, their slogan turned out to be just a little optimistic. The course of the Java language does bear some strong similarities to that of SQL. It too can be ported from one database to another, or even across operating systems, with little or no modification. At least, that's the dream. In the real world, production-level code tends to require some tweaking in order to work in a new environment. This blog will outline some of the reasons that SQL syntax may differ across different database vendors.

The ANSI SQL Specification

ANSI, which stands for American National Standards Institute, defines the basic set of syntax rules and commands that are to be used to interact with relational databases. However, much like browser implementations of HTML, CSS, and ECMAScript, most database implementations of SQL are imperfect and/or incomplete. ANSI SQL allows for some flexibility in the level of conformance, so there is no strict vendor requirement to implement the full specification. But even at the basic, lowest level, all vendors diverge at least a little bit.

Beyond that, there are non-standard extensions, which all vendors support in one form or another. Even something as simple as indexes are non-standard. The ANSI SQL specification says nothing about indexes, so every vendor's implementation of indexing is a supplement to the standard. That opens the door for vendors to come up with whatever syntax they deem fit or most advantageous to their brand. The result: a variety of SQL dialects, which are largely the same, but with some distinctions.

Writing Versatile SQL

If you want SQL code that will work across all database types, you should stick to standard SQL statements like SELECT, WHERE, GROUP BY, ORDER BY, etc. Aggregate functions like SUM(), AVG(), MIN(), and MAX() will also be understood by all popular database types, including SQL Server, MySQL, PostgreSQL, SQLite, and Oracle. Here's a query that should work with any database:

Select    
    c.customer_id,
    c.customer_name,
    SUM(p.amount) AS total_sales
FROM customers AS c
    LEFT JOIN purchases AS p
    ON c.customers_id = p.customer_id
WHERE
    c.customer_location = 'Canada'
GROUP BY
    c.customer_name ASC;

Learning SQL

If you're just starting out in database administration and/or development, you should concentrate on SQL that will apply to the most database types as possible. You should also work with a database that is highly ANSI SQL compliant and popular, such as MySQL. It has consistently been the most popular database for the past few dacades. It's also highly compliant, making it an excellent learning tool. There are many articles on it and most SQL samples were developed and run on MySQL. Microsoft SQL Server comes in at a close second. However, it uses Microsoft's dialect of SQL, called T-SQL. Having the most dissimilar SQL to other platforms makes SQL Server a less-than-ideal starter database. You're probably better off choosing PostgreSQL or SQLite, which are also quite popular and ANSI compliant. SQLite is particularly attractive to novices because of it's small size and portability.

Here are just some of the differences that you're likely to find between databases:

Case Sensitivity

Consider the clause WHERE name = 'Rob' Or WHERE name = 'rob':

MySQL	PostgreSQL	SQLite	SQL Server
Equivalent	Not Equivalent	Not Equivalent	Not Equivalent

Use of Quotation Marks

Some databases only support single quotes, while others allow both single and double quotes:

MySQL	PostgreSQL	SQLite	SQL Server
Both	Single Only	Both	Single Only

Column and Table Aliases

MySQL, PostgreSQL, and SQLite all use the "AS" keyword to denote aliases, i.e., SELECT SUM(score) AS avg_score, while SQL Server employs the equals sign, i.e., SELECT SUM(score) = avg_score.

Date/Time Functions

Each database implements its own date and time functions:

MySQL	PostgreSQL	SQLite	SQL Server
CURDATE() CURTIME() EXTRACT()	CURRENT_DATE() CURRENT_TIME() EXTRACT()	DATE('now') strftime()	GETDATE() DATEPART()

Navicat Premium: the Universal Tool

Navicat Premium is the tool of choice for working with a variety of database types. Not only can it connect to multiple databases simultaneously, but its Code Snippets feature makes writing queries against your preferred database type easier than ever before. The Code Snippets feature allows you to insert reusable code into your SQL statements when working in the SQL Editor. Besides gaining access to a collection of built-in snippets for common control flow statements and functions, you can also define your own.

You can download Navicat 17 for a 14-day fully functional FREE trial. It's available for Windows, macOS, and Linux operating systems.