Logical Operators

  • Logical Operators are having  lowest operator precedence in SQL statements.
  • Logical operators are

    AND
    OR
    NOT
    IN
    BETWEEN
    ALL
    ANY
    LIKE
    EXISTS
    IS NULL
    DISTINCT

It would be easy if we practically work on the above mentioned operators.

Let us select all the values from apple table.

Now we are going to perform AND operation which would return only if both the conditions are TRUE.

Here apple table should be true ie. name and weight conditions should be True or Pass.


OR

  •  returns results when either of the condition is True or successful.

NOT

  • It is negation, when the negative condition is true then SQL query will return values.

 IN

  • operator is used to return all rows which contains (‘Granny Smith’, ‘Red Apple’), you can have any number of column values.

 BETWEEN

  • It returns rows between one column value to another column value.

if you look below you can understand


 ALL
  • operator will loop through all the values that the sub query returns to the main query.

ANY

  • operator is used to return the rows from the main query based on any value that is present in the sub query of a table.
  • It is different from ALL sql operator in the way it works.

Like

  • It will return all the rows which contains this wildcard %.
  • if you specify before and after the string then query will return all the rows which has this sub string.
  • if you use % wild card charector before the word or the charector then it will return all the rows where the column name has this specific word or charector.

Here you can see all the rows where the name column ends with H.

Here it returns the row where the column name starts with H.


 EXISTS
  • Operator will return rows of data from the table based on the value that is there in the exists condition.

IS NULL

  • Query returns results when a condition is NULL.
  • Here we have used IS NOT NULL.

DISTINCT:
  • It selects only the Distinct values from the table.
  • though Apple table contains duplicate value Granny Smith, Distinct will return only the unique values.