MySQL:- Select Statement:- Equal Sign and Testing for Equality

 

Background

Let us touch on the select statement and how we should take care when using it.

 

Use Select Statement

SQL


set @number1 = 5;
set @number2 = 21;

/*
 *  Use Equal Sign:- Set @answer1 to @number 1 ???
*/
select 
        @answer1 = @number1
    ;

/*
 *    Use Colon and Equal Sign:- Set @answer2 to @number 1
*/
select 
        @answer2 := @number1
;

/*
 *  Display values in @answer1 and @answers2
*/
select 
          @number1 as number1

        , @number2  as number2
         
        , @answer1 as 'answer1 used ='
        
        , @answer2 as 'answer2 used :='
;

Output

Output – Grid

Explanation

  1. Set @number1 to 5
  2. Set @number2 to 21
  3. Set @answer1 to @number1
    • SQL:- select @answer1 = @number1
    • Returned:- null
  4. Set @answer2 to @number1
    • SQL:- select @answer2 := @number1
    • Returned:- 5

 

 

Use Set Statement

SQL


set @number1 = 5;
set @number2 = 21;

/*
    set @answer3 to @number1
*/
set @answer3 = @number1;

SELECT 

         @number1 as 'number 1'
         
       , @number2 as 'number 2'
         
       , @answer3 as 'answer 3'
                
       , CASE 
            when (@answer3 = @number1) then 1
            else 0
        END as 'Is Answer3 equal to Number1'
        
        , CASE 
            when (@answer3 = @number2) then 1
            else 0
        END as 'Is Answer3 equal to Number2'            
		

Output

Output – Grid

Explanation

  1. Set @number1 to 5
  2. Set @number2 to 21
  3. Set @answer3 to @number1
    • SQL:- set @answer3 = @number1
  4. Display Data
    1. number1 is 5
    2. number2 is 21
    3. answer3 is 5
    4. Equality Test, using case statement
      • Is answer3 equal to number1 => Yes
        • What we expected as we copied number1 to answer3
      • Is answer3 equal to number2 => No
        • What we expected as there is no correlation between number2 and answer

Summary

Please be careful with the select statement.

To save data use := ( colon and equal ).

Whereas to compare data use the equal sign ( = ).

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s