It is also included standard in Java 6, under the name "Java DB"
Both SQLite and Derby operate directly from disk. Only parts of the database file(s) that are needed in order to carry out the requested operations are read.
Both SQLite and Derby offer zero-administration, embeddable SQL database engines. SQLite stores all the data in a single cross-platform disk file. Derby spreads its data across multiple disk files.
Host Language Support
SQLite is written in ANSI-C. It supports bindings to dozens of languages, including Java. You cannot use Derby with languages that do not use the JAVA VM.
Derby is written in Java and is thus usable only by Java and scripting languages that run on the Java VM (Jython, JRuby, Jacl, etc.) and is currently only exposed via JDBC driver. However it is a 100% Java JDBC driver, and hence (with occasional) glitches runs cross platform on any JAVA VM with a single binary distribution. (SQLite is very portable as well, but you would have to maintain multiple binaries if shipping a cross-platform product).
SQL Language Support
Derby supports all of SQL92 and most of SQL99. SQLite only supports a subset of SQL92, though the supported subset is large and covers the most commonly used parts of SQL92. Some differences are pointed out below. One specific difference: Derby supports RIGHT JOINS and FULL OUTER JOINS, SQLite does not.
The code footprint of SQLite is less than 250KB. The code footprint for Derby is about 2000KB compressed and is thus more than 8 times larger. However a large amount of this difference is due to Derby's extensive localization and collation support for multiple languages built in.
In general the memory utilization of Derby is considerably higher than SQLite, occupying several megabytes of memory.
SQLite allows multiple simultaneous readers and a single writer. Mutiple processes can have the database open simultaneously.
Derby only allows a single process to have the database open at a time in its embedded mode. However, Derby also offers a full client/server mode.
In client/server mode, Derby supports giving multiple processes access to the database with row-level locking. Client/server mode of course requires that there be a thread or process available to act as the server, and is less performant than embedded mode.
Roles, Security, Schemas
Derby supports full encryption (see below). In addition it supports multiple databases, and full SQL role granting. Derby supports the SQL SCHEMAS, for separation of data in a single database, and full user authorization.
SQLite is largely a single database at a time engine. The ATTACH DATABASE command can be used to partially ameliorate this. Because of this design, neither SQL roles are not implemented nor are Schemas. Typically, access to the SQLite disk file grants full access to the caller. This is not a defect, but by design.
Derby has support for this built in. SQLite allows you to "fake" these, but has no comparable feature.
SQLite supports only basic types - it is a mostly typeless system. Which can be very nice in some cases, and annoying in others. Derby supports a wide variety of data types, including XML. The foreign key and referential integrity support is also complete.
Built in utilities
Derby has built in online backup/restore and database consistency check utilities. SQLite has a basic database consistency check utility, but no corresponding online backup/restore - you must close connections to the file to get a consistent backup. This is not usually a problem for SQLite, since concurrent usage by multiple PROGRAMS is not usually a design goal.
Derby has built in support for encryption and compression. SQLite has some optional add ins, but they are not part of the standard library.
Both support custom collation functions. Derby comes with many multilingual collations and localizations built in - these have to be manually added to the core SQLite package by the programmer.
Case Sensitive LIKE
Derby has case sensitive LIKE operator, SQLite does not. Derby supports custom collation and indices, like SQLite, but doesn't ship with a built in case-insenstive option.
Derby is currently very weak in this regard. It supports LIMIT effectively via the JDBC maxResultSize, but OFFSET must be faked in a nonperformant manner by obtaining the entire row set in memory and throwing away (manually) the unneeded rows. OLAP support is being added to Derby in the near future and will address this matter.
SQLite fully supports the non-standard-but-extremely useful LIMIT and OFFSET commands that Postgres and MySQL have adopted.
Derby will offer in its forthcoming 10.4 version a basic master-slave replication system. SQLite does not have any such mechanism (again, this is rarely part of the design spec for usage of SQLite).
There are JDBC clustering drivers available for Derby that allow failover to another Derby server.
Both Derby and SQLite are ACID compliant in their default configurations, so their databases will survive a program crash or even a power failure.
Database File Size
No data is currently available on the relative sizes of the database files for SQLite and Derby. Both SQLite and Derby support compression of database files; however SQLITE's VACUUM command makes the database inaccessible during its run, whilst Derby's analagous procedure can be run online.
No data is currently available on the relative speed of SQLite and Derby database engines.
Their query operation is similar in function, relative speed of different queries depend on cache-utilization, query plan optimization and implementation.
However you should be prepared to unpredictable speed penalty when using Derby under different VMs even on same hardware/OS.