JOINs: INNER, FULL, LEFT, and RIGHT

Overview

The operator JOIN was introduced in SQL/92 and was a replacement for an inconvenient operator “+” in SQL/86. The purpose of JOIN in RBDMS was to join several tables in one result. For object databases, like Eloquera, the usefulness of the operator JOIN has been reduced somewhat due to the availability of natural objects references. Nevertheless, JOIN could be useful in some circumstances.

Syntax and basics of JOIN

General syntax of JOIN:

SELECT Alias1,Alias2 FROM Type1 Alias1 [ LEFT | RIGHT| INNER | FULL ] JOIN Type2 Alias2 ON SomeFunction(Alias1.Field1, Alias2.Field2)

If the JOIN query contains SELECT of more than one type, then functions ExecuteQuery(…) and ExecuteScalar(…) returns an object IEnumerable<Dictionary<string,object>>. This object represents an associated collection of “type name – value”. See below for more details.

If JOIN query contains SELECT of a single type, then the enumeration of the objects (of the requested type) are returned.

Types of JOIN

The Eloquera database supports the following types of JOIN:

-    INNER JOIN.

The INNER JOIN keyword return rows when there is at least one match in both tables.

Example:

SELECT sch, wp FROM School sch INNER JOIN WorkPlace wp ON sch.Location=wp.Location

Above query selects the following database objects that have a common location:

—  School

— WorkPlace

If one object School has a common location with several objects WorkPlace, then the result includes several WorkPlace objects with the corresponding School.

For instance, tables Location, School and WorkPlace contain the following objects:

As mentioned above, any queries with the Eloquera database should use methods, such as:

DB.ExecuteQuery(…);

DB.ExecuteScalar(…);

Query:

IEnumerable<Dictionary<string,object>> queryResult = (IEnumerable<Dictionary<string,object>>)

db.ExecuteQuery("SELECT sch, wp FROM School sch INNER JOIN WorkPlace wp ON ch.Location=wp.Location");

Results are obtained as follows:

#	queryResult[“sch”]	queryResult[“wp”]
1	School1	wPlace2
2	School2	wPlace2
3	School3	wPlace3
4	School3	wPlace4

Here are some relevant observations:

• INNER JOIN type is most widely used and useful from all JOINS in relational databases.

• Use of this JOIN in Eloquera might be necessary to join two tables that weren’t related during the program design. In most cases this could be a sign that application is flawed.

-    LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.

Query:

IEnumerable<Dictionary<string,object>> queryResult = (IEnumerable<Dictionary<string,object>>)

db.ExecuteQuery("SELECT sch, wp FROM School sch LEFT JOIN WorkPlace wp ON sch.Location=wp.Location");

In this case, the following result is obtained:

#	queryResult[“sch”]	queryResult[“wp”]
1	School1	wPlace2
2	School2	wPlace2
3	School3	wPlace3
4	School3	wPlace4
5	School4	Null

-    RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table, even if there are no matches in the left table.

Query:

IEnumerable<Dictionary<string,object>> queryResult = (IEnumerable<Dictionary<string,object>>)

db.ExecuteQuery("SELECT sch, wp FROM School sch RIGHT JOIN WorkPlace wp ON ch.Location=wp.Location");

 And the following result is obtained:

#	queryResult[“sch”]	queryResult[“wp”]
1	School1	wPlace2
2	School2	wPlace2
3	School3	wPlace3
4	School3	wPlace4
5	Null	wPlace1

-    FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

Query: 

IEnumerable<Dictionary<string,object>> queryResult = (IEnumerable<Dictionary<string,object>>)

db.ExecuteQuery("SELECT sch, wp FROM School sch FULL JOIN WorkPlace wp ON sch.Location=wp.Location");

And the following result is obtained:

#	queryResult[“sch”]	queryResult[“wp”]
1	School1	wPlace2
2	School2	wPlace2
3	School3	wPlace3
4	School3	wPlace4
5	Null	wPlace5
6	School4	Null

Recommendation:

To match two types using JOIN, use the primary key that exists in all Eloquera tables and available in all queries under the name $ID.

For example:

SELECT a, b FROM A a INNER JOIN B b ON a.$ID = b.aId

Capabilities of JOIN

JOIN queries, as well as any other type of query could have:

•Named parameters:

Named parameters could also be used in ON statements.

A few limitations apply in using named parameters:

1)     Value of the named parameter shall be defined prior to the query execution.

2)     Named parameters cannot be used in a type name e.g. SELECT @type.

•Arrays

JOIN queries can use the functionality of entire arrays throughout the query.

•Inheritance

By default, types requested after the FROM are used, as well as their children.

For instance:

If TypeC inherits TypeA, then result of the query

      SELECT a,b FROM TypeA a INNER JOIN TypeB b ON b.id = a.id

could also return object TypeC for the field ‘a’, if it complies with condition in the query.

If you don’t want to use the children and require only the given type, you should use ONLY keyword

For example:

      SELECT a,b FROM ONLY TypeA a INNER JOIN TypeB b ON b.id = a.id

Result never includes TypeC in a field ‘a’.