SQL Operator Mastery: With Examples and Syntax
Useful SQL Operators on daily work !
SQL Operators Master SQL operators to refine your queries. Learn about arithmetic, comparison, logical, and bitwise operators to write precise SQL queries and extract valuable insights from your database.
Logical Operators in SQL Use SQL logical operators (AND, OR, NOT) to combine conditions in a WHERE clause. Filter data efficiently and write complex queries with ease.
SQL Arithmetic Operators Perform mathematical operations with SQL arithmetic operators (+, -, *, /, %). Calculate values, aggregate data, and manipulate numbers with precision.
Comparison Operators in SQL Compare values with SQL comparison operators (=, <, >, <=, >=, !=). Filter data based on specific conditions and write efficient queries.
SQL Bitwise Operators Manipulate binary data with SQL bitwise operators (&, |, ^). Perform complex operations and extract meaningful insights from your data.
AND Operator in SQL Use the SQL AND operator to combine multiple conditions. Filter data precisely and extract valuable insights from your database.
OR Operator in SQL Use the SQL OR operator to combine multiple conditions. Write flexible queries and extract meaningful insights from your data.
SQL NOT Operator Use the SQL NOT operator to negate a condition. Filter data efficiently and write precise queries with ease. The NOT operator is used to negate a condition, meanwhile it can be used with the EXISTS operator to check for the existence of a record.
IN Operator in SQL Use the SQL IN operator to check if a value exists in a list. Filter data efficiently and write flexible queries. The IN operator is used to specify a list of values, similarly it can be used with the NOT IN operator to exclude a list of values.
SQL LIKE Operator and like command SQL :The LIKE operator is used to search for a pattern in a string, finally it can be used with the NOT LIKE operator to search for a pattern that is not in a string.
SQL operators are essential tools in database management. Firstly, the logical operators such as AND, OR, and NOT are used to combine conditions in queries. Moreover, comparison operators like =, <>, <, <=, >, and >= help to filter data based on specified criteria. In addition, arithmetic operators (+, -, *, /) perform calculations within queries, facilitating data analysis. Furthermore, string operators such as CONCATENATE and LIKE manipulate text data effectively. Additionally, the IN and BETWEEN operators offer flexibility in specifying multiple values or ranges. Subsequently, NULL-related operators like IS NULL and IS NOT NULL handle NULL values gracefully. Consequently, aggregate functions like COUNT, SUM, AVG, MIN, and MAX operate on sets of values to derive useful insights.
Therefore, understanding these operators is crucial for proficient SQL querying. In conclusion, mastering SQL operators empowers database professionals to manage and analyze data with precision
To perform some operation on the given operand values.
– oracle supports the following operators are,
i) Assignment operator => =
ii) Arithmetic operators => + , – , * , /
iii) Relational operators => < , <= , > , >= , != (or) <>
iv) Logical operators => AND,OR,NOT
v) Set operators => UNION,UNION ALL,INTERSECT,MINUS
vi) Special operators => (+ve) operators and (-ve) operators
IN NOT IN
BETWEEN NOT BETWEEN
IS NULL IS NOT NULL
LIKE NOT LIKE
i) Assignment operator: The Assignment operator, such as =, is used to assign a value to a variable or column, meanwhile it can be used to update data, set default values, and perform other data manipulation tasks.
To assign a value to a variable / to a attribute.
syntax:
<column name> <assignment operator> <value>
Ex:
SQL> UPDATE EMP SET SAL=34000;
SQL> UPDATE EMP SET JOB=’HR’ WHERE EMPNO=7788;
ii) Arithmetic operators: The Arithmetic operators, such as +, -, *, /, and %, are used to perform mathematical operations on data, consequently they can be used to calculate values, perform aggregations, and return results based on numerical computations.
syntax:
<column name> <arithmetic operator> <value>
Ex: Want to display employees salaries after adding 5000/-?
SQL> SELECT SAL AS BASIC_SALARY,SAL+5000 AS NEW_SALARY FROM EMP;
Ex: Want to display EMPNO,ENAME,JOB,BASIC SALARY and ANNUAL SALARY of employees who are
working as a “MANAGER”?
SQL> SELECT EMPNO,ENAME,JOB,SAL AS BASIC_SALARY,
2 SAL*12 AS ANNUAL_SALARY FROM EMP WHERE JOB=’MANAGER’;
Ex: Want to display employees salaries after increment of 10%?
SQL> SELECT ENAME,SAL AS BEFORE_INCREMENT,
2 SAL+SAL*10/100 AS AFTER_INCREMENT FROM EMP;
Ex: Want to display ENAME,DEPTNO,BASIC SALARY,INCREMENT OF 5% and TOTAL SALARY of
the employees who are working under dept no is 10?
SQL> SELECT ENAME,DEPTNO,SAL AS BASIC_SAALRY,
2 SAL*0.05 AS INCREMENT_AMOUNT,
3 SAL+SAL*0.05 AS TOTAL_SALARY
4 FROM EMP WHERE DEPTNO=10;
Ex: Want to display all employees salaries after decrement of 5%?
SQL> SELECT ENAME,SAL AS BEFORE_DECREMENT,
2 SAL-SAL*0.05 AS AFTER_DECREMENT FROM EMP;
iii)Relational operators: The Relational operators, such as =, <, >, <=, >=, and !=, are used to compare values and filter data, additionally they can be used to create conditions and return specific results based on the relationships between values.
– comparing a specific column values with a user defined condition value in the query.
syntax:
where <column name> <relational operator> <value>;
Ex:
Want to list out the employees who are joined before 1981?
SQL> SELECT * FROM EMP WHERE HIREDATE < ’01-JAN-1981′;
(OR)
SQL> SELECT * FROM EMP WHERE HIREDATE < ’01-JAN-81′;
Ex:
Want to list out the employees who are joined after 1981?
SQL> SELECT * FROM EMP WHERE HIREDATE>’31-DEC-1981′;
(OR)
SQL> SELECT * FROM EMP WHERE HIREDATE>’31-DEC-81′;
iv) Logical operators : The Logical operators, such as AND, OR, and NOT, are used to combine conditions and filter data, furthermore they can be used to create complex conditional statements and return specific results based on multiple criteria.
– checking more than one condition in the query.
– AND,OR,NOT.
v)AND operator: The AND operator is used to combine multiple conditions, furthermore it can be used with the NOT operator to negate a condition.
– it return a value if both conditions are true in the query.
Cond1 AND Cond2
T T ===> T
T F ===> F
F T ===> F
F F ===> F
syntax:
where <condition1> AND <condition2>
Ex: Want to display employees who are working as a “MANAGER” and the name is “BLAKE”?
SQL> SELECT * FROM EMP WHERE JOB=’MANAGER’ AND ENAME=’BLAKE’;
vi) OR operator: The OR operator is used to combine multiple conditions, in addition it can be used with the IN operator to specify a list of values.
– it return a value if any one condition is true from the given group of conditions.
Cond1 OR Cond2
===== =====
T T ===> T
T F ===> T
F T ===> T
F F ===> F
syntax:
======
where <condition1> OR <condition2>
Ex: Want to display employees whose name is “SMITH” or working under a deptno is 10?
SQL> SELECT * FROM EMP WHERE ENAME=’SMITH’ OR DEPTNO=10;
Ex:
Want to display employees who are working as a “ANALYST” and whose salary is less than 1000?
SQL> SELECT * FROM EMP WHERE JOB=’ANALYST’ OR SAL<1000;
vii) NOT operator: The NOT operator is used to negate a condition, meanwhile it can be used with the EXISTS operator to check for the existence of a record.
– it returns all values except the given conditional values.
syntax:
where not <condiiton1> and not <condition2>
Ex:
Want to display employees who are not working as a “CLERK”,”SALESMAN”?
SQL> SELECT * FROM EMP WHERE NOT JOB=’CLERK’ AND NOT JOB=’SALESMAN’;
viii)SET OPERATORS: The SET operator is used to perform set operations, such as union, intersection, and difference, meanwhile it can be used to combine and manipulate data from multiple tables and return a single result set.
– are used to combined the results of two select statements.
syntax:
<select query1> <set operator> <select query2>;
EX:
A={10,20,30} B={30,40,50}
UNION: The UNION operator is used to combine the result-set of two or more SELECT statements, additionally it can be used with the UNION ALL operator to combine the result-set of two or more SELECT statements without removing duplicates.
– combined the results of two select statements without duplicates.
A U B = { 10,20,30,40,50}
UNION ALL: The UNION ALL operator is used to combine the result sets of two or more queries, additionally it returns all rows from both queries, including duplicates, and can be used to concatenate large datasets.
– combined the results of two select statements with duplicates.
A UL B = { 10,20,30,30,40,50}
INTERSECT: The INTERSECT operator is used to return all rows that are common to both queries, similarly it can be used to identify the overlap between two sets of data and return only the matching rows.
– it return the common values from both select statements.
A I B = { 30 }
MINUS: The MINUS operator is used to return all rows in the first query that are not returned in the second query, consequently it can be used to find the difference between two sets of data and return only the unique rows, additionally it can be used to perform set operations and combine the results of two queries.
– it return the un-common values from the left side select statement but not the
right side select statement.
A – B = { 10,20 }
B – A = { 40,50 }
For Practice Please use this table : Demo_Tables:
SQL> CREATE TABLE EMP_HYD(EID NUMBER(4),ENAME VARCHAR2(10),SAL NUMBER(8,2));
SQL> CREATE TABLE EMP_MUMBAI(EID NUMBER(4),ENAME VARCHAR2(10),SAL NUMBER(8,2));
SQL> SELECT * FROM EMP_HYD;
EID ENAME SAL
1021 SMITH 85000
1022 ALLEN 67000
1023 WARD 47000
SQL> SELECT * FROM EMP_MUMBAI;
EID ENAME SAL
1021 SMITH 85000
1024 JONES 38000
1025 SCOTT 68000
EX: wat to display employees details who are working in HYD but not in MUMBAI branch?
SQL> SELECT * FROM EMP_HYD MINUS SELECT * FROM EMP_MUMBAI;
EX: Want to display all employees details who are working in the organization?
SQL> SELECT * FROM EMP_HYD UNION ALL SELECT * FROM EMP_MUMBAI;(including duplicates)
SQL> SELECT * FROM EMP_HYD UNION SELECT * FROM EMP_MUMBAI;(excluding duplicates)
Ex: Want to display employees details who are working in both branches?
SQL> SELECT * FROM EMP_HYD INTERSECT SELECT * FROM EMP_MUMBAI;
BASIC RULES:
1. No.of columns and order of the columns should same in both select statements.
2. those columns datatypes must match in both queries.
SPECIAL OPERATORS:
IN operator: The IN operator is used to specify a list of values, similarly it can be used with the NOT IN operator to exclude a list of values.
– comparing the list of values with a single condition.
syntax:
where IN(v1,v2,v3,………..);
Ex: want to list out employees details whose EMPNO is 7369,7788,7900?
SQL> SELECT * FROM EMP WHERE EMPNO IN(7369,7788,7900);
Ex: want to list out employees details who are not working as a “ANALYST”,”PRESIDENT”,”MANAGER”?
SQL> SELECT * FROM EMP WHERE JOB NOT IN(‘ANALYST’,’PRESIDENT’,’MANAGER’);==========
BETWEEN: The BETWEEN operator is used to select a range of values, moreover it can be used with the NOT BETWEEN operator to exclude a range of values.
– comparing a particular range value.
syntax:
where <column name> between <low value> and <high value>;
Basic Rules:
1. it returns all values including source and destination value from the given range.
2. it always apply on low value to high value.
3. it can be used along with “AND” operator only.
Ex: Want to display employees who are joined in 1981?
SQL> SELECT * FROM EMP WHERE HIREDATE BETWEEN ’01-JAN-81′ AND ’31-DEC-81′;
Ex: Want to display employees who are not joined in 1981?
SQL> SELECT * FROM EMP WHERE HIREDATE NOT BETWEEN ’01-JAN-81′ AND ’31-DEC-81′;
IS NULL: The IS NULL operator is used to check for null values, similarly it can be used with the IS NOT NULL operator to check for non-null values.
– comparing NULLS in a table.
syntax:
where <column name> is null;
Ex: Want to fetch employees whose commission is empty / is null / is undefined ?
SQL> SELECT * FROM EMP WHERE COMM IS NULL;
Ex: Want to fetch employees whose commission is not empty / is not null / is defined ?
SQL> SELECT * FROM EMP WHERE COMM IS NOT NULL;
Working with NULL:
– it is a empty / a undefined / a unknown value in database.
– NULL != 0 and NULL != space.
– when we perform arithmetic operations are like addition, subtraction, multiple and division with NULL then it again returns NULL only.
Ex: if X=1000;
i) X + NULL ===> 1000+NULL ===> NULL
ii) X – NULL ===> 1000 – NULL ===> NULL
iii) X * NULL ==> 1000 * NULL ===> NULL
iv) X / NULL ===> 1000 / NULL ===> NULL
EX: want to display EMPNO,ENAME,SAL,COMM and SAL+COMM from emp table whose employee
name is “SMITH”?
SQL> SELECT EMPNO,ENAME,SAL,COMM,SAL+COMM AS TOTAL_AMOUNT
FROM EMP WHERE ENAME=’SMITH’;
EMPNO ENAME SAL COMM TOTAL_AMOUNT
—————– ———- ———- ———- ————
7369 SMITH 800
– In the above example the employee “SMITH” is not having commission so that
salary+ commission should be 800 but it returns NULL.
– To overcome the above problem then we should use a pre-defined function is
known as “NVL()”.
What is NVL(exp1,exp2): The NVL function is used to replace null values with a specified value, meanwhile the NVL2 function is used to return one of two values based on whether a value is null or not
– NVL stands for NULL VALUE.
– this function is used to replace a user defined value inplace of NULL in the given expression.
– this function is having two arguments those are Expression1 and Expression2.
> If Exp1 is NULL then it return Exp2 value(UD value)
> If Exp1 is NOT NULL then it return Exp1 value only.
EX:
SQL> SELECT NVL(NULL,0) FROM DUAL;
NVL(NULL,0)
0
SQL> SELECT NVL(NULL,500) FROM DUAL;
NVL(NULL,500)
500
SQL> SELECT NVL(0,500) FROM DUAL;
NVL(0,500)
0
SQL> SELECT NVL(100,500) FROM DUAL;
NVL(100,500)
100
Solution:
SQL> SELECT EMPNO,ENAME,SAL,COMM,SAL+NVL(COMM,0) AS TOTAL_AMOUNT
FROM EMP WHERE ENAME=’SMITH’;
EMPNO ENAME SAL COMM TOTAL_AMOUNT
—————– ———- ———- ———- ————
7369 SMITH 800 800
NVL2(exp1,exp2,exp3):
– it is an extension of NVL().
– this functions is having three arguments are Expression1,Expression2 and Expression3.
> If Exp1 is NULL then it return Exp3 value(UD value).
> If Exp1 is NOT NULL then it return Exp2 value(UD value).
EX: SQL> SELECT NVL2(NULL,100,200) FROM DUAL;
NVL2(NULL,100,200)
200
SQL> SELECT NVL2(500,100,200) FROM DUAL;
NVL2(500,100,200)
100
EX: want to update all employees commissions in a table based on the following conditions are,
i) if the employee commission is NULL then update those employees commission as 900.
ii) if the employee commission is NOT NULL then update those employees commission as COMM+500.
SQL> UPDATE EMP SET COMM=NVL2(COMM,COMM+500,900);
SQL LIKE operator: The LIKE operator is used to search for a pattern in a string, finally it can be used with the NOT LIKE operator to search for a pattern that is not in a string.
– comparing a specific string character pattern.
– when we use LIKE operator we must use the following wildcard operator are,
% – it represent the remaining group of characters after selected character.
– counting a single character from the given string expression.
Syntax:
where <column name> LIKE ‘ [<wildcard operator>] <expression> [<wildcard operator>]’;
Ex:
want to fetch employees whose name starts with “S” character?
SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘S%’;
S %(group of characters)
S MITH
S URESH
S UMAN
S COTT
Ex:
Want to fetch employees whose name ends with “E” character?
SQL> SELECT * FROM EMP WHERE ENAME LIKE’%E’;
Ex: want to fetch employees whose name is having “E” character?
SQL> SELECT * FROM EMP WHERE ENAME LIKE’%E%’;
Ex: Want to fetch employees whose name starts with “M” and ends with “N”?
SQL> SELECT * FROM EMP WHERE ENAME LIKE’M%N’;
Ex: Want to fetch employees whose name is having 4 characters?
SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘____’;
SMITH
WARD
SURESH
KING
SUMAN
FORD
SCOTT
Ex:
Want to fetch employees whose name is having the 2nd character is “O”?
SQL> SELECT * FROM EMP WHERE ENAME LIKE’_O%’;
Ex:
Want to fetch employees who EMPNO starts with 7 and ends with 8 ?
SQL> SELECT * FROM EMP WHERE EMPNO LIKE ‘7%8’;
Ex:
Want to list out employees who are joined in 1981?
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE ‘%81’;
EX:
Want to list out employees who are joined in the month of “DECEMBER”?
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE ‘%DEC%’;
EX:
Want to list out employees who are joined in the month of “DECEMBER” in 1982?
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE ‘%DEC%’ AND HIREDATE LIKE ‘%82’;
EX:
Want to list out employees who are joined in the month of “JUNE”,”DECEMBER”?
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE ‘%JUN%’ OR HIREDATE LIKE ‘%DEC%’;
LIKE operator with SPECIAL CHARACTERS:
DEMO_TABLE:
SQL> SELECT * FROM TEST;
EID ENAME
101 _SMITH
102 ALL@EN
103 MILL_ER
104 ADAM#S
105 WAR%NER
106 TRUNER%
EX:
Want to fetch employees whose name is having “@” symbol?
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%@%’;
EX:
Want to fetch employees whose name is having “#” symbol?
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%#%’;
EX:
Want fetch employees whose name is having “_” symbol?
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%_%’;
EX:
Want to fetch employees whose name is having “%” symbol?
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%%%’;
NOTE: – when we are fetching data based on ” _ , % ” then oracle server will return
wrong result because these ” _ , % ” symbols are treate as “wildcard operators” but not “special characters”. – To overcome the above problem we must use a pre-defined key word in oracle is known “ESCAPE ‘\’ “.
Solution:
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%\_%’ESCAPE’\’;
SQL> SELECT * FROM TEST WHERE ENAME LIKE ‘%\%%’ESCAPE’\’;
EX:
waq to fetch employees whose name not starts with “S” character?
SQL> SELECT * FROM EMP WHERE ENAME NOT LIKE ‘S%’;
If want to learn more SQL function then please visit Our SQL function post.
If you want to do practice with SQL then please click here