Joins

A join results from a statement that combines columns from two or more tables into a single view. From this view, you can retrieve, insert, update, or delete data, provided it is not read-only.

Note: This section primarily discusses joining tables using SELECT statements. However, you can also create joins with INSERT, UPDATE, and DELETE statements by applying a single statement to more than one table. SQL Engine Reference describes these SQL statements and how to optimize joined views.

You can retrieve data from tables by listing each table or view name in a FROM clause. Use a WHERE clause to specify one or more join conditions. A join condition compares an expression that references a column value from one table to an expression that references a column value from another table.

When data is properly normalized, most joins associate values based on some specified key value. This allows you to extract data in terms of referential integrity relationships. For example, if you want to know which professor teaches each class, you can create a join based on the Faculty ID, which is a foreign key in the Class table and a primary key in the Person table:

SELECT DISTINCT Class.Name, Person.Last_Name
FROM Class, Person, Faculty
WHERE Class.Faculty_ID = Person.ID
AND Class.Faculty_ID = Faculty.ID;

This example joins two tables on the basis of common values in a common column: Faculty ID.

You can also join tables by making numeric comparisons between columns of like data types. For example, you can compare columns using <, >, or =. The following self-join on the Faculty table identifies all faculty members whose salary was higher than each faculty member (this would produce considerably more records than the faculty table contains):

SELECT A.ID, A.Salary, B.ID, B.Salary
FROM Faculty A, Faculty B
WHERE B.Salary > A.Salary;

When joining columns, choose columns that are of the same data type when possible. For example, comparing two NUMERIC columns is more efficient than comparing a NUMERIC column with an INTEGER column. If the columns are not of the same data type but are both numeric or strings, Pervasive PSQL scans both the tables and applies the join condition as a restriction to the results.

When you use string type columns in a WHERE clause, one column in the join condition can be a computed string column. This allows you to concatenate two or more strings and use a join condition to compare them to a single string from another table.

The way in which Pervasive PSQL handles a join depends on whether the join condition contains an index column.

•

If the join condition contains a column that is defined as an index, performance improves. Using the index to sort rows in the corresponding table, Pervasive PSQL selects only rows that meet the restriction clause condition.

•

If the join condition does not contain a column that is defined as an index, performance is less efficient. Pervasive PSQL reads each row in each table to select rows that meet the restriction clause condition. To enhance performance, you can create an index in one of the tables before executing the join. This is especially helpful if the query is one that you perform often.

Joining Tables with Other Tables

To specify a join using a SELECT statement, use a FROM clause to list the relevant tables and a WHERE clause to specify the join condition and the restriction. The following example also uses aliases to simplify the statement.

SELECT Student_ID, Class_ID, Name
FROM Enrolls e, Class cl
WHERE e.Class_ID = cl.ID;

The next example joins three tables:

SELECT p.ID, Last_Name, Name
FROM Person p, Enrolls e, Class cl
WHERE p.ID = e.Student_ID AND e.Class_ID = cl.ID;

The next example retrieves a list of students who received a grade lower than a 3.0 in English.

SELECT First_Name, p.Last_Name

FROM Person p, Student s, Enrolls e, Class cl

WHERE s.ID = e.Student_ID

AND e.Class_ID = cl.ID

AND s.ID = p.ID

AND cl.Name = 'ACC 101'