Understanding SQL Comparison Operators

Rating: 5.0. From 2 votes.
Please wait...

Comparing various operands or variables based on different conditions is the basic thing which is done in every program we make for determining equality or inequality between two operands,So today we are here with a small snippet
Understanding SQL Comparison Operators which will explain about all the comparison operators used in PHP in most easiest way.

Comparison operators are mostly used in the WHERE clause to determine which records to select.
Here are some comparison operators which can be used in SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
=               Equal
<>              Not Equal
<>              Not Equal
!=              Not Equal
>               Greater Than
>=              Greater Than or Equal
<               Less Than
<=              Less Than or Equal
IN ( )          Matches a value in a list
NOT             Negates a condition
BETWEEN         Within a range (inclusive)
IS NULL         NULL value
IS NOT NULL     Non-NULL value
LIKE            Pattern matching with % and _
EXISTS          Condition is met if subquery returns at least one row

So let’s learn how we can use these operators

Inequality Operator
In SQL, there are two ways to test for inequality in a query. You can use either the <> or != operator.
Taking a example

1
2
3
SELECT *
FROM members
WHERE member_name<> 'deepak';

It can also be written as

1
2
3
SELECT *
FROM members
WHERE member_name!= 'deepak';

Moving forward let’s learn about Equality Operator
In SQL, you can use the = operator to test for equality in a query.
Example:

1
2
3
SELECT *
FROM members
WHERE member_name = 'deepak';

the SELECT statement above would return all rows from the members table where the member_name is equal to deepak.

Greater Than Operator
In SQL, you can use the >= operator to test for an expression greater than or equal to.
Example:

1
2
3
SELECT *
FROM members
WHERE member_id >= 1;

the SELECT statement would return all rows from the members table where the member_id is greater than or equal to 1. In this case, member_id equal to 1 would be included in the result set.

Less Than Operator
You can use the < operator in SQL to test for an expression less than. Example:

1
2
3
SELECT *
FROM members
WHERE member_id < 10;

the SELECT statement would return all rows from the members table where the member_id is less than 10. A member_id equal to 10 would not be included in the result set.

Less Than or Equal Operator
In SQL, you can use the <= operator to test for an expression less than or equal to
Example:

1
2
3
SELECT *
FROM members
WHERE member_id <= 10;

the SELECT statement would return all rows from the members table where the member_id is less than or equal to 10. In this case, member_id equal to 10 would be included in the result set.

LIKE Condition
The SQL LIKE condition is used for pattern matching using Wildcards. The LIKE Clause can be used with any of the SELECT, INSERT,DELETE or UPDATE Statement
Example

1
2
3
4
SELECT *
FROM members
WHERE member_name LIKE 'D%'
ORDER BY member_name;

the records in the members table where the member_name starts with ‘D’. will be fetched

we can also use LIKE statement like this

1
2
3
4
SELECT *
FROM members
WHERE member_name LIKE '%D%'
ORDER BY member_name;

the records in the members table where the member_name contains ‘D’ in it anywhere will be fetched

NOT NULL condition
NOT NULL condition is used in SQL to test for a non-NULL value.
Example:

1
2
3
SELECT *
FROM members
WHERE members_contact IS NOT NULL;

IS NULL Condition
IS NULL condition is used in SQL to test for a NULL
Example

1
2
3
SELECT *
FROM members
WHERE member_last_name IS NULL;

IN Condition
SQL IN condition (known as IN operator) allows you to easily test if an expression matches any value in a list of values
Example:

1
2
3
SELECT *
FROM members
WHERE member_name IN ('Deepak', 'Saurabh', 'Manglesh');

This will return all rows from the members table where the member_name is either Deepak, Saurabh or Manglesh.

EXISTS Condition
This condition is used with a Subquery which is meant to be met if subquery returns at least one result then it will show the result. It can be used in SELECT, INSERT, DELETE,UPDATE statement.

1
2
3
4
5
6
SELECT *
FROM members
WHERE EXISTS
(SELECT *
FROM orders
WHERE member.member_id= orders.member_id);

So this is all from this tutorial these are the basic operations we perform for comparing values in our programs.
Hope it will help our new developers
You can also read our tutorials

Preventing SQL Injection In PHP

UNDERSTANDING MYSQL GROUP BY AND ORDER BY

So guys start your journey as a developer now if you are still thinking about choosing PHP for your career. we are here to help you
Because Hurdles aren’t hurdles

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
42 ⁄ 14 =