I'm not fanatic on any database but I wish to see differences between vendors.
For instance, I use mostly Oracle and I see that others (MySQL, SQL Server, PostgreSQL, ...) cannot do:
The same for SQL Server, that others cannot do:
MySQL: REPLACE. It's an "DELETE, then INSERT" which will Replace an existing row.
SQL Server has a built in Auto Incrementing feature you can add to a column when you want a simple incrementing integer for a table.
In Oracle, you need to create a sequence and apply the appropriate trigger to have it increment itself.
Both SQL Server and MySQL don't support the standard "double pipe" for string concatenation, while every other DB seems to. (Update: apparently MySQL does... see below)
But this could be fixed... in the code for MySQL, and via http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259291 for SQL Server.
Oracle's CONNECT BY is a limited version of standard SQL's recursive SQL. DB2s and MSSQLs recursive "common table expressions" are a bit harder to code but offer more power than Oracle's CONNECT BY. (The next version of Oracle's database should get real recursive SQL, though.)
Flash back is a truly unique Oracle feature which reflects how deep MVCC is built into Oracle. MVCC is how Oracle deals with concurrency, as opposed to traditional pessimistic locking; and concurrency handling is one of the places where there is a lot of difference between databases (although most DBMSes are moving away from concurrency solely based on pessimistic locking). The fact that Oracle builds to firmly on MVCC is a significant advantage, in my opinion.
Regarding TOP results: All DBMSes have ways to do this.
Apart from that:
SQL-wise: Oracle has the most advanced and standards-compliant datetime handling. Oracle is strong on OLAP-related functions (but so are both DB2 and MSSQL; OLAP functions is an area where the open source DBMSes have had some trouble keeping up). Lately, Oracle seems to have basically ignored the SQL standard, in that its standards compliance is stagnating (in contrast to MSSQL, for example, which has improved a lot here); I blame this on arrogance due to Oracle's large market share.
Conceptually: Oracle and MySQL are examples of two very different ways of handling databases. In Oracle, it takes forever to create a database, and a database is a very heavy weight object, so in the Oracle world, a database tends to contain a lot of tables, possibly in different "schemas". In MySQL, a database is a very light-weight object, so MySQLers tend to have many databases which comparably fewer tables in each (which is probably why they don't seem to complain about MySQL's lack of schemas).
Oracle (like DB2) is an example of an DBMS which almost includes an entire operating system: It performs many features that a DBMS like MSSQL/MySQL/PostgreSQL would let the operating system's filesystem and virtual memory systems handle. Personally, I prefer the latter approach, but Oracle's way makes Oracle perform very much the same no matter which operating system is being used.
Compared to MSSQL, Oracle runs on many more platforms (like most other DBMSes; MSSQL is probably the only important DBMS which only runs on Windows).
Oracle offers an other way of clustering than most other DBMSes: RAC. I've heard many horror stories about RAC, but if you can get it working, it's probably fair to say that you have a very powerful (and expensive) clustering solution.
Regarding management, I find Oracle to be surprisingly complex to manage, compared to most other DBMSes.
Then, there are serious pricing differences, of course: Oracle's pricing is very high, and rising.
I am a developer on the Sybase SQL Anywhere server team. I could post all kinds of information here on SQL Anywhere (SA) features and such if you want, though I don't want to be downvoted into oblivion for posting advertising. Feel free to contact me directly if you want more information.
Here are responses to existing answers:
|| for string concatenationinsert ... on existing update for doing a single statement that can both insert new rows and update existing onesHere are some features about Sybase SQL Anywhere that (AFAIK) don't exist in other database products:
Full disclosure: I work for Sybase -- I am a developer on the SQL Anywhere server team. This is not meant as advertising or rep-whoring. If requested, I'll mark this answer as community wiki.
A major difference between MySQL and almost every other database out there, with the exception of SQLite, is that MySQL only knows one kind of join -- a nested loop.
This is ok for the kind of OLTP, bare-bones workload MySQL was ostensibly designed for (the same design principles led to not implementing features like triggers, views, and subqueries for a while), but it's deadly for major OLAP work. Don't take my word for it, read Peter Zaitsev: http://peter-zaitsev.livejournal.com/758.html . It is also horribly slow on subqueries, which it learned to do just recently. Small steps, I suppose..
Oracle is very advanced, has a lot of features, and a lot of them cost extra money. It's a bear to set up and support; but if you can afford the DBAs and the maintenance, it performs very well. Did I mention cost and maintenance? Just thought I'd repeat that. Nothing but respect for the Oracle engineers, though, they have built some awesome stuff.
One place where Oracle fails (as do most others) is multi-terabyte workloads. For that, you want to go to specialists like Teradata (best in class, extremely expensive), Greenplum, AsterData, Netezza, and others.
I've heard nothing but good things about SQL Server -- except the fact that it runs only on Windows.
MySQL is easy to set up, fast for OLTP, and generally takes the approach of doing few things, and doing them well.
PostgreSQL is kind of the opposite -- it's a database researcher's favorite playground, which means it has a dozen different join strategies, storage engines, advanced optional packages, and all kinds of stuff. It's slower than MySQL when doing things MySQL does well, and blows it out of the water when doing things MySQL just doesn't know how to do (see above, with hash joins).
DB2 is supposed to be good; no personal experience. It better be good, with all those people at Almaden.. Same with Sybase.
Differences depend on your intent--unless you really want just an exhaustive laundry list. In other words, it appears that you really mean to ask about significant differences, and significance varies by intent.
I suggest that most people typically find several specific things to be significant:
The relative significance of these database characteristics seems to vary primarily based on whether one's intent resides within programming in-the-large or in-the-small. Since I mostly work in the world of programming in-the-large, I will speak from there. When I work (typically on my own projects) in the world of programming in-the-small, I usually still lean towards my decisions for the world of in-the-large since I find it easy enough and I prefer to save myself from the headaches of changing my mind later when my project grows up. I have noticed that many open-source projects suffer huge, sometimes fatal, growth pains when their following forces them to cross the threshold from in-the-small to in-the-large.
My first concern is that I must always be able to change my mind about my choice of database vendor and product, so compatibility is king and portability is queen. With that in mind, I strongly prefer to treat the database as a storage medium only, so I avoid putting any code into the database and I avoid any features that are not relevant to storage or are specific to a vendor or product. For a RDBMS, compatibility primarily means ANSI SQL standard compliance, so I immediately reject any product that is not reasonably compliant. Since I work in the worlds of Linux, Unix, and Windows, portability means availability on all those platforms.
My code belongs together and in the best environment for it--not separated into the crippled environment of a database engine; another aspect of what it means to treat the database as merely a storage medium. Therefore, coding features have limited relevance to me. On very rare occasion, I will create code in the database for a trigger or some convenient functions, so I expect ANSI SQL compliance for basic capabilities and that is all.
I usually have a lot of data, so performance is important and I am willing to trade off some ease-of-use to get it. I don't mind a heavy database installation, or a complex database structure, if I have some basic tools to facilitate and automate the process. That means that I should be able to script the installation of the database engine (binaries) as well as my database structure and data. Being FORCED to use a GUI, as the only option, for important database tasks is unacceptable.
Sometimes, usually in the name of performance, I find it necessary to use a specific feature that drives me to a particular vendor or even a particular product. I do my best to isolate that dependency and to retain my option to switch to another vendor or product, without sacrificing the benefit from that special feature. I might even build an entire system around the special value derived from such a vendor-specific or product-specific feature, but only by explicitly accepting the cost of having done so. The cost of limiting one's options seems to outweigh every other form of cost.
Obviously, the price to purchase the database engine is a cost, but so is the loss of freedom in how to use it. Therefore, I value open-source products VERY highly.
Finally, I am often constrained by the decisions of others, such as my clients. Therefore, I further attempt to be flexible in accommodating choices that I would not make myself.
Given all that, my primary preference for a database engine is SQLite, because its compatibility, portability, and freedom of use outshine all others.
Second, I love Oracle, and I prefer it for programming in-the-large. It is not cheap, but it can do whatever I might need with the best mix of compatibility, portability, performance, and features.
Third, I use Microsoft SQL Server when my clients require it. I maintain skill and compatibility with it because it is too dominant in the market to ignore. It might even have a special feature that I need someday, although I have yet to encounter that situation.
I avoid MySQL because of its lack of compatibility, but I have often supported it as someone else's choice. I considered it for my primary preference, but my in-depth review of its characteristics made it clear that one cannot readily shift a code base between it and any ANSI-SQL-compliant RDBMS. For me, that is completely unacceptable.
By the way, my target for such a shift to a different database engine is a few minutes up to about a week (forty hours). Since I wrap my database access with a compatibility layer, I find that I can usually switch database engines within a few hours at most. Often, my applications are able to support multiple database engines simultaneously (via a simple configuration change at startup).
Best wishes.
I don't have a lot to contribute other than to say that Sybase ASE is, in my opinion, the red-headed step child of RDBMS'. It's missing many of the handy functions that you take for granted with SQL Server or Oracle (and even other flavors of Sybase), you can't link servers and do distributed queries without 3rd party tools, Sybase management tools and IDE's are limited and very poor (again, my opinion), and it's just painful to work with. I guess there's a reason Sybase has only 3% of market share.
Side note: If, like me, you must work with multiple RDBMS' and you need a good tool for that, check out Aqua Data Studio from AquaFold. I just started using it and it's really good. It's also got some great utilities like ER modeling and compare tools built in.
I don't know for the other database server but with PostgreSQL, you can
INSERT INTO mytable
( field_1, field_2,... )
VALUES
( value_1, value_2 )
RETURNING anyfield
anyfield must be a field of mytable of course.
It's very interesting when anyfield is an auto-increment.