SQL Operators (OR, AND, NOT)

What is an Operator in query?

Definition: Operators are nothing but a way which is used to filter records in sql query. You may use conditions with operator to add filter in your sql statement.

  • You may also use multiple operators in single sql statement as well.

    There are mainly three type of operators used in sql query as following.

  • AND
  • OR
  • NOT
    Important: It is important that you need to use WHERE clause with all above operators in your sql query. Without WHERE clause, you can’t use any of operator.

Why Operators needed?

Sometime it may happen that you need to filter your records with conditions based on your requirements. In that case, you may easily use operators to add filter and condition which give you desire result set based on your need.

  • AND Operator
    As name indicated, AND operator only display records if all the conditions defined in sql query are true, otherwise it won’t display any records if any one condition does not true.

    Syntaxt:

    SELECT ColumnName1,ColumnName2,ColumnName3,ColumnName4, ...
    FROM TABLE_NAME
    WHERE CONDITION 1
    AND CONDITION2
    AND CONDITION3

    Note: you may add more conditions also as per your need.

    Example:

    SELECT * FROM Employee
    WHERE Id = 1

    Id Employee Code First Name Last name Gender Birth Date
    1 EMP_1001 John Peter Male 10-11-1982

    In above sql query, we have defined only one condition and that is “Id=1” so it return only one record set which has Id=1 value.

    SELECT * FROM Employee
    WHERE FirstName = 'Josh' AND LastName = 'Wills';

    Id Employee Code First Name Last name Gender Birth Date
    2 EMP_1002 Josh Wills Male 09-07-1980

    In above sql query, we have defined two conditions and those are FirstName='Josh' AND LastName='Wills' so it return only one record set because condition is true for one result set only with first name and last name.

  • OR Operator

    OR operator display records if any of the conditions defined in sql query are true. Let’s look into syntax and example to understand it better

    Syntaxt:

    SELECT ColumnName1,ColumnName2,ColumnName3,ColumnName4, ...
    FROM TABLE_NAME
    WHERE CONDITION 1
    OR CONDITION2
    OR CONDITION3

    Note: you may add more conditions also as per your need.

    Example:

    SELECT * FROM Employee
    WHERE FirstName = 'John' OR LastName = 'Wills';

    Id Employee Code First Name Last name Gender Birth Date
    1 EMP_1001 John Peter Male 10-11-1982
    2 EMP_1002 Josh Wills Male 09-07-1980

    In above sql query, we have defined two conditions and those are FirstName='John' OR LastName='Wills' so it return record set if any row contain FirstName=”John” or LastName=”Wills”. As we can see there are two records matching those values so it return two result set.

  • NOT Operator

    When condition doesn’t get true, it display result set with NOT operator. Let’s look into syntax and example as below.

    Syntaxt:

    SELECT ColumnName1,ColumnName2,ColumnName3,ColumnName4, ...
    FROM TABLE_NAME
    WHERE NOT CONDITION

    Example:

    SELECT * FROM Employee
    WHERE NOT Id = 3

    Id Employee Code First Name Last name Gender Birth Date
    1 EMP_1001 John Peter Male 10-11-1982
    2 EMP_1002 Josh Wills Male 09-07-1980

    In above sql query, we have defined only one condition with NOT operator that is NOT Id=3. That means, sql query will execute and display all records except one row which has Id=3 value.

    Note: You may also use all above three operators in single query. Look into following example which has all operators in single query.

    SELECT * FROM Employee
    WHERE (FirstName = 'John' OR FirstName = 'Josh') AND Gender = 'Male'

    As per above sql query which has all operators, it will display following result set. Here, it will check Male as Gender and again check FirstName with two values “John” and “Josh”.

    Id Employee Code First Name Last name Gender Birth Date
    1 EMP_1001 John Peter Male 10-11-1982
    2 EMP_1002 Josh Wills Male 09-07-1980