List of differences between SQL databases
I’m not sure how comprehensive this list is, but maybe this will help – http://troels.arvin.dk/db/rdbms/
ansi-sql
Standard SQL boolean operator IS vs. equals (=) operator
That’s a new one for me. If I read that correctly the <boolean value expression> grammar defines three predicates solely for use with the boolean datatype IS TRUE, IS FALSE, IS UNKNOWN. These differ from their equality counterparts in that they only evaluate to True or False. Never to Unknown. i.e. UNKNOWN = TRUE would … Read more
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
this shows the different ways: — DB2 select * from table fetch first 10 rows only — Informix select first 10 * from table — Microsoft SQL Server and Access select top 10 * from table — MySQL and PostgreSQL select * from table limit 10 — Oracle select * from (select * from table) … Read more
What does SQL Select symbol || mean?
|| represents string concatenation. Unfortunately, string concatenation is not completely portable across all sql dialects: ansi sql: || (infix operator) mysql: concat ( vararg function ). caution: || means ‘logical or’ (It’s configurable, however; thanks to @hvd for pointing that out) oracle: || (infix operator), concat ( caution: function of arity 2 only ! ) … Read more
Why does no database fully support ANSI or ISO SQL standards?
In the software industry you have some standards that are really standards, i.e., products that don’t comply with them just don’t work. File specifications fall into that category. But then you also have “standards” that are more like guidelines: they may defined as standards with point-by-point definitions, but routinely implemented only partially or with significant … Read more
Difference between CURRENT_TIMESTAMP and GETDATE() [duplicate]
CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function. One interesting thing to note however, is that CURRENT_TIMESTAMP is converted to GETDATE() when creating the object within SSMS. Both functions retrieve their value from the operating system in the same way. There is no difference between the two, … Read more
ANSI SQL Manual
Here’s the ‘Second Informal Review Draft’ of SQL:1992, which seems to have been accurate enough for everything I’ve looked up. 1992 covers most of the stuff routinely used across DBMSs.
In MySQL, should I quote numbers or not?
MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you’re working with an int field (left-hand side), it’ll try to transparently convert the right-hand-side of the argument into an int as well, so ‘9’ just becomes 9. Strictly speaking, the quotes are unnecessary, and force MySQL to do … Read more
How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown. These predicates work with any comparable … Read more