SQL Disjunctions

Fundamentals of Disjunctions

Introduction

A logical disjunction is a technique of selecting records that match any of some provided values. This means that you must provide (at least) two values as a filter and the database engine would select only the records that include either of those values. Once again, Microsoft Access provides various tools to perform data analysis that includes logical disjunction.

One of the primary differences between a disjunction and a conjunction is that a disjunction can be applied to different values of the same field or values of different fields while a conjunction should always be used on different fields whether they use the same type or not.

As seen for conjunctions, Microsoft Access provides many options to create and execute a logical disjunction.

Filtering by Value for Logical Disjunctions

The filtering by value used in previous lessons is one of the most convenient ways to create a logical disjunction. To create this criterion, display the filter by value window. To apply a logical disjunction, put the check mark on at least two values excluding the (Select All) option.  This means that the values of a logical disjunction should be the check boxes of the same field in filtering by value. Here is an example:

After making the selections, to apply the filter, click OK. This would result in the records that use either of the selected values.

To remove the filter, we saw that you could click the Toggle Filters button on the Ribbon or the Filtered button on the table, query, or form. As an alternative, you can display the window again, click the (Select All) option and click OK.

A Logical Disjunction in a Query Design

If you are working in the Design View of a query, to create a single filter, use the Criteria box of the desired field. To let you create a logical disjunction, the bottom side of the Design View of a query provides an additional row named or. Based on this, to create a logical disjunction, set the first filter in the Criteria box for one field. Click the or box for the same field and type the second criterion. This means that the sub-expressions of a logical disjunction should be on different rows (Criteria and Or) but for the same column. Here is an example:

Once you are ready, preview the query in the Datasheet View.

Filtering by Form for Logical Disjunctions

Filtering by Form is another very convenient tool to analyze data using a disjunction. To filter by form, open the table, query, or form in their regular view. In the Sort & Filter section of the Ribbon, click Advanced and click Filter By Form. A window will appear with two tabs at the bottom. One tab is labeled Look For and the other tab is labeld Or:

To create a disjunction when filtering by form, click the combo box under the desired field and select the desired value. Here is an example:

Then, in the bottom side of the window, click the Or tab. Click the combo box under the same field and select the other value. Here is an example:

After making the selections, apply the filter. The resulting list would include the records that use either of the selected values. Here is an example:

A Logical Disjunction on an Object Filter

To set a conditional disjunction on a table, an existing query, or a form/report that already has a record source, display the table or query in Datasheet View, a form in Form View or in Layout View, or a report in Report View or in Layout View. In the Sort & Filter section of the Ribbon, click Advanced and click Advanced Filter/Sort... Create the disjunction as we saw for the Design View of a query, using the Criteria and the Or boxes of the desired field.

An Expression for a Logical Disjunction

Introduction

The Boolean operator used to create a logical disjunction is named OR. Its expression uses the following formula:

sub-expression1 OR sub-expression2

As you can see, you create a sub-expression on each side of the OR operator. Each sub-expression is a logical operation, the same type we intrduced in Lesson 34 as:

column-name operator value

So an expression for a logical disjunction is actually:

column-name1 operator value1 OR column-name2 operator value2

To make the expression easy to read, you should put each sub-expression in parentheses.

Creating a Disjunction Criterion in the Design View

To create a filtered disjunction for a table, a form, or a report in Design View, access its Property Sheet. Click Filter and type an expression as seen above. Here is an example:

Remember that if you want the filter to apply immediately when you display the object in its regular view, set the Filter On Load property to Yes.

Logical Disjunctions in the SQL

As seen for other filters, in the SQL, a logical disjunction is created in the WHERE clause as follows:

WHERE expression1 OR expression2

Remember that each expression is in the form:

field-name Operator Value

 This means that you must specify a column, the operator that performs the filtering, and the value applied to the column. Here is an example:

SELECT PropertyNumber,       City,
       Locality,
       PropertyType,
       Condition,
       MarketValue
FROM   Properties
WHERE  Locality="Montgomery County" OR Locality="Prince George's County";

Creating Many Logical Disjunctions

Introduction

So far, we have created only two expressions for a disjunction. Indeed, you can include as many expressions as you want. You have many options. For the result, the database engine will include all records that include any of the values.

Filtering by Value

To create a logical disjunction that includes more than two expressions, if you are filtering by value, include a check mark to each of the values of the field. After making your selections, click OK.

Filtering by Form

To create a logical disjunction with many expressions, if you are filtering by form, in the combo box of the desired field, select the desired value. Here is an example:

In the bottom side of the window, click the Or tab and select the second value in the combo box of the intended field. Here is an example:

Keep clicking Or in the bottom section of the window and selecting the additional values. Here is an example:

After making the selections, apply the filter.

The SQL and Logical Disjunctions

To create a logical disjunction with many expressions in SQL, add as many logical expressions as you want and separate them with OR operators. Although the parentheses are not required, they can make your expression easier to read.

Here is an example:

SELECT PropertyNumber,
       City,
       PropertyType,
       Condition,
       MarketValue
FROM   Properties
WHERE (City = "Rockville") OR (City = "Bethesda") OR (City = "Silver Spring")

Logical Disjunctions and Different Fields

Introduction

So far, we have applied all our logical disjunctions to only one field. This is not a rule. That technique is used when you want to view records that include only some specific values of one field. In reality, you can create a disjunction that involves as many fields as you want. You can create the logical expression in filtering by form or in Design View.

Filtering by Form

To create a logical disjunction that involves many fields, if you are filtering by form, in the combo box of the desired field, select the desired value. In the bottom side of the window, click the Or tab. In the top side of the window, click a cell below another column header, then click the arrow of its combo box and select a value. After making the selections, apply the filter.

Filtering by Design

To create a many-fields-based logical disjunction in the Design View of a query, an Object Filter, or the Query Builder, in the bottom part of the window, the Criteria box for one of the field and type a logical expression. Click the Or box for another field and type the desired expression.

Using SQL

In the SQL, to create a logical disjunction for many fields, create a logical expressions for each field and the desired value. Separate the expressions with OR operators. Here is an example:

SELECT RoomNumber,
       RoomType,
       BedType, 
       DailyRate, 
       RoomStatus
FROM   Rooms
WHERE  RoomType = "Bedroom" OR BedType = "Queen";

Remember that, to make your code easy to read, you should include each expression in parentheses.

Topics on Creating and Using Disjunctions

Logical Disjunctions IN Filters

An IN operation is just a variant of a logical disjunction that involves only one field. When creating an IN filter, the values you specify in the parentheses play the same role as if the name of the field equal to one of the values were combined with the OR operator. As a result, this statement:

SELECT PropertyNumber,
       City,
       PropertyType,
       Condition,
       MarketValue
FROM Properties
WHERE (City = "Rockville") OR (City = "Bethesda") OR (City = "Silver Spring")

is the same as:

SELECT PropertyNumber,
       City,
       PropertyType,
       Condition,
       MarketValue
FROM Properties
WHERE City IN ("Rockville", "Bethesda", "Silver Spring");

Logical Disjunctions and Different Values

In a logical disjunction, you can use expressions that use different types of values. This is possible because, as mentioned above, you can use different fields. In the same way, you can include an expression that checks for null or not null values. The expression is formulated normally. The final result will include the records that are null and those that include the other specified values.

Logical Expressions and/or Functions in a Disjunction

Each sub-expression of a logical disjunction can be a logical operation that can produce True/False or any value you want. A sub-expression can also be produced by a function. Here is an example:

Sorting Records

When creating a logical disjunction, you can sort the records using any of the fields, whether it is included in the expression or not.

Here is an example:

SELECT States.StateName,
       States.AreaSqrMiles,
       States.AdmissionUnionDate,
       States.AdmissionUnionOrder, 
       States.Region
FROM States
WHERE (((Left([StateName], 1))="M")) OR (((Year([AdmissionUnionDate])) Between 1815 And 1855))
ORDER BY AreaSqrMiles;

Practical Learning: Ending the Lesson

1. Close Microsoft Access
2. When asked whether you want to change, click Yes