TIBSQL - This is the most bare component for just executing SQL. No interface to data-aware components. Very simple and ideal for just executing SQL.
TIBSQL is really just a wrapper around the underlying Firebird API.
TIBQuery - The next thing. On itself a read-only dataset, which can be expanded with TIBUpdateSQL to make it updatable. Highly flexible. SQL can be anything you want (including joins etc). Can be used with data-aware components. (I use it most of the time with a dynamic function to create TIBUpdateSQL from the given SQL.)
TIBDataSet - Somewhat more heavy. Includes UPDATE, INSERT and DELETE statements. Buffers the result set.
To be honest, there is not much difference between TIBQuery/TIBUpdateSQL and TIBDataset. Both TIBQuery and TIBDataset descend from TIBCustomDataset and neither adds much in the way of extra functionality. TIBDataset does little more than publish selected TIBCustomDataset properties, while TIBQuery adds SQL parameter property management allowing parameter values to be given at design time (for what it's worth). Both buffer the result set unless you set the Unidirectional property to true - buffering is avoided at the cost of losing bidirectional navigation.
TIBUpdateSQL allows you to add Modify/Insert/Delete/Refresh SQL to an existing TIBQuery (select query). You can thus flexibly extend an existing read only dataset and make it read/write without having to replace it with a TIBDataset. However, the logic for buffering the dataset and transferring data to and from the database is all in TIBCustomDataset and is the same regardless of which approach you take. If you know that you are going to want a read/write dataset when you first write a program then TIBDataset has the merit of holding all the SQL queries in the same object rather than splitting them across two objects. At run time, the path lengths are slightly longer when using TIBQuery/TIBUpdateSQL compared with TIBDataset, but not in a significant way.
A potentially interesting use of TIBUpdateSQL at run time is with TIBDataset (or even TIBTable). These also have a public UpdateObject property (this is published in TIBTable). If you assign a TIBUpdateSQL object to a TIBDataset.UpdateObject property then its Modify/Insert/Delete/Refresh SQL overrides the Modify/Insert/Delete/Refresh SQL in the TIBDataset properties. Similarly, if you use at TUpdateSQL with a TIBTable then you can thus modify the way it updates the underlying table using custom SQL instead of the automatically generated SQL.
TIBTable - The heavy one. You provide a table name and it just does a SELECT on it. No joins etc. Can also read a lot of data/records (including ALL fields) so you might want to avoid this one in client/server production programs.
TIBTable is also a TIBCustomDataset descendent and functionally is the same as TIBDataset except that it generates the SQL queries for you rather than you having to provide them. The downside is that it is restricted to a single table (no joins) and that all fields are read/written rather than only those that you are interested in. It should only be of interest for simple applications and for programmers that do not know SQL and do not want to know about SQL. Everyone else should use TIBDataset or TIBQuery/TIBUpdateSQL.
IBX for Lazarus might differ slightly but I think the core functionality remained the same.
That's true - warts and all!