SQL Query to Select all Records From Employee Table Where Name is Not Specified

In SQL, filtering records based on specific criteria is a fundamental task when working with relational databases. One common scenario is selecting all records from a table while excluding certain values, such as specific names or designations. This article demonstrates how to use SQL queries effectively to find and exclude records based on these criteria, using operators like <> (not equal to) and NOT. By applying these techniques, we can easily filter out unwanted data and ensure that our query results are accurate and relevant.

1. Creating a table

First, create the employee_details table with the following structure. This table will store the employee information, including ID, name, designation, and age.

  CREATE TABLE employee_details(
     emp_id VARCHAR(8),
     emp_name VARCHAR(20),
     emp_designation VARCHAR(20),
     emp_age INT);

2. Inserting data into the table :

Inserting rows into employee_details table using the following SQL query as follows.

INSERT INTO employee_details VALUES('E40001','PRADEEP','H.R',36),
    ('E40002','ASHOK','MANAGER',28),
    ('E40003','PAVAN KUMAR','ASST MANAGER',28),
    ('E40004','SANTHOSH','STORE MANAGER',25),
    ('E40005','THAMAN','GENERAL MANAGER',26);

3. Verifying the inserted data :

Viewing the table employee_details after inserting rows by using the following SQL query as follows.

SELECT * FROM employee_details;

Output

Query 1: find an employee whose name is not Pradeep.

Since we need to display the names other than Pradeep we can use not equal to (<>) operator with the where clause to get the required query executed, In the WHERE clause we can use any other conditions also using other operators such as >,<, AND, OR, NOT etc..,

1) USING <> operator

The <> operator in SQL is used to filter rows from a table where the column value does not match the specified value. It is particularly useful for excluding specific records from our results, such as filtering out employees whose names do not match a particular criteria

Query:

 SELECT* FROM employee_details
 WHERE emp_name <>'PRADEEP';

Output

2) USING NOT operator

The NOT operator achieves the same result as using the <> operator by negating the condition. It is used to exclude records where the specified condition is true. For instance, if we want to exclude employees whose name is "Pradeep," we can use NOT with the condition to achieve the same result as using <>

Query:

  SELECT* FROM employee_details
  WHERE NOT emp_name='PRADEEP';

Output

Query 2: Exclude Specific Designations

Query to find the employee whose designation is not "General Manager" and "Store Manager", combine multiple conditions with the AND operator. Here AND operator is used to execute the following query.

Query:

  SELECT* FROM employee_details
  WHERE  emp_designation<> 'GENERAL MANAGER' AND
  emp_designation <> 'STORE MANAGER';

Output

Explanation:

The query filters the employee_details table to exclude employees with the designations "General Manager" and "Store Manager." It uses the AND operator to combine conditions, ensuring that only employees whose designation does not match both criteria are returned. The output shows employees with other roles like "H.R" and "Manager," providing a clear view of excluded designations.

Conclusion

In this article, we explained how to use <>, NOT, and logical operators like AND to filter records effectively in SQL. These techniques are highly flexible and can be applied in a variety of real-world scenarios, such as excluding specific names, designations, or any other criteria from our results. Understanding these operators is essential for data filtering, especially when working with large datasets or creating dynamic queries in applications. By mastering these methods, we can efficiently retrieve precise and meaningful data while improving query performance.