SQLite - How do you join tables from different databases?

Adam Smith picture Adam Smith · Jul 26, 2011 · Viewed 68.3k times · Source

I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a hard time figuring out how to join tables from the different databases.

If someone can help me out with this one, I'd really appreciate it!

Edit: See this question for an example case you can adapt to your language when you attach databases as mentioned in the accepted answer.

Answer

Brian Gideon picture Brian Gideon · Jul 26, 2011

If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db's that can be attached is a compile time setting(SQLITE_MAX_ATTACHED), currently defaults to 10, but this too may vary by the build you have. The global limit is 125.

attach 'database1.db' as db1;
attach 'database2.db' as db2;

You can see all connected databases with keyword

.databases

Then you should be able to do the following.

select
  *
from
  db1.SomeTable a
    inner join 
  db2.SomeTable b on b.SomeColumn = a.SomeColumn;

Note that "[t]he database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment".