Attaching database to an existing connection Is attaching the database supported only by SQLite and MS SQL Server?
All other DBMSes can only have one database per connection?
Scenario:
Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period.
The database in use is not the two mentioned above.
How do people solve that?
EDIT:
The plain SQL supports the syntax of catalog.schema.table which produces fully qualified table name to the query.
However I believe that in order to use that syntax I have to have a connection to the second database.
Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1 and then just use the appropriate syntax.
However if those 2 databases are physically located on 2 different servers, I think I need to create a connection to the second database in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s).
Now, my understanding is that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes.
Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2.
TIA for confirming or deniying my suspicions.
all rdms support
Db.table

SQL SERVER
[db].[dbo].[Table]

So you can make a query
SELECT a.col1,b.cl2 FROM db1.table1 a INNER JOIN db1.table1 b ON a.id= b.id

That would get you columns from 2 databases, on one connection.
2 connections make the thing more complicted
MYsql can establish federated tables http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html so that you have scenario 1 again. (that is possible for almost every rdms)
For Postgreslq exists something similar see https://www.postgresql.org/docs/9.3/postgres-fdw.html
Else you can only get the data from connection/Server  1 and compare it to the data received by server 2, by means of a variables
(the syntax differs somewaht from rdms to rdms)