Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

12.1.4. Logical Operators

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common to different SQL database servers, although some servers may return any non-zero value for TRUE.

  • NOT, !

    Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL.

    mysql> SELECT NOT 10;
            -> 0
    mysql> SELECT NOT 0;
            -> 1
    mysql> SELECT NOT NULL;
            -> NULL
    mysql> SELECT ! (1+1);
            -> 0
    mysql> SELECT ! 1+1;
            -> 1
    

    The last example produces 1 because the expression evaluates the same way as (!1)+1.

  • AND, &&

    Logical AND. Evaluates to 1 if all operands are non-zero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

    mysql> SELECT 1 && 1;
            -> 1
    mysql> SELECT 1 && 0;
            -> 0
    mysql> SELECT 1 && NULL;
            -> NULL
    mysql> SELECT 0 && NULL;
            -> 0
    mysql> SELECT NULL && 0;
            -> 0
    
  • OR, ||

    Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.

    mysql> SELECT 1 || 1;
            -> 1
    mysql> SELECT 1 || 0;
            -> 1
    mysql> SELECT 0 || 0;
            -> 0
    mysql> SELECT 0 || NULL;
            -> NULL
    mysql> SELECT 1 || NULL;
            -> 1
    
  • XOR

    Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

    mysql> SELECT 1 XOR 1;
            -> 0
    mysql> SELECT 1 XOR 0;
            -> 1
    mysql> SELECT 1 XOR NULL;
            -> NULL
    mysql> SELECT 1 XOR 1 XOR 1;
            -> 1
    

    a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).


 
 
  Published under the terms of the GNU General Public License Design by Interspire