Which DBMS are more standard compliant? I find it frustrating that the same queries don't work in different DBMS.

Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.

Some databases such as MySQL allows you to do SHOW TABLES, etc.

Some limit the results using SELECT TOP 10 ... while others with ... LIMIT 10.

Why are different DBMS so incompatible and deviant?

Why don't they follow the SQL standard?

Which are good at adhering to the SQL standard?

Which are notoriously bad at adhering to the SQL standard?
I find it frustrating that the same queries don't work in different DBMS.


Yes. I find it frustrating that the headlights and wiper controls are not always in the same place in different cars. Since I can't control it, I learn to live with it. I generally stick to certain car brands but the location of these things is not anywhere near the top of the list of primary decision criteria. Similarly, I don't pick a database platform because of what percentage of the standard they cover or how many queries work the same as in Oracle and MySQL.


  Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.


INFORMATION_SCHEMA was an interesting choice. Yes, some platforms have opted not to support that standard at all. Others have covered the bare minimum. I make a pretty strong argument against using INFORMATION_SCHEMA in SQL Server, for example: 


The case against INFORMATION_SCHEMA views



  Some databases such as MySQL allows you to do SHOW TABLES, etc.


Well, yes, some databases are more notorious than others for just making up stuff that seems to make sense to the developer knee-deep in code at the time (LIMIT and their perverse GROUP BY forgiveness are other examples).


  Why are different DBMS so incompatible and deviant?


I'm sure some do things like SHOW TABLES because that is considered easier than SELECT name, other cols FROM sys.tables;. In fact I would bet that that's the problem with the majority of the world's codebase, RDBMS included; people tend to do what's easier rather than what's right.

But I'm not sure it's a problem that the different platforms are incompatible and "deviant" as you call them. 


  Why don't they follow the SQL standard?


Covering the entire standard is hard. Covering only the standard is even harder. Vendors develop extensions to the standard, and proprietary features not (yet) covered by the standard, to give users more powerful platforms. Imagine if we sat around waiting for the next iteration of the standard before we could develop anything? Imagine if all of the platforms covered the full standard and nothing but the standard? Imagine if all of the platforms only implemented any new functionality that they all agreed to develop the same and release at the same time? What would be the point of having different platforms? They'd all be the same, and the incompatibility would no longer be an issue because you could switch freely between platforms without risk - but then why would you, if they're all the same? See the problem you're "solving" - it just creates a different problem.

Take indexes for example. The standard doesn't care about physical implementation, and some vendors have made decisions to implement all kinds of beneficial index variations and physical storage mechanisms to better their platforms - SQL Server has filtered indexes, include columns, XML indexes, full-text indexes, in-memory tables, columnstore, etc. etc. None of which you'll read a lick of in the standard. So as they develop these things, how are they supposed to make it compatible with anything along the same lines that Oracle may or may not be developing or planning? Postgres has gone on and made the decision that elaborate JSON support is far more important than, say, performance gains from parallel processing. That's fine, and totally within their right. That doesn't mean I want SQL Server to do the same thing.

Normal ad hoc CRUD stuff should work identically for basic vanilla tables across platforms (though even there, MySQL has done some funky things like use backticks around table names by default, which need to be stripped out if trying to port the code to any other platform). As soon as you start doing anything more exotic outside of standard storage and retrieval, the standard throws its arms in the air. You want Window functions? Okay, you need your vendor to roll up their sleeves and implement them, because most platforms don't have them, and they take a lot of work; even SQL Server is not yet complete, but they're way ahead of the other guys. And they have plenty of other language features that I'm very happy are there, in spite of them not being in the standard. 


  Which are good at adhering to the SQL standard?
  Which are notoriously bad at adhering to the SQL standard?


I don't know enough about the full standard and all of the platforms to comment. But I will suggest this: If this is the reason you're choosing one platform or another, stop what you're doing right now, and ask yourself why this is so important. I will also suggest that SQL Server has gotten much better about adhering to the standard and implementing extensions where they are beneficial (a look back in history at things like timestamp and, yes, top shows that they used to care a lot less). Some other platforms give a big middle finger to the standard, and continue to do so.