It’s now time to do a Data Manipulation Language (DML) statements recap and use the sample UMADB database, presented in the previous two articles, in all the coming examples.

It will go over the SELECT, INSERT, UPDATE, and DELETE statements. However, this chapter won’t discuss the syntax of these statements. Here we will explain how you can write shorter and clearer statements by resorting to a few keywords that you might not be aware of. If you want to play around with the examples, be sure to restore the UMADB library. SQL 101 48 - UMADB.zip.

I’m going to assume that you’re familiar with the most commonly used DML statements and will not explain their syntax in depth, as they were previously covered in this series, starting here. Instead, I’ll focus on some details that can simplify the statements—for instance, shorter “implementations” of concepts. Having said that, let’s start with two of my personal favorites: BETWEEN and IN.

Using the BETWEEN and IN Predicates

Let’s get started with a simple yet very powerful keyword. If you started querying the IBM i’s database using Query/400 (as most of us did), one of the things you might miss is the RANGE keyword. This simple-to-use tool allows you specify the lower and upper limits of a range of values in a clear and concise way. What you might not know is that SQL has a RANGE equivalent: BETWEEN. This keyword’s equally simple to use, but it has a different syntax, which is closer to common English than the robot-speak of RANGE.

It’s easier to explain with an example, so let’s imagine that a user needs a list of all the university students that were born in the 1990s. The knee-jerk reaction would be to write something like this:

SELECT      STNM

            , STDB

   FROM     UMADB_CHP2.PFSTM

   WHERE    STDB >= 19900101

            AND STDB <= 20000101

;

Even though this statement is correct (assuming that the student’s birth date, column STDB of the PFSTM table, is in YYYYMMDD format), it can be made clearer with BETWEEN:

SELECT      STNM

            , STDB

   FROM     UMADB_CHP2.PFSTM

   WHERE    STDB BETWEEN 19900101 AND 20000101

;

Notice how using the BETWEEN predicate made the statement easier to read. By the way, I’m a big fan of clear code, so you’ll see a lot of indentation in my code examples. It makes the code easier to read and, more importantly, easier to maintain. For instance, if I want to add a new column to the query, I simply add a new line wherever I need to add it, and insert a comma followed by the column name. If all the columns are in the same line, this might not be so simple, especially in queries with a lot of columns. The only downside to this is that my queries tend to get a bit long. However, if you use IBM Navigator for i’s Run SQL Scripts or any other non-native query tool (IBM Rational Developer for i’s, WinSQL, Toad, DBeaver and so on – I covered a few of these previously, so if don’t know them, go and have a look, you might be positively surprised) this is not a big issue. All the examples shown here were written in Run SQL Scripts. You’ll notice the SQL syntax with the period (.) separating the library (schema) and file (table) names, instead of the system’s native syntax with the slash character (/) acting as a separator between the library (or schema) and the table, and the semicolon (;) terminating each statement.

Even if you knew BETWEEN, you might not know that you can invert the selection by adding a simple keyword: NOT. Here’s an example to illustrate what I mean: the user actually wanted a list of all the students who weren’t born in the 90s. Well, let’s not waste the statement we just wrote. Let’s modify it instead:

SELECT      STNM

            , STDB

   FROM     UMADB_CHP2.PFSTM

   WHERE    STDB NOT BETWEEN 19900101 AND 20000101

;

See how with a very simple change you can get the exact opposite result of the original query? You can use NOT in all sorts of ways to easily negate a comparison. It’s particularly useful when the opposite of a comparison is complex to write down. Instead, you can simply write NOT (original comparison), and you’re done. I’ll provide additional examples in a moment.

Another tedious and error-prone situation is when you want to find all the records that have one of several values in a given column. For instance, let’s say that someone wants a list of all the teachers with the rank of Dark Master, Maximus Praeceptor, or Praeceptor. (Yup, the teachers’ table is a bit quirky—actually, the entire database is! Take a moment to query the tables, and you’ll see what I mean.) In Query/400 you’d use LIST, but I noticed many people still write the comparison statement using something like this:

SELECT      TENM

, TETR

   FROM     UMADB_CHP2.PFTEM

   WHERE    TETR = 'Dark Master'

            OR TETR = 'Maximus Praeceptor'

            OR TETR = 'Praeceptor'

;

Instead of using LIST’s SQL equivalent IN keyword:

SELECT      TENM

, TETR

   FROM     UMADB_CHP2.PFTEM

   WHERE    TETR IN ('Dark Master', 'Maximus Praeceptor', 'Praeceptor')

;

Naturally, you can also use NOT to quickly list all the teachers who don’t hold one of these ranks:

SELECT      TENM, TETR

   FROM     UMADB_CHP2.PFTEM

   WHERE    TETR NOTIN ('Dark Master', 'Maximus Praeceptor', 'Praeceptor')

;

I’ve shown examples of BETWEEN using numeric value and IN using character values, mainly because this is their most common use. Keep in mind, though, that you can use these SQL predicates with any types of values and in any SQL clause that requires the use of a comparison, in addition to the WHERE clause. This is just the first taste of what’s coming! The next articles will cover other interesting DML operators, so stay tuned.