Get Started: SQL Operators

In This Article, You Will Learn About SQL Operators. 

SQL Operators – Before moving ahead, let’s know a bit about the SQL Comments.

Table of Contents

SQL Operators

The SQL operators are defined as operators like Arithmetic, Comparison, , and Compound which are used to perform a specific action based on operators.

SQL Arithmetic Operators

The SQL Arithmetic operators are those which include mathematical operators such as addition, subtraction, multiplication, and division etc.

Table: 1

Operator

Name

Description

+

Add

Use to add two or more values

Subtract

Use to subtract two or more values

*

Multiply

Use to multiply two or more values

/

Divide

Use to divide two or more values

%

Modulo

Use to return remainder

Example: Use the Add ( + ) operator to add values.

				
					SELECT 10 + 20;
				
			

Example: Use the Subtract ( – ) operator to subtract values.

				
					SELECT 10 - 20;
				
			

Example: Use the Multiply ( * ) operator to multiply values.

				
					SELECT 10 * 20;
				
			

Example: Use the Divide ( / ) operator to divide values.

				
					SELECT 10 / 20;
				
			

Example: Use the Modulo ( % ) operator to multiply values.

				
					SELECT 11 % 2;
				
			

SQL Bitwise Operators

Operator

Name

&

Bitwise AND

|

Bitwise OR

^

Bitwise exclusive OR

SQL Comparison Operators

The SQL Comparison operators are used to compare two numerical values.

Operators

Name

Description

=

Equal to

Check whether one value is equal to other value

>

Greater than

Check whether one value is greater than other value

<

Less than

Check whether one value is less than other value

>=

Greater than or equal to

Check whether one value is greater than or equal to other value

<=

Less than or equal to

Check whether one value is less than or equal to other value

<>

Not equal to

Check whether one value is not equal to other value

Table: Numbers

Name

Marks

A

10

B

20

C

30

D

40

E

50

Example: Use the Equal ( = ) comparison to compare two values.

				
					SELECT * FROM Numbers
WHERE Marks = 20;
				
			

Example: Use the Greater than ( > ) comparison to compare two values.

				
					SELECT * FROM Numbers
WHERE Marks > 20;
				
			

Example: Use the Less than ( < ) comparison to compare two values.

				
					SELECT * FROM Numbers
WHERE Marks < 20; 
				
			

Example: Use the Greater than or Equal To ( >= ) comparison to compare two values.

				
					SELECT * FROM Numbers
WHERE Marks >= 20;
				
			

Example: Use the Less than or Equal To ( <= ) comparison to compare two values.

				
					SELECT * FROM Numbers
WHERE Marks <= 20;
				
			

Example: Use the Not Equal To ( <= ) comparison to compare two values.

SQL Compound Operators

Name

Operators

+=

Add equals

-=

Subtract equals

*=

Multiply equals

/=

Divide equals

%=

Modulo equals

&=

Bitwise AND equals

^-=

Bitwise exclusive equals

|*=

Bitwise OR equals

SQL Logical Operators

Operator

Description

ALL

TRUE if all of the subquery values meet the condition

AND

TRUE if all the conditions separated by AND is TRUE

ANY

TRUE if any of the subquery values meet the condition

BETWEEN

TRUE if the operand is within the range of comparisons

EXISTS

TRUE if the subquery returns one or more records

IN

TRUE if the operand is equal to one of a list of expressions

LIKE

TRUE if the operand matches a pattern

NOT

Displays a record if the condition(s) is NOT TRUE

OR

TRUE if any of the conditions separated by OR is TRUE

SOME

TRUE if any of the subquery values meet the condition

Example: Use the ALL operator to check the condition.

				
					SELECT Name
FROM Numbers
WHERE Marks = ALL (SELECT Marks FROM Name WHERE Marks = 20);
				
			

Example: Use the AND operator to check the condition.

				
					SELECT * FROM Numbers
WHERE Name = 'A' AND Marks = 20;
				
			

Example: Use the ANY operator to check the condition.

				
					SELECT * FROM Numbers
WHERE Marks < ANY (SELECT Marks FROM Numbers);
				
			

Example: Use the BETWEEN operator to check the condition.

				
					SELECT * FROM Numbers
WHERE Marks BETWEEN 10 AND 40;
				
			

Example: Use the EXISTS operator to check the condition.

				
					SELECT Name
FROM Data_1
WHERE EXISTS (SELECT Class FROM Data_1 WHERE Data_1.S.No = Data_2.S.No);
				
			

NOTE: Refer SQL EXISTS for more details.

Example: Use the IN operator to check the condition.

				
					SELECT * FROM Number
WHERE Marks IN (20, 30);
				
			

Example: Use the LIKE operator to check the condition.

				
					SELECT * FROM Number
WHERE Name LIKE 'L%';
				
			

Example: Use the NOT operator to check the condition.

				
					SELECT * FROM Number
WHERE Name NOT LIKE 'A%';

				
			

Example: Use the OR operator to check the condition.

				
					SELECT * FROM Number
WHERE Name = 'A' OR Marks = 70;
				
			

Example: Use the SOME operator to check the condition.

				
					SELECT * FROM Number
WHERE Marks < SOME (SELECT * FROM Number WHERE Marks < 30);
				
			

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Leave a Comment

Stay in the loop

codingstreets