EXISTS is arguably the most under-utilized predicate in SQL, because most users don’t appreciate what it can do. Let’s explore a potential use case of EXISTS as a way to avoid complicated JOINS.

We all know that our databases are not completely free of data quality issues. Our programs cause some of these, while others are – inadvertently (I hope) – caused by our users. The fact of the matter is that the data quality issues are a fact of life. The EXISTS predicate is a shortcut to avoid them until you can clean up the database. Here’s how you can use it.

Using EXISTS to Omit Records on a SELECT Statement

Another typical request from users is to show only records from a table that meet a condition that doesn’t depend directly on that table. In other words, something that you can’t easily put in the WHERE clause. As previously mentioned, this can be caused by a known but not yet solved data quality issue. Here’s an example: imagine that I want to refine the Course Information view to hide the courses that don’t offer classes at the moment (or some other convoluted restriction or known data quality issue).

You can do this with OUTER JOINs or really complex statements, but there’s a (probably) more straightforward solution: just use the EXISTS predicate. Let me show you an example: let’s take the SELECT statement from the Course Information view and “hide” courses without classes. In other words, only the course names that have at least one match in the Classes table will be shown. Seems complex? It’s actually quite simple:

SELECT      COURSES.COURSE_ID

      , COURSES.NAME

      , COURSES.DESCRIPTION

      , COURSES.DEP_NAME

      , DIRECTOR.NAME as "DIRECTOR_NAME"

      , DIRECTOR."RANK" as "DIRECTOR_RANK"

      , TA.NAME as "TA_NAME"

      , TA."RANK" as "TA_RANK"

FROM UMADB_CHP4.TBL_COURSES COURSES

      INNER JOIN UMADB_CHP4.TBL_TEACHERS DIRECTOR

ON COURSES.CODE = DIRECTOR.NAME

      INNER JOIN UMADB_CHP4.TBL_TEACHERS TA

ON COURSES.COTA = TA.NAME

WHERE EXISTS (

SELECT 1

FROM UMADB_CHP4.TBL_CLASSES CLASSES

WHERE CLASSES.COURSE_NAME = COURSES.NAME

)

;

I added a WHERE clause, with an EXISTS predicate and a query inside it. Note that this is not a sub-query, in the traditional sense: I’m not interested in how many rows or columns are returned by this inner query. I just need to know if the number of rows returned is zero or not, for the specified condition (in this case, CLASSES.COURSE_NAME = COURSES.NAME). If the inner query returns at least one record, then the condition with the EXISTS predicate will evaluate to TRUE, meaning that the Courses table record with that course name will be returned. Otherwise, if there aren’t any classes with that course name (i.e., the inner query doesn’t return a single record), the condition evaluates to FALSE and the Courses record is ignored. This is a simple way to restrict which records a query returns based on complex conditions. You can also use NOT EXISTS to make the conditions more readable and maintainable.

Let’s create the conditions necessary to see this in action. As things stand, all courses have classes associated with them. In the downloadable source code, you’ll find data about three courses, each with four classes. You can confirm this by running the SELECT statement with the EXISTS predicate I’ve just shown. Now let’s add an additional course record and rerun the SELECT to see what happens. If you’re following along, type the following statement:

INSERT INTO       UMADB_CHP4.PFCOM

(CONM, CODS, CODN, CODE, COTA, COSC)

VALUES(

'Philosophy Studies and Shakespeare'

      , 'To be or not to be? Checking my EXISTance'

      , 'Philosophy'

      , 'Dr. Doom'

      , 'Vader, Darth'

      , '1'

)

;

If you rerun the SELECT statement, you’ll still see the same three courses, even though you’ve just added a fourth to the Courses table. This is because there’s no match for “Philosophy Studies and Shakespeare” on the Classes table. Now, let’s create a class for this course:

INSERT INTO UMADB_CHP4.PFCLM

   (CLNM, CLCY, CLCN, CLSN, CLSA, CLSE, CLSC)

VALUES('Hamlet and Mao Tse Tung'

      , 2016

      , 'Philosophy Studies and Shakespeare'

      , 'Dalton, Joe'

      , '52 SloMo Boulevard, San Fernando, California'

      , This email address is being protected from spambots. You need JavaScript enabled to view it.'

      , '1'

   )

;

Finally, run the SELECT statement once again. Now there are four courses, as many as exist on the Courses table, because we just added a Class record to match the “Philosophy and Shakespeare” course. This is just a simple illustration of how the EXISTS predicate can be used. You can probably think of a few examples of your own, based on your data and its specificities.

Next time, we will cover another way to root out data quality issues using the EXCEPTION JOIN. Until then, feel free to reach out with your questions, comments, or corrections using the Comments section of this article.

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