LibreOffice Base SQL: Logical Operators

Today, I will continue the discussion of SQL queries by discussing the the logical operators available to narrow or broaden a search. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at

Rehashing AND and OR

In the post on Updating and Deleting Records, I wrote about using AND and OR to combine multiple conditions for a WHERE clause. This allows us to answer questions based on more than one field in the table. As I showed in the post, this allows us to narrow or broaden our target data closer to what we want.

Order of Precedence

Just like in mathematics, the logical operators in SQL have an order of precedence. What that means is that conditions related to certain operators are evaluated before the others regardless of their order. Here is the operator order

  • Brackets ()
  • NOT
  • AND

Note that AND has greater precedence than OR. This means that AND conditions are evaluated before OR conditions. Let say we need a list of all the married members in New Town and New York. On first instinct, you might try the following SQL:

SELECT “FirstName”, “LastName”, “Married”
FROM “TeamMembers”
WHERE “City” = ‘New Town’
OR “City” = ‘New York’
AND “Married” = True;

However, because of the order of precedence, this evaluates to a list of team members in New Town and married members in New York. I added the married field here so you will see the unmarried team member that shows up in this query.

To correct this query so we get what we wanted, we just need to add brackets [()].

SELECT “FirstName”, “LastName”, “Married”
FROM “TeamMembers”
WHERE (“City” = ‘New Town’
OR “City” = ‘New York’)
AND “Married” = True;

This groups the two city conditions together, giving us the correct results.

Other Logical Operators

There are four other logical operators to consider today. We will cover ALL, ANY, and SOME at a later time. The four we will go over today are NOT, BETWEEN, LIKE, and IN.


NOT negates or reverses the condition of the statement. The truth table for the NOT operator is

Statement Result
NOT true False
NOT False True
SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE NOT “City” = ‘New York’;

This would give us a list of team members who are not in New York. Be aware that this query produces the same results as

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “City” <> ‘New York’;

which just demonstrates there is more than one way to do something in SQL.


BETWEEN allows you to pull from a range of values. This is the same as asking whether something is greater than or equal to (>=) one value, and less than or equal to (<=) another value. In fact, you could use these two conditional operators and get the same results as BETWEEN, but as you will see, BETWEEN is shorter and more compact.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
‘1990-01-01’ AND ‘2010-01-01’;

This would give use the names of all team members hired between January 1990 and January 2010. BETWEEN is inclusive, which means that it includes the start and end values. So, if someone was born January 1, 1990, their name is included in the list. You can also negate BETWEEN with a NOT.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
‘1990-01-01’ AND ‘2010-01-01’;


LIKE is used to match patterns in strings of characters by using the wild card characters underscore (_) and percent (%). Underscore is a place holder for any single character, but the percent is a place holder for one or more characters. Let’s do some examples.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” LIKE ‘_enson’;

This query includes the two last names Benson and Jenson. We can create a broader search with the percent wild card.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” LIKE ‘%on’;

This query includes not only Benson and Jenson but Hamelton and Benton. LIKE is handy when you are not exactly sure just what it is you are looking for or how it is spelled.

We can use not as well to get a list of names who don’t end in “on.”

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” NOT LIKE ‘%on’;

This query gives us a list of names like Walker, Smith, and Donovan that do not end in “on.”


IN checks to see if the value is in a set of possible values. We can manually create the search set or use an internal query to create the set. For today, we will create a manual set, but later we will discuss internal queries.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName”
IN (‘Walker’, ‘Smith’, ‘Donovan’);

This query gives you all the Walkers, Smiths, and Donovans. Note the set in brackets. Of course, we can negate this with a NOT to get a list of everyone except the Walkers, Smiths, and Donovans.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName”
NOT IN (‘Walker’, ‘Smith’, ‘Donovan’);

Logical operators help us to asks better questions when creating a query. Just like in mathematics, the operators are controlled by an order of precedence. AND and OR allow us to join more than one condition to the query. NOT negates any condition it is associated with. To select from a range of values, we can use BETWEEN. LIKE allows us to search the database using a pattern. Selecting only records matching a set of values is done using IN.

In my next post, we will discuss arranging the result set in a certain order.

Written with StackEdit.

LibreOffice Base SQL: Basic Queries

Today, I begin the first of several posts on creating SQL queries in LibreOffice. I will start with the basics and work my way up to the more complicated. In each case, I will use the “TeamProject” database for my example queries. See the post at to set up the database on your system.

SELECT Command Basics

The SELECT command is used to gather information about the data contained in a database. A SELECT command is also known as a query. The purpose of a query is to answer a question. The most basic form of the SELECT statement is

SELECT field1, field2,..fieldx FROM tablename;

The SELECT command lists a set of fields you want to extract FROM a certain table. Let’s look at a couple of examples. The results we get from a query is called a dataset. The dataset only contains the information you request. This is handy, because often you don’t need all the information the table.

SELECT “FirstName”, “DateOfHire” FROM “TeamMembers”;

This will display all the first names and hire dates for all the records in the “TeamMembers” table.

SELECT * from "TeamMembers";

The asterisks (*) is a special wild card that allows you to display all the fields. This query would display all the fields for all the records.

Distinct Fields

Sometimes, you just need to extract certain information from a table. For example, how many cities do the team members represent? The temptation is to enter the query

SELECT “City” FROM “TeamMembers”;

but this would show the city for every record, creating repetition. The correct way to do this is to add the keyword DISTINCT to the command.

SELECT DISTINCT “City” FROM “TeamMembers”;

This will display just one record for each city. Any repetition is ignored. You can use DISTINCT with more than one field.

SELECT DISTINCT “City”, “LastName” FROM “TeamMambers”; 

In this case, the query will ignore any records where the city and last name repeat. To see the records unfiltered, run the command without the DISTINCT keyword.

Adding WHERE

To filter our queries even more, we can use the WHERE clause. Just as we did with the UPDATE statement, we can use the WHERE clause to better answer the question we are asking. Using what we already known about the WHERE clause, we can do the following:

SELECT * FROM “TeamMembers” 
    WHERE “DateOfHire” > '2000-01-01'; 

This command will display a list of team members who were hired after January 1, 2000. Running this command will display all the team members except for 2.

SELECT * FROM “TeamMembers” 
    WHERE “DateOfHire” < ‘2000-01-01’;

Swap the sign to see the other two who were hired before January 1, 2000.


Sometimes, you want to reference a field or group of fields by a different name. Aliases allow you to create an “alias” name in the resulting dataset. If for some reason, we needed the field “City” named “Place”, we could execute the following command.

SELECT “City” AS “Place” FROM “TeamMembers”; 

The AS clause defines the alias.

The real power of aliases comes when we combine more than more field into one, as in the following example.

SELECT “LastName” || ', ' || “FirstName” AS “FullName” 
    FROM “TeamMembers”; 

The double piping symbols (||), are used to concatenate, or join, multiple fields together. Here we concatenate the last name to a comma and space, then concatenate the result to the first name. We give the new concatenated string the alias “FullName”.

Altogether Now

Let me leave you with one last bonus query. This one will use everything discussed in this post except for DISTINCT. I couldn’t find a way to add DISTINCT to the mix without it feeling redundant.

SELECT “City”, 
    “LastName” || ', ' || “FirstName” AS “FullName” 
    FROM “TeamMembers” 
    WHERE “City” = 'New York'; 

In this query, we select multiple fields, use an alias, and a WHERE clause. This shows how you can combine different pieces to begin to create more complicated queries.

Next time, we will revisit the WHERE clause by examining the operators that allows us to create more complex filters.

Written with StackEdit.

LibreOffice Base SQL: Inserting Data Records

In most cases you will never use SQL to insert data into Libreoffice Base tables as this is mostly done through forms. However, it helps to know how the SQL works for those occasions when you get error messages when trying to input data through a form, and there are also times when you just need a small table with a few records for selection in a form. Creating a form in the latter case might take more time than just entering the data through SQL. Today, I am going to show you the basics of inserting records into a table. Continue reading

LibreOffice Version Control

While working on a manuscript, it is a good idea to keep different versions of the document (1st draft, 2nd draft, 3rd draft, etc.) in case you need to go back to an earlier version of your work. Many times this is accomplished by saving the document under a new name. Another approach is using some kind of version control software that allows you to track changes. However, LibreOffice does have a basic version control system built in and contains all the versions in one file. Continue reading