Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

SQL

Learn step by step



Operator in SQL

They are found in just about every SQL query. SQL operators are the mathematical and equality symbols used to compare, calculate, or evaluate values. Equality operators include the (>), (<), and (=) symbols, which are used to compare two values. Each of these characters has a special meaning, & when SQL comes across them, they help to tell that how to evaluate an expression or conditional statement. Operators will appear mostly inside of a conditional statement in the WHERE clause of SQL Commands.

  • SQL Arithmetic Operator
  • SQL Comparison Operator
  • SQL Assignment operator
  • SQL Bitwise Operator
  • SQL Logical Operator
  • SQL Unary Operator

SQL Arithmetic Operator

Arithmetic operators are used to perform mathematical operations in SQL the same as in most other languages. There are 4 conventional operators for mathematical operation:

  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)

Addition

Addition is performed by the use of the plus (+) symbol.

SELECT SALARY + BONUS FROM EMPLOYEE_PAY;
/*column SALARY is added to BONUS for a total for each row of data*/
Subtraction

Subtraction is performed using the minus (-) symbol.

SELECT SALARY - BONUS FROM EMPLOYEE_PAY;
/*column BONUS is subtracted from SALARY column for the difference*/
Multiplication

Multiplication is performed by using asterisk (*) symbol.

SELECT SALARY * 10 FROM EMPLOYEE_PAY;
/*column SALARY is multiplied by 10*/
Division

Division is performed by the use of the slash (/) symbol.

SELECT SALARY / 10 FROM EMPLOYEE_PAY;
/*column SALARY is multiplied by 10*/
Comparison Operators
Operator Description Example
=   Equality test.
SELECT EMPNAME "Employee" FROM EMP_d WHERE SALARY = 1500;
!=, ^=, <> Inequality test.
SELECT EMPNAME FROM EMP_d WHERE SALARY ^= 5000;
>   Greater than test.
SELECT EMPNAME "Employee", JOB "Title" FROM EMP_d WHERE SALARY > 5000;
<   Less than test.
SELECT * FROM PRICE_D WHERE MINPRICE < 30;
>=   Greater than or equal to test.
SELECT * FROM PRICE_D WHERE MINPRICE >= 20;
<=   Less than or equal to test.
SELECT EMPNAME FROM EMP_d WHERE SALARY <= 1500;
IN "Equivalent to any member of".
SELECT * FROM EMP_d WHERE EMPNAME IN ('SMITH', 'WARD');
ANY/ SOME Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows.
SELECT * FROM DEPT WHERE LOCATION = SOME ('NEW YORK','DALLAS');
NOT IN Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL.
SELECT * FROM DEPT WHERE LOCATION NOT IN ('NEW YORK', 'DALLAS');
ALL Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows.
SELECT * FROM emp_d WHERE salary >= ALL (1500, 3000);
[NOT] BETWEEN x and y [Not] greater than or equal to x and less than or equal to y.
SELECT EMPNAME, JOB FROM EMP_d WHERE SALARY BETWEEN 4000 AND 5000;
EXISTS TRUE if a sub-query returns at least one row.
SELECT * FROM EMP_d WHERE EXISTS (SELECT EMPNAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKE y [ESCAPE z] TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. Character "_" matches any single character. Character following ESCAPE are interpreted, useful when y contains a percent or underscore.
SELECT * FROM EMP_d WHERE EMPNAME LIKE '%E%';
IS [NOT] NULL Tests for nulls. This is the only operator that should be used to test for nulls.
SELECT * FROM EMP_d WHERE COMM IS NOT NULL AND SALARY > 1500;

Sql Assignement Operator

These are used for assigning values.

SELECT ID = ProductID, Product = ProductName FROM Products

SQL Logical Operators

There are 3 Logical Operators AND, NOT, and OR. When retrieving data using SELECT statement, we can use these operators in the WHERE clause, which allows to combine more than one conditions.

Operator Description Example
NOT Returns TRUE if the given condition is NOT TRUE or FALSE. And returns FALSE if given condition is TRUE. If it is UNKNOWN, then it remains UNKNOWN.
SELECT * FROM EMP_d WHERE NOT (job IS NULL)

SELECT * FROM EMP_d WHERE NOT (sal BETWEEN 2000 AND 5000)

AND AND Returns TRUE if both conditions are TRUE. And return FALSE if either of them or both conditions are FALSE;
SELECT * FROM EMP_d WHERE job='CLERK' AND deptno=5
OR Returns TRUE if either condition is TRUE. Returns FALSE if both the conditions are FALSE.
SELECT * FROM emp_d WHERE job='CLERK' OR deptno=5



Related Videos