Home » SQL » SQL logical Operator – AND, OR & NOT

SQL logical Operator – AND, OR & NOT

The SQL logical operator are those that allows you to combine Boolean expressions. It returns value absed on TRUE and FALSE condition. in SQL we have 3 kind of Logical Operator

Definitions of 3 kind of SQL Logical Operator

AND

AND is a logical operator that allows to combine two Boolean expressions. It returns TRUE only when both expressions evaluation is TRUE.

OR

OR is a logical operator that allows to combine two Boolean expressions. It returns TRUE when either of the conditions evaluates to TRUE. (any one condition should be true to return value)

NOT

NOT is a logical operator that allows to returns value when the conditions is TRUE.

The AND & OR operators are used to filter records based on more than one condition, it used in WHERE Clause:

  • The AND operator displays a record result, if all the conditions within AND are TRUE.
  • The OR operator displays a record result, if any of the conditions within OR is TRUE.
  • The NOT operator displays a record result, if the condition(s) is NOT TRUE.

Syntax:

AND:

WHERE (condition1) AND (condition2) AND (condition3 ...);

OR:

WHERE (condition1) OR (condition2) OR (condition3 ...);

NOT:

WHERE NOT (condition1);

Example #1: AND Operator

Below example return the record of rows from Customer_Tbl when the Product_Category and Product_Base_Margin both condition will be true or Matched with Data

SELECT
       *
FROM
       [dbo].[Customers_Tbl]
WHERE
      ([Product_Category] = 'Furniture') AND ([Product_Base_Margin] > 0.70)

Result:

Example #2: OR Operator

Below Query shows the records Margin less than 40% and greeter then 80% only. You can treat this Example if you need to get the Number range of data from tables records.

SELECT
         Region, [Product_Base_Margin]
FROM
        [dbo].[Customers_Tbl]
WHERE
        ([Product_Base_Margin] < 0.40) OR ([Product_Base_Margin] > 0.80)

Result:

Example #3: NOT Operator

Below SQL statement selects all fields from Region is NOT equal to “West”

SELECT
       Product_Category, Region
FROM
       [dbo].[Customers_Tbl]
WHERE
       NOT Region = 'West'
GROUP BY 
       Product_Category, Region

Result:

Example #4: Combined of AND, OR and NOT Operator

Below SQL query have 2 condition on Product_Category with OR condition and excluding West in region. Both applied on AND logical operator.

Here AND logical operator will work if NOT and OR condition will be TRUE, OR it will matched with records.

SELECT
       Product_Category, Region
FROM
       [dbo].[Customers_Tbl]
WHERE
       NOT(Region = 'West') AND((Product_Category='Furniture') OR (Product_Category='Technology'))
GROUP BY 
       Product_Category, Region

Result: