8 Powerful SQL Operators to Master Data Manipulation and Queries

SQL Operators

  • SQL operators are signs and commands, which are a key element in SQL statements and are used to filter, compare, combine, and do calculating or logical operations.
  • For example, will refer to the SQL operators explanation with detailed explanation if there are.
  • Here are a few examples of the SQL operators topic. I hope you will understand better after these examples.
SQL Operators
SQL Operators

1. Arithmetic Operators

  • Arithmetic operators work to do mathematical operations.
OperatorDescription   Example
+AdditionSELECT 10 + 5;
SubtractionSELECT 10 – 5;
*MultiplicationSELECT 10 * 5;
/DivisionDivision SELECT 10 / 5;
%Modulus(remainder)SELECT 10 % 3;

 

  • Example:

SELECT (salary + bonus) AS total_income

FROM employees;

  • This query is the one that adds up salaries and bonuses of the employees, thus showing their total income.

2. Comparison Operators

  • Comparison operators compare two values and give a boolean result (TRUE, FALSE, or NULL).
OperatorDescriptionExample
=Equal toSELECT * FROM students WHERE age = 20;
!= or <>Not equal toSELECT * FROM students WHERE age <> 20;
>Greater thanSELECT * FROM products WHERE price > 100;
<Less thanSELECT * FROM products WHERE price < 100;
>=Greater than or equal toSELECT * FROM students WHERE marks >= 75;
<=Less than or equal toSELECT * FROM students WHERE marks <= 75;

 

  • Example:

SELECT name, salary FROM employees WHERE salary >50000;

  • This is the one that brings employees whose wages are more than 50,000.

3. Logical Operators

  • Logical operators are used to unite different conditions.
OperatorDescriptionExample
ANDBoth conditions trueSELECT * FROM students WHERE age > 18 AND marks > 80;
OREither condition trueSELECT * FROM students WHERE age < 18 OR marks < 50;
NOTNegates conditionSELECT * FROM students WHERE NOT age > 18;

 

  • Example:

SELECT name FROM employees

WHERE department ='HR' AND experience >5;

  • It shows the names of employees working in the HR department with more than 5 years of experience.

4. Bitwise Operators

  • Bitwise operators are at the binary level.
OperatorDescription  Example
&Bitwise AND  SELECT 5 & 3;
``Bitwise OR
^Bitwise XORSELECT 5 ^ 3;

 

  • Example:

SELECT 5&3 AS result;

  • Here, a bitwise AND operation is performed between 5 and 3.

5. String Operators

  • String operators impact text data.
OperatorDescription  Example
+Concatenation (SQL Server)SELECT ‘Hello’ + ‘ World’;
``
LIKEPattern matchingSELECT * FROM customers WHERE name LIKE ‘J%’;
NOT LIKENegated pattern matchingSELECT * FROM customers WHERE name NOT LIKE ‘J%’;

 

  • Example:

SELECT name

FROM customers

WHERE email LIKE '%@gmail.com';

  • This retrieves customers with email address.

6. Set Operators

  • The result of the set of computer programs will have given statements.
Operator  Description  Example
UNIONCombines results (unique)SELECT name FROM emp1 UNION SELECT name FROM emp2;
UNION ALLCombines all resultsSELECT name FROM emp1 UNION ALL SELECT name FROM emp2;
INTERSECTCommon rows between setsSELECT name FROM emp1 INTERSECT SELECT name FROM emp2;
EXCEPT Rows in first, not secondSELECT name FROM emp1 EXCEPT SELECT name FROM emp2;

 

  • Example:

SELECT product_name FROM warehouse1 UNIONSELECT product_name FROM warehouse2;

  • This gets the unique names of products from both warehouses.

7. Special Operators

  • Operators that are special have more choices to be selected.
OperatorDescription  Example
BETWEENWithin range (inclusive)SELECT * FROM employees WHERE age BETWEEN 30 AND 40;
IN Matches any value in listSELECT * FROM employees WHERE department IN (‘HR’, ‘IT’);
NOT INDoes not match any value in listSELECT * FROM employees WHERE department NOT IN (‘HR’, ‘IT’);
IS NULLChecks for NULL valuesSELECT * FROM students WHERE marks IS NULL;
IS NOT NULLChecks for non-NULL valuesSELECT * FROM students WHERE marks IS NOT NULL;
EXISTSChecks for existence of rows in subquerySELECT * FROM employees WHERE EXISTS (SELECT * FROM managers);

 

  • Example:

SELECT name FROM employees

WHERE age BETWEEN 25 AND 35 AND department IN ('IT', 'HR');

  • This returns employees whose age is between 25 and 35 working in IT or HR

8. Assignment Operators

  • Assignment operators are operators that assign values to the variables.                                    
Operator Description   Example
=Assign valueSET @total = 100;

 

  • Example:

SET @bonus = salary * 0.10;

Leave a Comment