Operators in SQL with Example

Various Types of Operators in SQL are :

Arithmetic Operators :

+ (ADDITION)     - (DIFFERENCE)     * (MULTIPLY)     / (DIVISION)

Relational Operators :

 = (EQUAL TO)         < (LESS THAN)             > (GREATER THAN)
<> (NOT EQUAL TO)    <= (LESS THAN EQUAL TO)   >= (GREATER THAN EQUAL TO)

Conjunction Operators : Conjunction Operators are used to combine the conditions. Operators are :

 AND Operator                OR Operator

Logical Operators : Logical Operators in SQL are :

Operators Meaning
Unique Used to search every row of a specified table for uniqueness (no duplicate)
All Used to compare a value to all values in another value set.
Any Used to compare a value to any applicable value in the list according to the condition
Is Null Used to compare a value with a NULL value
Between Used to search for values that are within a set of value, given the minimum value and maximum value.
In Used to compare a value to a list of literals values that have been specified.
Like Used to compare a value to similar values using wildcard operators. There are two wildcards:
(%) – It(percent symbol) indicates sequence of 0 or more characters or numbers.
(_) – It(underscore) indicates a single number or character
Exist Used to search for the presence of a row in a specified table that meets certain criteria.

Negating Conditions with the NOT operator :

The NOT operator reverses the meaning of the logical operators with which it is used. For example,

  • [NOT] IN
  • ]NOT] EXIST
  • IS [NOT] NULL
  • [NOT] BETWEEN
  • [NOT] LIKE

The operator enclosed in square brackets [..] is optional.

Aggregate Functions or Summary Functions

Aggregate function is used to provide summarisation information for SQL statement, count , total and averages. Some aggregate functions are:

Function Meaning
Count ([DISTINCT] attribute) Used to count rows or values of a column that do not contain null value.
Count (*) Used to count all the rows of a table including duplicates.
Sum ([DISTINCT] attribute) Used to return the total awesome on the values of a numeric column for a group of rows.
AVG ([DISTINCT] attribute) Used to find average is for a group of rows.
Max (attribute) Used to return the maximum value for the values of a column in a group of rows.
Min (attribute) Used to return the minimum value for the values of a column in a group of rows.

Null Values are always discarded from aggregation Operators.

Set Operators in SQL

  • UNION
  • INTERSECT
  • MINUS
  • UNION ALL
  • INTERSECT ALL
  • MINUS ALL
Example :

Consider the two relation R(A) and S(A) such that

Set Operators in SQL with Example

CONTAIN Operator

The CONTAIN Operator is similar to Division Operator in Relational Algebra.

CONTAIN Operators in SQL with Example

NULL values :

NULL is the set of some randomly generated ASCII value, generated by DBMS engine.

  • No two NULL values are equal.
  • NULL value is non zero.

NULL Operators in SQL with Example

More About Operators :

The BETWEEN and NOT BETWEEN Operator

The BETWEEN and NOT BETWEEN operators are used in range queries.
The BETWEEN operator select a range of data between two values. The values can be numbers, text or dates.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2;

The NOT BETWEEN operator select the data outside the range of data between two values. The values can be text, numbers or date.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name
NOT BETWEEN value1 AND value2;

Combining AND & OR Operator

We can also combine and and or useful and used parenthesis to form complex expressions.

Example :
SELECT * FROM Student
WHERE Age=19
AND (Name = 'Ankit' OR Marks = 458)

All and any

  • If subquery is prefixed with ‘ANY’ keyword, then the condition will be true, if it is satisfied by any of the values produced by inner query.
  • Is subquery is prefixed with ‘ALL’ keyword, then the condition is true, if it is satisfied by all the values produced by inner query.
  • Relation between IN and Any Operators in SQL with Example
Example 1 :

All and Any Operators in SQL with Example

Example 2:

Consider the relation Employee(Eno,Ename,Sal,Dno)

Eno Ename Sal Dno
E1  – 5 4
E2  – 10 6
E3  – 15 5
E4  – 20 6
E5  – 25 5
Question : Retrieve employees whose salary is greater than salaries of any employee in department 5.

Query    : SELECT Eno FROM Employee
           WHERE sal > ANY (SELECT Sal FROM Employee
           WHERE Dno=5)
Solution : >ANY(15,25) ⇒ E4

The EXIST and NOT EXIST Operator

  • If the subquery is prefixed with ‘EXIST’ operator, then the condition is treated as true, if inner query returns non empty rows.
  • If the inner query is prefixed with ‘NOT EXIST’, then the condition is treated as true, if inner query returns empty rows.
  • Always EXIST and NOT EXIST should have SELECT command.

Difference Between EXIST & ALL and ANY

EXIST ALL and ANY
Any query formulated in ALL and ANY can also be formulated in EXIST. Any query formulated in EXIST may not be formulated in ALL and ANY.
Exist queries always leads to correlated subquery. They need not be correlated query
They are less efficient. They are more efficient.

Previous Home Next
Query and Subquery in SQL SQL LIKE Operator

     

Incoming search terms:

  • questions on operators in sql

Leave a Reply