PL/SQL NOT EQUAL Operator

In PL/SQL, the NOT EQUAL operator is used to compare two values and determine if they are not equal. If the values are different, the result of the comparison is true; otherwise, it is false.

This operator is often used in conditional statements and queries to filter data based on inequality. In this article, We will learn about PL/SQL NOT EQUAL Operator with the help of various examples and so on.

What is PL/SQL NOT EQUAL Operator?

The NOT EQUAL operator in PL/SQL is represented by != or <>. It is used to check if two expressions or values are not equal to each other.

This operator can be used in WHERE clauses, IF statements, and other conditional expressions.

Syntax:

Using != Operator:

expression1 != expression2

Using <> Operator:

expression1 <> expression2

Explanation:

• expression1: The first value or column to compare.
  
• expression2: The second value or column to compare.
  
• != or <>: The NOT EQUAL operator that performs the comparison.

Examples of PL/SQL NOT EQUAL Operator

We create two tables, employees and departments, to demonstrate the use of the NOT EQUAL operator. The employees table stores employee details, while the departments table holds department information.

1. Employees Table

The employees table is set up with emp_id, emp_name, and dept_id, while the departments table includes dept_id and dept_name. We insert sample data into both tables for demonstration purposes.

Query:

-- Create `employees` table
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_id NUMBER
);

-- Insert data into `employees`
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'John Doe', 101);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (2, 'Jane Smith', 102);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Emily Davis', 103);

Output:

Explanation:

• The CREATE TABLE statement defines the employees table with columns for employee ID, name, and department ID.
  
• The INSERT INTO statements add three records to this table: John Doe in department 101, Jane Smith in department 102, and Emily Davis in department 103.
  
• This setup creates a basic structure for storing employee details and their departmental affiliations.

2. Department Table

The CREATE TABLE statement establishes the departments table with two columns: dept_id (the unique identifier for each department) and dept_name (the name of the department).

The INSERT INTO statements add three records to the table, specifying department IDs and their corresponding names: HR (101), Finance (102), and IT (104).

Query:

-- Create `departments` table 
CREATE TABLE departments (     
     dept_id NUMBER PRIMARY KEY,     
     dept_name VARCHAR2(50) ); 

-- Insert data into `departments` 
        INSERT INTO departments (dept_id, dept_name) VALUES (101, 'HR');
        INSERT INTO departments (dept_id, dept_name) VALUES (102, 'Finance'); 
        INSERT INTO departments (dept_id, dept_name) VALUES (104, 'IT');

Output:

Explanation:

The above department table is the output of the above query displaying the department information .This table represents the department information with unique IDs and names, capturing the organizational structure relevant to the employees' departmental affiliations.

Example 1: Basic Comparison

This SQL query retrieves the names of employees whose department ID is not equal to 101. It uses the != operator in the WHERE clause to filter out employees from department 101 and return only those who belong to other departments.

Query:

SELECT emp_name
FROM employees
WHERE dept_id != 101;

Output:

Explanation:

The output includes names of employees with dept_id values other than 101, excluding those with dept_id equal to 101.

• Includes: Names of employees with dept_id values other than 101.
  
• Excludes: Employees with dept_id equal to 101.

Example 2: Using <> in a Join

This SQL query performs a join between the employees and departments tables using a condition that filters out rows where the dept_id values do not match. The join is based on e.dept_id <> d.dept_id, meaning it will include rows where the department IDs are different.

Query:

SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id <> d.dept_id;

Output:

Explanation:

• Includes: Pairs of employee names and department names where the dept_id values do not match between the two tables.
  
• Excludes: Pairs where the dept_id values are the same.

Example 3: Conditional Logic in PL/SQL Block

This SQL query uses a CASE statement to check if the name of the employee with emp_id = 2 is 'Jane Smith'. If it is not 'Jane Smith', the query returns 'Employee name is not Jane Smith'; otherwise, it returns 'Employee name is Jane Smith'.

Query:

SELECT
    CASE
        WHEN (SELECT emp_name FROM employees WHERE emp_id = 2) != 'Jane Smith'
        THEN 'Employee name is not Jane Smith'
        ELSE 'Employee name is Jane Smith'
    END AS result;

Output:

Explanation:

• If the employee's name is 'Jane Smith': Employee name is Jane Smith.
  
• If the employee's name is not 'Jane Smith': Employee name is not Jane Smith.

Example 4: Filtering Rows in a Subquery

This SQL query retrieves the names of employees who work in departments that are not named 'IT'. The inner query selects dept_id values from the departments table where the department name is not 'IT'.

The outer query then uses these department IDs to filter employees from the employees table.

Query:

SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name <> 'IT');

Output:

Explanation:

• Includes: Names of employees whose department ID is associated with departments that do not have the name 'IT'.
  
• Excludes: Employees who work in the 'IT' department.

Conclusion

The NOT EQUAL operator (!= or <>) in PL/SQL is essential for filtering and comparing data where values are not equal. It is versatile for use in queries and conditional statements to manage data based on inequality.