New syntax for joins in Oracle 9 or higher

 

Scope

Language: Java, .NET, Visual Basic, Visual FoxPro
DBMS: Oracle 9 or higher
 

Introduction

As support for ANSI SQL 1999 join syntax was implemented in Oracle 9i, GeneXus now generates this new syntax. The ANSI SQL 99 syntax provides increased functionality and better legibility.
 

Description

The biggest advantage of the new join syntax is its support for full outer joins. The introduction of the ANSI SQL 99 standard join syntax in Oracle9i greatly simplifies the full outer join query, due to the availability of outer joins to multiple tables. We are no longer limited by unidirectional outer joins. Oracle9i introduced the full outer join operation to carry out such operations.

This feature is not visible for the end users of GeneXus applications. It refers to the code generated by GeneXus when Oracle is the DBMS selected, and the DBMS Option Property is set to Oracle version "9.x or higher."

For this reason, the advantages of the SQL 99 join syntax are only visible for programmers.
The first one is that it's a standard. Prior to the SQL 99 standard, there was no commonality in the way that anything other than a simple equijoin or a cross join (i.e., Cartesian product) was implemented across all vendors' product lines.
But what if you wanted to see all customers together with the orders they placed, even if they placed no orders, (i.e., a simple outer join)? For this, you had no commonality between relational database products.

Readability is an additional benefit. Separating the join condition from the WHERE clause makes it really easy to tell apart the join elements from the applied conditions to limit the returned data — even for complex SQL statements.

There is no performance penalty. The use of ANSI SQL 99 syntax doesn't slow down your database (or speed it up).
 

Example of Generated Code

For example, to see all customers along with the orders they placed, the query would be written as follows in Oracle (versions prior than 9):

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID ( )

However, the same query would be written like this in Microsoft SQL Server (old versions):

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID *= O.CustomerID

Now, using the ANSI SQL 99 syntax, the same query can be re-written to work both in Oracle and SQL Server (as well as DB2 and others) as follows:

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C LEFT OUTER JOIN Orders O
ON (C.CustomerID = O.CustomerID)
 

Samples

To list the orders of all the customers, in GeneXus you write:

FOR EACH
print area (OrderId CustomerId Company)
ENDFOR

The navigation list shows
For Each ORDERS (Line: 19)
Order: OrderId
Index: IORDERS

Navigation filters: Start from: FirstRecord
Loop while: NotEndOfTable

ORDERS ( OrderId ) INTO OrderId CustomerId
CUSTOMERS ( CustomerId ) INTO Company


After that, GeneXus 8.0 version for Oracle generates:

SELECT T1.CustomerId, T2.Company, T1.OrderId
FROM ORDERS T1, CUSTOMERS T2
WHERE T2.CustomerId ( ) = T1.CustomerId
ORDER BY T1.OrderId

GeneXus 8.0 version for SQL Server generates:
SELECT T1.CustomerId, T2.Company, T1.OrderId
FROM (ORDERS T1 (NOLOCK) LEFT JOIN CUSTOMERS T2 (NOLOCK)
ON T2.CustomerId = T1.CustomerId)
ORDER BY T1.OrderId


GeneXus 9.0 with Oracle Version = 9.x or higher

SELECT T2.Company, T1.OrderId, T1.CustomerId
FROM (ORDERS T1 LEFT JOIN CUSTOMERS T2
ON T2.CustomerId = T1.CustomerId)
ORDER BY T1.OrderId

FAQ
 
To use it, you should set the value 9.x or higher in the Oracle Version Property.
The property is located in the section Database Information in the Model Properties/DBMS Options
It´s available only when the database manager selected in the model is Oracle.
 
Mainly we suggest to use it because it allows some joins that previously were not possible to generate.