In this lesson, you will learn how to seamlessly bridge data from different source files into a single workspace using the ATTACH command.
The "Single Connection" Concept
Usually, when you connect to a database file (like main.db), your SQL environment is scoped strictly to the tables within that file. However, in lightweight architectures, you often store related data in separate files—perhaps keeping historical records in one file and active transactions in another. Instead of writing complex application-layer code to merge these datasets, SQL allows you to "mount" secondary databases into your existing connection.
Attaching Databases
To query across files, you use the ATTACH DATABASE command. Once attached, you assign an alias to the database, which acts as a namespace prefix for its tables.
Imagine you are connected to users.db and need to pull data from orders.db. You would execute:
-- Attach the secondary file
ATTACH DATABASE 'orders.db' AS orders_data;
-- Perform a cross-database join
SELECT
u.username,
o.order_date,
o.total_amount
FROM users AS u
JOIN orders_data.orders AS o ON u.user_id = o.user_id
WHERE o.status = 'shipped';
Why this matters
By using the alias (in this case, orders_data), you tell the query engine exactly which file to inspect. This is incredibly powerful for modular data management. It allows you to keep your primary database lean while performing analytical joins against archived or external data sources without needing to import or move raw data physically.
Remember, when you detach the database using DETACH DATABASE orders_data;, those tables will immediately become unreachable to your current session, keeping your workspace clean. This approach is highly efficient because it avoids the overhead of duplicating data just to perform a temporary relational query, allowing you to treat multiple files as if they were a single, unified schema.
Key Takeaway: The ATTACH command allows you to treat separate database files as a single, unified environment by referencing them through unique aliases within your SQL queries.