Non-Key Join

Contact Us or call 1-877-932-8228
Non-Key Join

Non-Key Join

Joins are usually specified on primary key and foreign key columns. However, SQL is not limited to joining on such columns. Any columns can be joined as long as the datatypes match. In fact, even a function can be used as part of join.

Just for fun let's see if the first two characters of an employee's first name happen to match any country code; uppercase those two characters since country codes are uppercased.

Code Sample:

SELECT country_id, country_name, first_name
FROM employees e
INNER JOIN countries c ON c.country_id = upper(substr(e.first_name,1,2));

This is a somewhat contrived example since the HR schema is well designed and relatively simple. It involves the use of functions in the joined column. Be aware, there is a potential for performance problems if appropriate indexes have not been defined. Oracle has a feature called function based indexes which can be helpful when using deterministic functions in this manner under some circumstances.