Using AND/OR logic in conditions

Using several conditions in a query is a very common scenario because, in many cases, we are required to build a query with a set of filters to get the most accurate results.

Conditions in queries can use the AND or OR operators.

A relation that creates an intersection between conditions is called an AND relation. This means all the common rows for Filter A and Filter B will be retrieved.

Have a look at the following figure. Here, the data retrieved by the AND operator can be described as the dark area in the middle. It is the common intersected area.

For instance, if our query has a set of two conditions: year equal to 2014 AND product name equal to smart phones and no smart phones were sold in 2014, the query won't return any results since the conditions didn't have common rows.

The relation between conditions using an OR operator is a relation that uses a union operator, meaning there is no need for the conditions to have common rows. If any one of the conditions is found valid, data will be retrieved. The data retrieved by the OR operator can be described as the entire area of the following figure:

Getting ready

We are required to create a query that will present the number of cars sold per client country and maker.

The query should retrieve data only for USA and by the maker Fennari.

How to do it...

First, we will create the Client Country filters by dragging the Client Country object to the filter pane. Then, we will use the equal operator, and in the operand choose the value USA.

Once the second object has been dragged, an And operator will appear between the two filters, as shown in the following screenshot:

The relation that has been established between the two filters is an intersection relation, meaning only a common dataset that corresponds to both of the filters can be retrieved. If one of the filters is found to be false (for example, no such models were bought in USA), then the query will not retrieve any data.

We will now run the query. Surprisingly, we will get only one row, as shown in the following screenshot:

We will now change the AND operation to OR in the Query Panel by clicking on the AND operation. By doing so, we will establish a union relationship between the two query filters, meaning if any one of the filters is found to be true, then the query will retrieve that data. Have a look at the following screenshot:

We will run the query. We will notice that there is an increase in the number of rows, as shown here:

The reason for the increase in the number of rows is that we set the OR operator, which retrieves all the Fennari rows or all the USA rows.

Notice that the first row displays data for Japan as Fennari sold cars in Japan too.

How it works...

When creating a set of filters, we will always use an AND/OR operation. While the AND operation is more restrictive and requires that all the conditions be valid, the OR operation requires that at least one of the query filters be valid.

When the query is sent to the database in order to retrieve the relevant data, the filters are executed using the AND/OR operators.

The use of each type of operation depends on the purpose of the query and how the data can be identified. Both operators can be combined in a single set of conditions.

There's more...

A great way to understand how the AND operation works is by demonstrating what it can't do.

In the following screenshot, we have the Client Country condition used twice. The first condition is equal to USA and the second is equal to Japan.

Running the query will result in the following informative message:

This message simply says that the data criteria we tried to retrieve doesn't exist in the database.

We can argue that there is data for both of the countries, but if we take a closer look at the equal operator, we will realize that it means only one value can be matched.

By creating two conditions of the same object, we are actually contradicting the arithmetic meaning of the equal operator, saying it can be equal to two different values. This set of conditions will not work in any kind of reporting tool or even simple Excel filters as the meaning of equal to is universal.