Join the Club--and Check Out These Tools --
New tools ease the heavy lifting of SQL joins

"What God has joined let no one put asunder" (Commonly heard at weddings)

Best not to suggest denormalizing His tables either. As this quotation indicates, joins of one kind or another aren't new. With the rise of the relational model and client/server power tools, we're now able to execute joins with almost as much ease as our end users can imagine them. In this month's column, I'll take apart the join -- put it asunder. With the help of some popular tools, I'll reassemble it by the end.

A simple example sets the stage. Figure 1 shows an entity-relationship diagram of a database for film aficionados, consisting of tables for FILM, ACTOR, and ROLE. Both FILM and ACTOR have one-to-many relationships to the ROLE table. A standard SQL two-table join looks as follows:

SELECT ACTOR.NAME "Actor", ROLE.NAME "Role"

FROM ACTOR,ROLE

WHERE ACTOR.ACTOR_ID = ROLE.ACTOR_ID

AND ROLE.FILM_NAME = "North by Northwest"

ORDER BY ACTOR.NAME;

The first rows of output appear in Table 1. The purpose of the query was to determine whether Hitchcock had one of his customary cameo roles. If you read last month's column, you know why Cary Grant appears multiple times in the list. The multiple listings don't point to data corruption or the absence of a unique index. Grant did in fact have multiple names in the film -- which is one reason why I felt like my brain was missing a unique index or two while I watched it. This sort of join matching ACTOR_ID from the ACTOR and ROLE tables with an equality condition is known technically as an equijoin.

Other types of joins are clustered around the garden-variety equijoin. The natural join is a subset that matches columns of the same name. The outer join allows missing (null) values in the query's result set. I suggest the term ether-join for a new variety: the join that boldly spans databases and machines, across the ether -- perhaps making use of a middleware product such as Information Builders' EDA/SQL - to bring together data from systems of various ages and architectures. Figure 2 is a diagram of joins and their characteristics.

When SQL products first hit the scene, such joining capabilities contributed to the belief that all our data access problems would quickly be solved. Viewed through the lens of SQL and artificially enhanced by intoxicating whiffs from vendor demos, our dowdy data models took on dazzling hues.

Was it nirvana? Of course not! If it had been, the vendors couldn't have sold us any new products. In reality, plenty of remaining potholes existed.

"No more need for complex navigational coding," said the SQL vendors. "Now you just tell the DBMS what you want, not how to get it. "Okay, I though, I'll try a join:

SELECT T1.A,T1.B,F,G,H

FROM T1,T2

WHERE T1.A = T2.A

AND T1.B = T2.B

AND T1.C = T2.C

AND T1.D = T2.D

AND F > [value entered by user];

After a few such verbose compound-key joins, we saw that room for improvement clearly existed. The 1990s generation of development tools hasn't eliminated all the complexity of SQL. But they've made some of the most tedious work, the coding of repetitive table-joining syntax, a lot easier. Their solution is what I call the visual join, which greatly simplifies the work of the system's carpenter or "joiner" (an older but certainly appropriate term).

Oracle's Developer/2000 generates joins by reading explicit primary- and foreign-key information in the DBMS (almost certainly Oracle) dictionary. Oracle's standard way of connecting a master block to a detail block is the "Default Block" process. If you choose it, you'll fill in several graphical property sheets that generate default SQL and form behavior. The property sheets also allow more fine-grained control where needed, as with the BIRTH_YEAR condition noted earlier. Creating a second block related to the first causes Developer/2000 to build a relation object. A relation object is an internal control structure that controls several block-to-block properties, including when to populate or refresh the detail block in conjunction with actions occurring in the master block and whether to perform cascading deletes (automatic deletes of detail rows when a master row is deleted).

With its emphasis on the presence of key definitions in the database, Developer/2000 falls under the "hard-wired" category in Figure 2. The advantage of such products is that they encourage careful and complete database design up-front.

If you include Oracle's Designer/2000 CASE tool set in your development process, you can automate even more of your SQL heavy lifting. Always keep in mind, however, that Oracle's power arises largely from the strong integration of the database with development tools. You should take the time to design and document your database before you embark on client-side forms development.

Database Programming & Design--October 1995--Doug Thomson

Corporate Developer -- Why doesn't life come with a quick-reference card? --Visualizing the Plot

The complex, event-driven, graphical systems we're building today have highly complex "plots" due to the many paths users can take through them and the many types of components used to build them, such as screen painters, fourth-generation language (4GL) code, base and derived classes, VBX/OCX controls, SQL, and so on. We need quick-reference visualizers more than ever, and we're fortunate that many of today's tools provide them. I am using the term "visualizers" to refer to diagramming tools that depict an application's overall structure. SQLWindows, by contrast, uses the term to describe what are commonly called widgets (for example, radio buttons, drop-down lists, spin boxes, and so on). Quick-reference visualizers go far beyond the faded green IBM EBCDIC quick-reference card of years past. Now they're on-screen, dynamic, and in at least one case, even configurable to the way individual developers prefer to work.

I'll describe the visualizers provided with Oracle's Developer/2000 (formerly known as Forms version 4.5), -- with a brief nod to object-oriented (OO) class browsers.

Oracle's Developer/2000 was released earlier this year and represents a big leap over its predecessor, Forms version 4.0. One of the leaps is the "Object Navigator," Oracle's visualizer.

The Object Navigator provides a wealth of information about the application being built. It's positively oracular. Familiar visualizer features include instant coordination between the visualizer and design windows; easy expansion and contraction; and context-sensitive launching of windows that help you flesh out the application.

Oracle's favorite differentiator is tight integration between client and server -- and the Object Navigator is no exception. Possibly because Gupta's SQLWindows connects to many back-end DBMSs, its visualizer does not show server objects (for example, the tables owned by a user) in a seamless and complete form along with client objects (for example, radio button objects). Oracle shines here. The Object Navigator shows all relevant server objects along with client objects. As the icing on the cake, Developer/2000 lets you drag client-side objects, such as procedures, and drop them on the server -- in other words, it provides drag-and-drop application partitioning, which can be very valuable when you are fine-tuning performance.