Now that you know how the EXISTS predicate works and have a good grip on the whole INNER/OUTER JOIN thing, explaining the exception join will be a breeze.

Like many IBM i databases in the real world, this database has no true structure. It’s just a set of tables, informally joined by fields with the same name, or similar names, in different tables. The actual table relationships are kept by the RPG programs that use the database. Naturally, this can potentially lead to database inconsistencies and loss of database integrity.

Editor's Note: This article is excerpted from chapter 4 of SQL for IBM i: A Database Modernization Guide, by Rafael Victória-Pereira.

A typical example in our little fictional database would be a class with the wrong course name. Let’s create that scenario by inserting a record in the Classes table with a course name that doesn’t exist in the Courses table:

To confirm that an inconsistency exists, let’s run a SELECT using your newly acquired

knowledge of EXISTS:

This will return a single row, matching the “Macbeth meets Friedrich Nietzsche” class that was created moments ago. Now let’s rewrite this query using EXCEPTION JOIN instead of EXISTS:

It looks like a regular JOIN, similar to the ones discussed in Chapter 2, but the functionality is totally different. In fact, it’s similar to the Query/400 “unmatched records with primary file” option. It offers a simple way to check for data problems that’s

unique to the IBM i. It’s true that you can use EXISTS/NOT EXISTS to implement similar functionality, at least most of the time, but the EXCEPTION JOIN’s compact and “natural” syntax is faster to write and easier to maintain. There’s a catch: writing an EXCEPTION JOIN with three or four tables might not produce the results you’re looking for. There will be times when an EXISTS predicate will yield better results, even if it’s not as readable as an EXCEPTION JOIN. Anyway, this is a new tool to add to your kit, and, as most things, there will be appropriate times to use it and other times you will want to look elsewhere for the solution for a specific problem.

To keep our fictional database as “clean” as possible, let’s delete this anomalous record:

Time for Some (More) Practice

Now that I’ve shown you an example of the EXCEPTION JOIN, how about rolling up your sleeves and writing the necessary SELECT statements to check the consistency of the entire database? Or, even better, create a few views that you can use from time to time, just to check whether the database is still fully consistent. This will be a temporary measure, because I’ll show you later how you can (and should) maintain your database consistency using referential integrity and other constraints. For the moment, let me give you a hand and explain how the tables are linked to one another.

• The Courses table links with the Teachers table twice, via the Course_Director_ Name and the Course_TA_Name Note that these links to the Teachers’ Name column are independent from one another, which means you’ll have to write two very similar SELECT statements.
• The Classes table links with the Courses table, as shown before, but also with the Students table. The Classes Student_Name column must match the Students’ Name
• Finally, the Grades table links separately to the Students table, via Student_Name, and the Classes table, via the Class_Name/Class_Year combination, which match the Name and Year columns of the Classes table,

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale