Sql 简明教程

SQL - NOT EQUAL

The SQL NOT EQUAL Operator

SQL NOT EQUAL 运算符用于比较两个值，如果它们不相等，则返回 true。它由 "<>" 和 "!=" 表示。这两个之间的差异在于 <> 遵循 ISO 标准，而 != 则不遵循。因此，建议使用 <> 运算符。

The SQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by "<>" and "!=". The difference between these two is that <> follows the ISO standard, but != doesn’t. So, it is recommended to use the <> operator.

我们可以在 WHERE 子句中使用 NOT EQUAL 运算符基于特定的条件筛选记录，在 GROUP BY 子句中使用它对结果进行分组。

We can use the NOT EQUAL operator in WHERE clause to filter records based on a specific condition and in GROUP BY clause to group the results.

Syntax

以下为 SQL 中 NOT EQUAL 运算符的语法 -

Following is the syntax of the NOT EQUAL operator in SQL −

WHERE expression1 <> expression2;

Example

为了更好地理解它，让我们考虑一下 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将按如下方式创建：

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT EQUAL with Text

我们可以在 SQL 中使用 NOT EQUAL 运算符与文本进行比较，然后返回两个文本值。我们可以在 SQL 语句的 WHERE 子句中使用 "<>" 或 "!="，并排除与特定文本值相匹配的行。

We can use the NOT EQUAL operator with text in SQL to compare two text values and return. We can use "<>" or "!=" in the WHERE clause of a SQL statement and exclude rows that match a specific text value.

Example

在以下查询中，我们正在检索所有记录，其中 CUSTOMERS 表的 NAME 不是“Ramesh”−

In the following query, we are retrieving all the records from the CUSTOMERS table whose NAME is not 'Ramesh' −

SELECT * FROM CUSTOMERS WHERE NAME <> 'Ramesh';

Output

以上代码的输出如下所示 −

The output of the above code is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT EQUAL with GROUP BY Clause

我们可以在 GROUP BY 子句中使用不等于运算符，按不等于指定文本值的值对结果进行分组。

We can use the NOT EQUAL operator with the GROUP BY clause to group the results by the values that are not equal to the specified text value.

聚合函数（例如 COUNT()、MAX()、MIN()、SUM() 和 AVG()）经常与 GROUP BY 语句一起使用。

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

此处，我们正在检索“CUSTOMERS”表中具有不同年龄的记录数（不包括“22”），并按年龄值进行分组 −

Here, we are retrieving the number of records with distinct ages (excluding '22') in the 'CUSTOMERS' table and grouping them by age value −

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE <> '22' GROUP BY AGE;

Output

在执行以上查询时，它将生成如下所示的输出 −

On executing the above query, it will generate the output as shown below −

COUNT(id)	AGE
1	32
2	25
1	23
1	27
1	24

NOT EQUAL with Multiple Conditions

不等于运算符还可以在 WHERE 子句中与多个条件一起使用，以筛选出符合特定条件的行。

The not equal operator can also be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

现在，我们正在检索所有工资为“ >2000 ”或“ =2000 ”的客户。同时，客户不应来自“Bhopal”−

Now, we are retrieving all the customers whose salary is either ">2000" or "=2000". At the same time, the customer must not be from "Bhopal" −

SELECT * FROM CUSTOMERS
WHERE ADDRESS <> 'Bhopal' AND (SALARY>'2000' OR SALARY='2000');

Output

以下是上面代码的输出： -

Following is the output of the above code −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Negating a Condition Using NOT EQUAL

在 SQL 中，不等于运算符还可以与 NOT 运算符结合使用，以否定条件。它将筛选出符合特定条件的行。

In SQL, the NOT EQUAL operator can also be combined with the NOT Operator to negate a condition. It filters out the rows that meet a specific condition.

Example

在以下查询中，我们正在检索“CUSTOMERS”表中所有“SALARY”等于“2000”的行 −

In the following query, we are retrieving all rows from the "CUSTOMERS" table where the "SALARY" is equal to '2000' −

SELECT * FROM CUSTOMERS WHERE NOT SALARY != '2000';

Output

执行上面的代码后，我们得到以下输出： -

After executing the above code, we get the following output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00