Choosing Join Columns

Go Up to Joining Tables (Embedded SQL Guide)

How do you choose which columns to join? At a minimum, they must be of compatible data types and of similar content. You cannot, for example, join a CHAR column to an INTEGER column. A common and reliable criterion is to join the foreign key of one table to its referenced primary key. Often, joins are made between identical columns in two tables. For example, you might join the Job and Employee tables on their respective job_code columns.

­INTEGER, DECIMAL, NUMERIC, and FLOAT data types can be compared to one another because they are all numbers. String values, like CHAR and ­VARCHAR, can only be compared to other string values unless they contain ASCII values that are all numbers. The CAST() function can be used to force translation of one InterBase data type to another for comparisons. For more information about CAST(), see Using CAST( ) for Data Type Conversions.

Important: If a joined column contains a NULL value for a given row, InterBase does not include that row in the results table unless performing an outer join.