Is Sql syntax per DataBase engine (jdbc)

Assume that there is application that must interact with a DB of more than on type (i.e. MySql, MSSQL etc). To do this. there is a layer between the application and the DB (whatever type the DB is) that is responsible to handle开发者_如何学Python the interaction with the DB. This layer has some parts that has, sql statements(hardcoded) of the type:

`"SELECT "+ obj.getName()+ "FROM TABLE_X WHERE NAME=?"` etc (trivial sample sql).  

My question is, is the SQL syntax same in all Database engines (MySql, MSSQL etc)? Which part differ?

I am asking this because I am partially working in an old project that does this and as far as I know it works.The sql statements are much more complex than the example including joins and updates.

I always thought though, that SQL syntax varies per database type.

The full supported database syntax varies between database servers.

However, a VAST majority support one of the two basic subset standards: ANSI SQL (Main SQL standard) and/or Transact SQL (MS SQL/Sybase "enhanced" version of ANSI SQL).

• Really basic SQL (SELECT/INSERT/UPDATE queries) are pretty much uniform.

• Features that are NOT directly SQL statement related might likely differ to a much greater degree (stored procedure calls, control flow statements, DDL syntax).

• Plus, every DB engine adds their own extra capabilities on top of vanilla ANSI SQL, from complete supersets (T-SQL, PLSQL) to minor additional abilities/keywords here and there (see LIMIT/TOP example in a different answer).

You should obviously stick to vanilla ANSI SQL if you want to make your SQL code as portable as possible, but which specific features beyond that you can use depends heavily on which engines you want to limit yourself to.

Please note that the prepared statements (the "?" part of your example) have NOTHING to do with SQL syntax and are instead a property of the database connectivity APIs/libraries; as such they are as portable as your language's libraries (as well as individual database server's API they use) allow. The portability is obviously not as universal as that of ANSI SQL syntax but they are fairly widely supported.

The basics - SELECT, INSERT, UPDATE, DELETE are consistent between vendors. That includes aggregate functions (MIN, MAX, COUNT, AVG) and GROUP BY, HAVING and ORDER BY clauses. Also, subqueries and UNION.

Beyond that, things get really sketchy.
String and date comparison/manipulation has some similarities, but generally very different. ANSI helps, but the reality is no vendor implements the entire ANSI spec for a given year. For example, SQL Server added analytic functions in v2005 but it's a limited subset (ROW_NUMBER, RANK, DENSE_RANK and NTILE) vs Oracle 9i+ support for LEAD and LAG besides the already mentioned. PostgreSQL didn't support analytics until 8.4, MySQL still doesn't have any analytic support. The other reality people gloss over is that the ANSI version might not perform as well as when using native syntax -- COALESCE on SQL Server is a good example, because it's slower/less efficient. The list is just too big to cover all the differences -- containing to a specific data type helps, but then you need to know versions of the respective vendors...

Some databases named their extensions of the SQL standard/syntax. Sybase and SQL Server's is Transact-SQL (TSQL), because SQL Server was originally built off Sybase. Oracle has PLSQL, and PLSQL syntax is largely supported by PostgreSQL but there's still diversions. MySQL has yet to name their extension.

Very basic SQL syntax like your example above is pretty much the same across all SQL databases, but once you start getting into joins and anything more complex there are quite a lot of differences.

You are correct. Although basic functionality is similar, support for more complex features varies from engine to engine. If you want to get into the specifics, a good place to start might be by looking at the source code for hibernate's various dialects.

Unfortunately the syntax differs between DB Management Systems. For example in MSSQL there is a TOP construction, whereas in MySQL you'll have to use LIMIT. They also differ in basic thing like data types. Generally speaking the more complicated queries you write the bigger differences show up.

You might be interested in looking at Comparison of relational database management systems.