MySQL is a relational database management system that is used to store and manage data. It is an open-source system and is widely used in web applications. The WHERE clause is an important part of the MySQL query language. It is used to filter data in a table based on a specific condition. The WHERE NOT NULL clause is used to filter rows that have non-null values in a particular column. In this article, we will discuss how to use MySQL WHERE NOT NULL.
The WHERE NOT NULL clause is used to retrieve rows that have non-null values in a particular column. It is used in SELECT, UPDATE, and DELETE statements. The syntax for using WHERE NOT NULL is as follows:
SELECT column_name(s) FROM table_name WHERE column_name IS NOT NULL;
The above syntax retrieves all rows from the table where the specified column has a non-null value. The column_name(s) parameter specifies the column(s) that you want to retrieve from the table. The table_name parameter specifies the name of the table from which you want to retrieve the data.
Let’s look at an example to understand how to use WHERE NOT NULL. Suppose we have a table named ’employees’ with the following data:
| ID | Name | Age | Salary | Department | |----|------|-----|--------|------------| | 1 | John | 35 | 60000 | Sales | | 2 | Mary | 28 | NULL | Marketing | | 3 | Alex | 42 | 75000 | Finance | | 4 | Jane | 31 | 45000 | HR | | 5 | Bill | 26 | NULL | Sales |
Suppose we want to retrieve all rows from the table where the Salary column has a non-null value. We can use the following query:
SELECT ID, Name, Age, Salary, Department FROM employees WHERE Salary IS NOT NULL;
The above query will retrieve the following data:
| ID | Name | Age | Salary | Department | |----|------|-----|--------|------------| | 1 | John | 35 | 60000 | Sales | | 3 | Alex | 42 | 75000 | Finance | | 4 | Jane | 31 | 45000 | HR |
As you can see, the query has retrieved all rows where the Salary column has a non-null value.
The WHERE NOT NULL clause can also be used in UPDATE and DELETE statements. Let’s look at an example to understand this.
Suppose we want to update the Salary of all employees in the ‘Sales’ department to 70000. We can use the following query:
UPDATE employees SET Salary = 70000 WHERE Department = ‘Sales’ AND Salary IS NOT NULL;
The above query will update the Salary of all employees in the ‘Sales’ department to 70000, but only those employees who have a non-null value in the Salary column.
Suppose we want to delete all employees from the ‘Marketing’ department who do not have a Salary. We can use the following query:
DELETE FROM employees WHERE Department = ‘Marketing’ AND Salary IS NULL;
The above query will delete all rows from the table where the Department column is ‘Marketing’ and the Salary column has a null value.
The WHERE NOT NULL clause is a powerful feature of the MySQL query language. It allows you to retrieve, update, and delete rows from a table based on a specific condition. The clause is easy to use and can be used in SELECT, UPDATE, and DELETE statements. It is important to note that the WHERE NOT NULL clause only works on columns that have a null value. If a column has a default value or a value that is not null, the clause will not work.