Logical Comparisons

Introduction

 

Logical Operators

As a review, to compare two values for equality, use the = operator. Its formula is:

Value1 = Value2

The equality operation is used to find out whether two values are the same. From the above formula, the SQL interpreter would compare Value1 and Value2. If Value1 and Value2 are the same, the comparison produces a TRUE result. If they are different, the comparison renders FALSE.

As opposed to equality, to find out if two values are not equal, use the <> operator. Its formula is:

Value1 <> Value2

The <> is a binary operator (like all logical operators) that is used to compare two values. The values can come from two variables as in Variable1 <> Variable2. Upon comparing the values, if both hold different values, the comparison produces a TRUE. Otherwise, the comparison renders FALSE or a null value.

Like the Visual Basic language, in SQL, the comparison for a lower value is performed using the < operator. Its formula is:

Value1 < Value2

The value held by Value1 is compared to that of Value2. As it would be done with other operations, the comparison can be made between two variables, as in Variable1 < Variable2.

As done in the Visual Basic language, to compare for lesser or equal value in SQL, use the <= operator whose formula is:

Value1 <= Value2

If both Value1 and Value2 are the same, the result is true or positive. If the left operand, in this case Value1, holds a value lower than the second operand, in this case Value2, the result is still true. If the value of Value1 is strictly higher than that of Value, the comparison produces a FALSE result.

Like the Visual Basic language, the comparison for a strict greater value is performed with the > operator. Its formula is:

Value1 > Value2

Both operands, in this case Value1 and Value2, can be variables or the left operand can be a variable while the right operand is a constant. If the value on the left of the > operator is greater than the value on the right side or a constant, the comparison produces a true or positive value. Otherwise, the comparison renders false or null.

Like the Visual Basic language, the SQL supports the comparison for greater or equality using the >= operator. Its formula is:

Value1 >= Value2

The comparison is performed on both operands: Value1 and Value2. If the value of Value1 and that of Value2 are the same, the comparison produces a true or positive value. If the value of the left operand is greater than that of the right operand, the comparison produces true or positive also. If the value of the left operand is strictly less than the value of the right operand, the comparison produces a false or null result. 

A conditional statement is an expression you formulate to evaluate it. Most of the time,  the statement is written so that, when evaluated, it can produce a result of true or false, then, depending on the outcome, you can take action. A condition is usually written as simple as possible to make it clear to you and the SQL interpreter. Although the interpreter never gets confused, if you create a difficult statement, you may receive an unpredictable result.

In the next few sections, we will review the keywords and formulas that Transact-SQL provides to help you formulate clear expressions. Expressions usually start with a keyword, followed by the expression itself. After the expression, you can tell the interpreter what to do. The statement may appear as follows:

Keyword Expression
	Statement

With the above formula, we will always let you know what keyword you can use, why, and when.

After the expression, you can write the statement in one line. This is the statement that would be executed if/when the Expression of our formula is satisfied. In most cases, you will need more than one line of code to specify the Statement. As it happens, the interpreter considers whatever comes after the Statement as a unit but only the line immediately after the Expression. To indicate that your Statement covers more than one line, start it with the BEGIN keyword. Then you must use the END keyword to indicate where the Statement ends. In this case, the formula of a conditional statement would appear as follows:

Keyword Expression
BEGIN
	Statement Line 1
	Statement Line 2
	
	Statement Line n
END

You can still use the BEGIN...END combination even if your Statement covers only one line:

Keyword Expression
BEGIN
	Statement
END

Using the BEGIN...END combination makes your code easier to read because it clearly indicates the start and end of the Statement.

Probably the primary comparison you can perform on a statement is to find out whether it is true. This operation is performed using an IF statement in Transact-SQL. Its basic formula is:

IF Condition
	Statement

When creating an IF statement, first make sure you provide a Condition expression that can be evaluated to produce true or false. To create this Condition, you can use variables and the logical comparison operator reviewed above.

When the interpreter executes this statement, it first examines the Condition to evaluate it to a true result. If the Condition produces true, then the interpreter executes the Statement. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim Connect As SqlConnection = _
  		New SqlConnection("Data Source=(local);" & _
      				  "Database='Exercise';" & _
      				  "Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
       		 New SqlCommand("DECLARE @DateHired As DateTime, " & _
       				"@CurrentDate As DateTime " & _
       				"SET @DateHired = '1996/10/04' " & _
       				"SET @CurrentDate = '2007/04/11' " & _
       				"IF @DateHired < @CurrentDate " & _
     "SELECT N'You have the experience required for a new promotion in this job'", _
       				Connect)
        Connect.Open()
        Dim rdr As SqlDataReader = Cmd.ExecuteReader()

        While rdr.Read()
            MsgBox(rdr(0))
        End While

        rdr.Close()
        Connect.Close()
    End Sub
End Class

This would produce:

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives. Consider the following code:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim Connect As SqlConnection = _
	New SqlConnection("Data Source=(local);" & _
     			  "Database='Exercise';" & _
     			  "Integrated Security=yes;")
    Dim Cmd As SqlCommand = _
    	 New SqlCommand("DECLARE @DateHired As DateTime," & _
       			" @CurrentDate As DateTime;" & _
       			"SET @DateHired = '1996/10/04'" & _
       			"SET @CurrentDate = '2007/04/16';" & _
       			"IF @DateHired > @CurrentDate" & _
     " PRINT 'You have the experience required for a new promotion';", _
       			Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Cmd.ExecuteReader()

    While rdr.Read()
        MsgBox(rdr(0))
    End While

    rdr.Close()
    Connect.Close()
End Sub

In case the expression to examine produces a false result, there is nothing to do. Sometimes this will happen.

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result

	WHEN Value_n THEN Result
}

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim Connect As SqlConnection = _
 	New SqlConnection("Data Source=(local);" & _
     			  "Database='Exercise';" & _
     			  "Integrated Security=yes;")
    Dim Cmd As SqlCommand = _
    	 New SqlCommand("DECLARE @CharGender Char(1)," & _
       		        "@Gender Varchar(20);" & _
       		        "SET @CharGender = 'F';" & _
       		        "SET @Gender = " & _
       		        "CASE @CharGender" & _
       			"    WHEN 'm' THEN 'Male'" & _
       			"    WHEN 'M' THEN 'Male'" & _
       			"    WHEN 'f' THEN 'Female'" & _
       			"    WHEN 'F' THEN 'Female'" & _
       			"END;" & _
       		"SELECT N'Student Gender: ' + @Gender;", _
       			Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Cmd.ExecuteReader()

    While rdr.Read()
        MsgBox(rdr(0))
    End While

    rdr.Close()
    Connect.Close()
End Sub

Here is the result of executing it:

In most cases, you may know the only types of values that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim Connect As SqlConnection = _
 	New SqlConnection("Data Source=(local);" & _
     			"Database='Exercise';" & _
     			"Integrated Security=yes;")
    Dim Cmd As SqlCommand = _
    	New SqlCommand("DECLARE @CharGender Char(1)," & _
       		"@Gender Varchar(20);" & _
       		"SET @CharGender = 'H';" & _
       		"SET @Gender = " & _
       		"CASE @CharGender" & _
       		"    WHEN 'm' THEN 'Male'" & _
       		"    WHEN 'M' THEN 'Male'" & _
       		"    WHEN 'f' THEN 'Female'" & _
       		"    WHEN 'F' THEN 'Female'" & _
   		"ELSE 'Unknown'" & _
       		"END;" & _
       		"SELECT N'Student Gender: ' + @Gender;", _
       		Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Cmd.ExecuteReader()

    While rdr.Read()
        MsgBox(rdr(0))
    End While

    rdr.Close()
    Connect.Close()
End Sub

This would produce:

If you do not produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.

As done in the Visual Basic language, to examine a condition and evaluate it before taking action in SQL, you can use the WHILE operator. The basic formula of this statement is:

WHILE Expression 
    Statement

When implementing this statement, first provide an Expression after the WHILE keyword. The Expression must produce a true or a false result. If the Expression is true, then the interpreter executes the Statement. After executing the Statement, the Expression is checked again. AS LONG AS the Expression is true, it will keep executing the Statement. When or once the Expression becomes false, it stops executing the Statement. This scenario can be illustrated as follows:

Here is an example:

DECLARE @Number As int

WHILE @Number < 5
	SELECT @Number AS Number
GO

To effectively execute a while condition, you should make sure you provide a mechanism for the interpreter to get a referenced value for the condition, variable, or expression being checked. This is sometimes in the form of a variable being initialized although it could be some other expression. Such a while condition could be illustrated as follows:

This time, the statement would be implemented as follows:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='Exercise';" & _
     			   "Integrated Security=yes;")
    Dim Cmd As SqlCommand = _
    	  New SqlCommand("DECLARE @Number As int;" & _
       			 "SET @Number = 1;" & _
       			 "WHILE @Number < 5" & _
       			 "    BEGIN" & _
       			 "        SELECT @Number AS Number;" & _
       			 "        SET @Number = @Number + 1;" & _
       			 "    END;", _
       			 Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Cmd.ExecuteReader()

    While rdr.Read()
        MsgBox(rdr(0))
    End While

    rdr.Close()
    Connect.Close()
End Sub   

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result; in other words, something is not half true or half false or "Don't Know": either it is true or it is false).

Like the Visual Basic language, in SQL, a Boolean variable can hold a TRUE value. The value is also considered as 1. By contrast, if something does not hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

After you have declared a variable, the SQL interpreter reserves a space in the computer memory for it but does not put anything in that memory space. At that time, that area of memory does not hold a significant value. Also at that time, the variable is considered null.

Here is note to be careful about: when a variable is said to hold a null value, it does not mean its value is 0. It does not even mean that the variable's memory space is empty. It actually means that we cannot clearly determine the current value that the variable is holding.

To support the null value, Transact-SQL provides a constant named NULL. The NULL constant is mostly used for comparison purposes. For example, you can use an IF statement to check the nullity of a variable.

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example:

REM Square Calculation
Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim Connect As SqlConnection = _
 	New SqlConnection("Data Source=(local);" & _
     			  "Database='Exercise';" & _
     			  "Integrated Security=yes;")
    Dim Cmd As SqlCommand = _
    	 New SqlCommand("DECLARE @Side As Decimal(10,3)," & _
       			" 	 @Perimeter As Decimal(10,3)," & _
       			" 	 @Area As Decimal(10,3);" & _
       			"SET @Perimeter = @Side * 4;" & _
       			"SET @Area = @Side * @Side;" & _
       			"IF @Side IS NULL" & _
       			"    PRINT 'A null value is not welcome'" & _
       			"ELSE IF @Side > 0" & _
       			"    BEGIN" & _
       			"        SELECT @Side AS Side;" & _
       			" 	 SELECT @Perimeter AS Perimeter ;" & _
       			"        SELECT @Area AS Area;" & _
       			"    END;" & _
       			"ELSE" & _
       			"    PRINT 'You must provide a positive value';", _
       			Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Cmd.ExecuteReader()

    While rdr.Read()
        MsgBox(rdr(0))
    End While

    rdr.Close()
    Connect.Close()
End Sub

To avoid having a NULL value, you can either initialize the variable or you can assign it a value. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    REM Square Calculation
    Private Sub btnCalculate_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnCalculate.Click
        Dim Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     				  "Database='Exercise';" & _
     				  "Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
      		New SqlCommand("DECLARE @Side As Decimal(10,3)," & _
               		" 	 @Perimeter As Decimal(10,3)," & _
              		" 	 @Area As Decimal(10,3);" & _
       			"SET @Side = 48.126;" & _
       			"SET @Perimeter = @Side * 4;" & _
       			"SET @Area = @Side * @Side;" & _
       			"IF @Side IS NULL" & _
       			"    PRINT 'A null value is not welcome'" & _
       			"ELSE IF @Side > 0" & _
       			"    BEGIN" & _
       			" 	SELECT @Side, @Perimeter, @Area;" & _
       			"    END;" & _
       			"ELSE" & _
       			"    PRINT 'You must provide a positive value';", _
       			Connect)

        Connect.Open()
        Dim rdr As SqlDataReader = Cmd.ExecuteReader()

        While rdr.Read()
            txtSide.Text = rdr(0)
            txtPerimeter.Text = rdr(1)
            txtArea.Text = rdr(2)
        End While

        rdr.Close()
        connect.Close()
    End Sub
End Class

This would produce:

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.

1. Create an Windows Application that gets the length and the width of a rectangle, then it calculates the perimeter and the area. The calculation must be performed by the SQL interpreter and not the Visual Basic compiler
2. Create an Windows Application that gets the length, the width, and the height of a box, then it calculates the area and the volume. The calculation must be performed by the SQL interpreter and not the Visual Basic compiler
3. Create an Windows Application that gets the radius of a circle, then it calculates the perimeter and the area. The calculation must be performed by the SQL interpreter and not the Visual Basic compiler
4. Create an Windows Application that gets the radius of a sphere, then it calculates the diameter, the circumference, the total area, and the volume. The calculation must be performed by the SQL interpreter and not the Visual Basic compiler
5. Create an Windows Application that gets the radius and the height of a cylinder, then it calculates the diameter of the base, the circumference of the base, the area of the base, the lateral area of the cylinder, the total area of the cylinder, and the volume. The calculation must be performed by the SQL interpreter and not the Visual Basic compiler