8 Powerful SQL Operators to Master Data Manipulation and Queries Vaishnavi Tech Talks

Table of Contents

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.

1. Arithmetic Operators

Arithmetic operators work to do mathematical operations.

Operator	Description	Example
+	Addition	SELECT 10 + 5;
–	Subtraction	SELECT 10 – 5;
*	Multiplication	SELECT 10 * 5;
/	Division	Division 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).

Operator	Description	Example
=	Equal to	SELECT * FROM students WHERE age = 20;
!= or <>	Not equal to	SELECT * FROM students WHERE age <> 20;
>	Greater than	SELECT * FROM products WHERE price > 100;
<	Less than	SELECT * FROM products WHERE price < 100;
>=	Greater than or equal to	SELECT * FROM students WHERE marks >= 75;
<=	Less than or equal to	SELECT * 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.

Operator	Description	Example
AND	Both conditions true	SELECT * FROM students WHERE age > 18 AND marks > 80;
OR	Either condition true	SELECT * FROM students WHERE age < 18 OR marks < 50;
NOT	Negates condition	SELECT * 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.

Operator	Description	Example
&	Bitwise AND	SELECT 5 & 3;
`	`	Bitwise OR
^	Bitwise XOR	SELECT 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.

Operator	Description	Example
+	Concatenation (SQL Server)	SELECT ‘Hello’ + ‘ World’;
`		`
LIKE	Pattern matching	SELECT * FROM customers WHERE name LIKE ‘J%’;
NOT LIKE	Negated pattern matching	SELECT * 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
UNION	Combines results (unique)	SELECT name FROM emp1 UNION SELECT name FROM emp2;
UNION ALL	Combines all results	SELECT name FROM emp1 UNION ALL SELECT name FROM emp2;
INTERSECT	Common rows between sets	SELECT name FROM emp1 INTERSECT SELECT name FROM emp2;
EXCEPT	Rows in first, not second	SELECT 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.

Operator	Description	Example
BETWEEN	Within range (inclusive)	SELECT * FROM employees WHERE age BETWEEN 30 AND 40;
IN	Matches any value in list	SELECT * FROM employees WHERE department IN (‘HR’, ‘IT’);
NOT IN	Does not match any value in list	SELECT * FROM employees WHERE department NOT IN (‘HR’, ‘IT’);
IS NULL	Checks for NULL values	SELECT * FROM students WHERE marks IS NULL;
IS NOT NULL	Checks for non-NULL values	SELECT * FROM students WHERE marks IS NOT NULL;
EXISTS	Checks for existence of rows in subquery	SELECT * FROM employees WHERE EXISTS (SELECT * FROM managers);

Example: