Informix Guide to SQL: Tutorial Composing SELECT Statements

The database that a CONNECT DATABASE or CREATE DATABASE statement opens is the current database. To refer to a table in a database other than the current database, include the database name as part of the table name, as the following SELECT statement illustrates:

SELECT name, number FROM salesdb:contacts

The database is salesdb. The table in salesdb is named contacts. You can use the same notation in a join. When you must specify the database name explicitly, the long table names can become cumbersome unless you use aliases to shorten them, as the following example shows:

SELECT C.custname, S.phone

	FROM salesdb:contacts C, stores:customer S

	WHERE C.custname = S.company

You must qualify the database name with a database server name to specify a table in a database that a different database server manages. For example, the following SELECT statement refers to table customer from database masterdb, which resides on the database server central:

SELECT O.order_num, C.fname, C.lname

	FROM masterdb@central:customer C, sales@boston:orders O

	WHERE C.customer_num = O.Customer_num

	INTO TEMP mycopy

In the example, two tables are being joined. The joined rows are stored in a temporary table called mycopy in the current database. The tables are located in two database servers, central and boston.

Informix allows you to over qualify table names (to give more information than is required). Because both table names are fully qualified, you cannot tell whether the current database is masterdb or sales.