MySQL SQL Statements and Search Conditions

Implementing Database in MySQL – Part 6

Foreword: In this part of the series, we look at search conditions in MySQL SQL Statements.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 6 of my series, Implementing Database in MySQL. In this part of the series, we look at search conditions in MySQL SQL Statements. You should have read the previous parts of the series before reaching here, as this is the continuation.
We saw the following syntax in the previous part of the series:

    CHECK (expr)

Here, expr stands for expression. It is also a search condition in the sense that it is used to search information in the database. We shall see many practical examples, later on. In this part of the series, we learn the operators of a search condition.

Comparison Operators
In a search condition you may need what is called a comparison operator. There are a good number of them, whose symbols I give here and their meanings:

=         equal to
>         greater than
<         less than
>=     greater than or equal to
<=     less than or equal to
!=        not equal to

An example of a search-condition is:

    price > 0

where price is the column name for price. In the column-definition when creating a table, you would type this in the CHECK constraint as,

    CHECK (price > 0)

This means, each price value in the price column in the table should always be greater than zero (it is not the alphabet O).

Note: Search-conditions are used in many places; not only in the CHECK constraint.

NULL in Conditions
For simplicity, consider a NULL value as an empty cell in a table. Examples of ways you can use NULL in conditions are as follows, where salary is the name of the column:

Salary IS NULL
Salary IS NOT NULL

Here, NULL, NOT, and IS are reserved words.

Logical Operators
Another kind of operators falls in the class of logical operators. The names of the operators are, AND, OR and NOT. These three words are reserved words.

You may be dealing with the customer table and you want rows whose CustonerIDs are greater than 5 and at the same time less than 10; you would type the following:

    (CustomerID > 5) AND (CustomerID < 10)

The use of the AND operator can be that simple. Looking at this piece of code, there is an expression on the left of the AND operator in brackets, and an expression on the right of the operator in brackets. You use brackets for expressions that you want to be computed first. Always use brackets like this, otherwise the order in which the whole expression will be computed will not be what you want, and you will have wrong answers. This use of brackets is applicable to the OR and NOT operators, as well.

If from the customer table, you want rows whose CustomerIDs are less than 5 OR greater than 10, you would type:

        (CustomerID < 5) OR (CustomerID > 10)

If you want rows whose CustomerIDs are not less than 5, you would type:

    NOT (CustomerID < 5)

There are other reserved words used in search-conditions. You will see them as we go along in the series. Let us continue in the next part.

Chrys

Broad Network

Related Articles

MySQL SQL Statements and Search Conditions

Implementing Database in MySQL – Part 6

Introduction

Related Links

Comments