Oracle Not Equals (!= ,<> ) SQL Operator

I will explain Oracle Not Equals (!=) SQL Operator in this post.

 

You can read the following post to learn All Comparison operators in Oracle SQL.

SQL WHERE Clause and Comparison Operator in Oracle SQL | Oracle SQL Tutorials -4

 

 

 

 

Oracle Not Equals (!=) SQL Operator

 

There are lots of syntax in Oracle SQL for Not Equal and the “not equals” operator may be expressed as “<>” or “!=” in Oracle SQL.

These operators are used in the Where clause.

 

SQL WHERE Clause

WHERE clause in the SQL is used to filter records returned by a query.

 

You can display only specific records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, … FROM table_name
WHERE condition;

 

You can use The following operators in the WHERE clause.

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>  or  !=	Not equal.
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify and search multiple possible values for a column

 

You can use these compariton operators in the Where Clause as follows.

 

 

Firstly query all table, then apply not equal condition using where clause as follows.

 

SQL> 
SQL> SELECT * FROM HR.DEPARTMENTS;       

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700

27 rows selected.

 

 

Not Equals (!= , <> ) SQL Operator

Now list all departments which location_id is not equal to 1700.

 

SQL> SELECT * FROM HR.DEPARTMENTS WHERE LOCATION_ID!=1700;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
20 Marketing 201 1800
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500

6 rows selected.


Second usage of Not equal operator is <> operator as follows.

SQL> SELECT * FROM HR.DEPARTMENTS WHERE LOCATION_ID<>1700;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
20 Marketing 201 1800
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500

6 rows selected.

SQL>

 

Do you want to learn Oracle SQL for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course