Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SELECT WHERE IN null in MySQL?
Following is the syntax −
select yourColumnName1, yourColumnName2, yourColumnName3, . . . N from yourTableName where yourValue in(yourColumnName1,yourColumnName2) or yourColumnName1 is NULL;
Let us create a table −
mysql> create table demo60 −> ( −> id int not null auto_increment primary key, −> first_name varchar(20), −> last_name varchar(20) −> ) −> ; Query OK, 0 rows affected (2.11 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo60(first_name,last_name) values('John','Smith');
Query OK, 1 row affected (0.09 sec)
mysql> insert into demo60(first_name,last_name) values('John','Doe');
Query OK, 1 row affected (0.51 sec)
mysql> insert into demo60(first_name,last_name) values(null,'Brown');
Query OK, 1 row affected (0.09 sec)
mysql> insert into demo60(first_name,last_name) values('David','Miller');
Query OK, 1 row affected (0.16 sec)
mysql> insert into demo60(first_name,last_name) values('David','Smith');
Query OK, 1 row affected (0.11 sec)
mysql> insert into demo60(first_name,last_name) values('Chris','Brown');
Query OK, 1 row affected (0.12 sec)Display records from the table using select statement −
mysql> select *from demo60;
This will produce the following output −
+----+------------+-----------+ | id | first_name | last_name | +----+------------+-----------+ | 1 | John | Smith | | 2 | John | Doe | | 3 | NULL | Brown | | 4 | David | Miller | | 5 | David | Smith | | 6 | Chris | Brown | +----+------------+-----------+ 6 rows in set (0.00 sec)
Following is the query to select where in with NULL −
Mysql> select −> id, −> first_name, −> last_name −> from demo60 −> where 'John' in(first_name,last_name) or first_name is NULL;
This will produce the following output −
+----+------------+-----------+ | id | first_name | last_name | +----+------------+-----------+ | 1 | John | Smith | | 2 | John | Doe | | 3 | NULL | Brown | +----+------------+-----------+ 3 rows in set (0.00 sec)
Updated on: 20-Nov-2020
375 Views
Advertisements