BETWEEN<\/code> predicate, which brought back some memories. In the early days of SQL on the ANSI X3H2 Database Standards Committee, we created the BETWEEN<\/code> predicate. We wanted the SQL language to sound a bit like English and have shorthand terms for common coding situations. Many of our keywords were stolen from other programming languages or deliberately chosen so that they were not likely to be confused with data elements. The original syntax for the between predicate looks like this:<\/p>\nBETWEEN::=\r\n<test_expression> [NOT] BETWEEN\r\n <begin_expression> AND <end_expression><\/pre>\nThe BETWEEN<\/code> predicate specifies the inclusive range to test the expression values. The range is defined by boundary expressions with the AND<\/code> keyword BETWEEN<\/code> them. Naturally, all the expressions in BETWEEN<\/code> predicate must be the same data type or cast to it, as in the case of any comparison predicate. This predicate Is defined as a shorthand for:<\/p>\n(<test_expression> >= <begin_expression> \r\nAND <test_expression> <= <end_expression>)<\/pre>\nAnd the negated version of the predicate<\/p>\n
<test_expression> NOT BETWEEN <begin_expression> AND <end_expression><\/pre>\nis equal to:<\/p>\n
NOT (<test_expression> BETWEEN <begin_expression> AND <end_expression>)<\/pre>\nThe grammar for SQL is deliberately picked to be LALR(1)<\/code>. If you don\u2019t remember that from your compiler writing classes, don\u2019t feel bad. It means that SQL can have a little more complicated grammar than many programming languages to sprinkle keywords In places a little more like natural English. It\u2019s important to notice that the BETWEEN<\/code> predicate uses a closed interval, which includes the endpoints of the range. However, the NOT<\/code> BETWEEN<\/code> excludes<\/em> them.<\/p>\n<test_expression> NOT BETWEEN <begin_expression> AND <end_expression><\/pre>\nis equivalent to<\/p>\n
(<test_expression> < <begin_expression> \r\nOR <test_expression> > <end_expression>)<\/pre>\nAt some point in these early days, one of the committee members proposed changing the syntax and creating what would now be called a \u201csymmetric BETWEEN<\/code>\u201d as the default definition. This proposal passed because committees love proposals. Only Microsoft implemented this feature in their Access tabletop database. All the other vendors ignored it, and the proposal was rescinded at the next committee meeting.<\/p>\nBut proposals with extended features seem to keep coming back to life. The current ANSI\/ISO standard syntax is:<\/p>\n
<test_expression> [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] \r\n<begin_expression> AND <end_expression><\/pre>\nThe keyword ASYMMETRIC<\/code> has the original functionality, and it is optional. The BETWEEN<\/code> SYMMETRIC<\/code> syntax is like BETWEEN<\/code> except that there is no requirement that the argument to the left of the AND<\/code> be less than or equal to the argument on the right. Well, not entirely: officially, the <begin_expression><\/code> is the minimum, and the <end_expression><\/code> is the maximum. This transformation converts a SYMMETRIC<\/code> BETWEEN<\/code> into a regular old vanilla BETWEEN<\/code>.<\/p>\nIntervals in the ISO data model<\/h2>\n
Several ISO standards deal with the concepts of intervals. From a mathematical viewpoint, the kinds of intervals you can have are (1) Closed, (2) Opened, (3) Half open high, and (4) Half open low. A closed interval includes both the endpoints, like the range in the BETWEEN<\/code> predicate. An open interval excludes both the endpoints of the range, like the NOT<\/code> BETWEEN<\/code> predicate. The half open intervals are open on either the high-end or the low end of the interval range.<\/p>\nA half open interval on the high-end is how ISO models time. We talk about \u201c24-hour time\u201d Or \u201cmilitary time,\u201d but the truth is a day is defined as an interval from 00:00:00 up to 23:59:59.999.. at whatever precision can be measured. If you try and put in \u201c24:00:00 Hrs\u201d, DB2 and other databases will automatically convert it to 00:00:00 Hrs of the next day. Think of it as being like converting a person\u2019s height from 5\u201918\u201d to 6\u20196\u201d instead. These conventions get even stranger when you look at how different countries and cultures handle times greater than one day. If an event in Japan runs past midnight, they simply add more hours to the event. For example, an event that ran past midnight might be shown as \u201c25:15:00 Hrs.\u201d<\/p>\n
The advantage of the half open interval is easy to see in the ISO 8601 standards, which define how temporal data is represented. You are always sure when an event starts, even if you\u2019re not sure when it will end, so you can use a NULL<\/code> to mark the end of an event that is in process. This NULL<\/code> can be coalesced to a meaningful value. For example, sometimes it might make sense to use COALESCE(interval_final_timestamp, CURRENT_TIMESTAMP)<\/code> To figure out the duration of the interval at exactly the moment the query is invoked. Other times, you might want to use COALESCE(interval_final_timestamp, legally_defined_stop_timestamp)<\/code>.<\/p>\nThe BETWEEN<\/code> predicate is not just used for timestamps. It works perfectly well for numeric ranges and text, too. Numeric ranges can be used to throw things into buckets, which looks reasonably obvious until the three parameter values are of different numeric types. Now you have to consider rounding and casting errors. Even worse, if the parameters are character data with different correlations. As a generalization, you really need to make sure that all three parameters are of the same type. In fact, ranges and text data can get so complicated, I\u2019m just going to ignore them. Let\u2019s just look at numeric ranges.<\/p>\nReport cards<\/h2>\n
A classic example of reducing values into ranges is converting grades from numeric totals or percentages to a letter grade. The usual convention is that a score in the 90s is an \u201cA\u201d, a score in the 80s is a \u201cB\u201d, a score in the 70s is a \u201cC\u201d, a score in the 60s is a \u201cD\u201d and anything below that is an \u201cF\u201d. I\u2019m choosing to ignore plus or minus options on the letters.<\/p>\n
The CASE<\/code> expression in SQL is executed from left to right, and the first WHEN<\/code> clause that tests TRUE<\/code> returns the value in its THEN<\/code> clause. This means that the order in which you write your tests will control how it executes; not all programming languages work this way. In effect, we have hard-coded half open intervals.<\/p>\nCASE\r\nWHEN score >= 90.000 THEN \u2018A\u2019\r\nWHEN score >= 80.000 THEN \u2018B\u2019\r\nWHEN score >= 70.000 THEN \u2018C\u2019\r\nWHEN score >= 60.000 THEN \u2018D\u2019\r\nELSE \u2018F\u2019 END<\/pre>\nIt is important to notice that this expression will handle somebody who has more than 100 points to qualify as an \u201cA\u201d student. In this example, that\u2019s probably what was intended for extra credit, but this might indicate an error in the data in other schemes. Likewise, a score of zero might be a data error. Then, of course, because this is SQL, what would a NULL<\/code> mean? Perhaps it indicates an incomplete? A general rule of thumb is to design for the extreme cases but tuning for the most expected cases.<\/p>\nThe OVERLAPS() predicate<\/h2>\n
The OVERLAPS<\/code> predicate is part of the SQL Standards but not part of SQL Server. This predicate is defined only for temporal data and is based on temporal intervals. Yes, there is a temporal interval type in Standard SQL. Before getting into it, we need to back up and discuss something known as Allen\u2019s operators. They are named after J. F. Allen, who defined them in a 1983 research paper on temporal intervals. The basic model has two temporal intervals, expressed as ordered pairs of start and termination timestamps (S1, T1) and (S2, T2).<\/p>\nHere are the base relations between two intervals, as timelines.<\/p>\n
![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/an-occlusion-calculus-based-on-an-interval-algebra.png\")
<\/p>\n
SQL did not add all 13 relationships, but we decided that an overlaps predicate would be the most useful.<\/p>\n
The result of the <OVERLAPS predicate><\/code> is formally defined as the result of the following expression:<\/p>\n (S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2))\r\n OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1))\r\n OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))<\/pre>\nwhere S1 and S2 are the starting times of the two time periods and T1 and T2 are their termination times. The rules for the OVERLAPS()<\/code> predicate sound like they should be intuitive, but they are not. The principles that we wanted in the Standard were:<\/p>\n1. A time period includes its starting point but does not include its end point. We have already discussed this model and its closure properties.<\/p>\n
2. If the time periods are not “instantaneous,” they overlap when they share a common time period.<\/p>\n
3. If the first term of the predicate is an INTERVAL<\/code>, and the second term is an instantaneous event (a <datetime> data type), they overlap when the second term is in the time period (but is not the end point of the time period). That follows the half-open model.<\/p>\n4. If the first and second terms are instantaneous events, they overlap only when they are equal.<\/p>\n
5. If the starting time is NULL<\/code> and the finishing time is a <datetime><\/code> value, the finishing time becomes the starting time, and we have an event. If the starting time is NULL<\/code> and the finishing time is an INTERVAL<\/code> value, then both the finishing and starting times are NULL<\/code>.<\/p>\nPlease consider how your intuition reacts to these results when the granularity is at the YEAR-MONTH-DAY level. Remember that the day begins at 00:00:00 Hrs.<\/p>\n
(today, today) OVERLAPS (today, today) = TRUE\r\n (today, tomorrow) OVERLAPS (today, today) = TRUE\r\n (today, tomorrow) OVERLAPS (tomorrow, tomorrow) = FALSE\r\n (yesterday, today) OVERLAPS (today, tomorrow) = FALSE<\/pre>\nContiguous temporal intervals with DDL<\/h2>\n
Alexander Kuznetsov wrote this idiom for History Tables in T-SQL, but it generalizes to any SQL. It builds a temporal chain from the current row to the previous row with a self-reference. This is easier to show with code:<\/p>\n
CREATE TABLE Tasks\r\n(task_id INTEGER NOT NULL, -- makes sense in your data\r\n task_score CHAR(1) NOT NULL, -- whatever makes sense in your data\r\n previous_end_date DATE, -- null means first task\r\n current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,\r\n CONSTRAINT previous_end_date_and_current_start_in_sequence\r\n CHECK (prev_end_date <= current_start_date)\r\n DEFERRABLE INITIALLY IMMEDIATE,\r\n current_end_date DATE, -- null means unfinished current task\r\n CONSTRAINT current_start_and_end_dates_in_sequence\r\n CHECK (current_start_date <= current_end_date),\r\n CONSTRAINT end_dates_in_sequence\r\n CHECK (previous_end_date <> current_end_date),\r\n PRIMARY KEY (task_id, current_start_date),\r\n UNIQUE (task_id, previous_end_date), -- null first task\r\n UNIQUE (task_id, current_end_date), -- one null current task\r\n FOREIGN KEY (task_id, previous_end_date) -- self-reference\r\n REFERENCES Tasks (task_id, current_end_date));<\/pre>\nWell, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence, and PRIMARY KEY constraints we had in the simple history table schema.<\/p>\n
The two UNIQUE<\/code> constraints will allow one NULL<\/code> in their pairs of columns and prevent duplicates. Remember that UNIQUE<\/code> is NULL<\/code>-able, not like PRIMARY<\/code> KEY<\/code>, which implies UNIQUE<\/code> NOT<\/code> NULL<\/code>.<\/p>\nFinally, the FOREIGN<\/code> KEY<\/code> is the real trick. Obviously, the previous task has to end when the current task started for them to abut, so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy but requires some thought.<\/p>\nThere is just one little problem with that FOREIGN<\/code> KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE<\/code> with some other options. The idea is that you can turn a constraint ON<\/code> or OFF<\/code> during a session so the database can be in a state that would otherwise be illegal. But at the end of the session, all constraints have to be TRUE<\/code> or UNKNOWN<\/code>.<\/p>\nWhen a disabled constraint is re-enabled, the database does not check to ensure any existing data meets the constraints. You will want to hide this in a procedure body to get things started.<\/p>\n
BETWEEN<\/strong><\/p>\nPlease notice that the OVERLAPS<\/code> and BETWEEN<\/code> predicates work with static intervals, but there are also dynamic predicates for data. The LEAD<\/code> and LAG<\/code> operators view the rows as representing points in time or in a sequence, but that is a topic for another article.<\/p>\n