MySQL If Exists

Introduction

The two distinct provisions in MySQL are called EXISTS, and IF EXISTS. The records in the subqueries are checked for existence using the EXISTS clause. The EXISTS clause returns true if there is at least one entry in the subquery; otherwise, it returns false. The primary query uses this as the determining factor for running and getting records. On the other hand, when deleting tables, views, and databases, we use the IF EXISTS clause. An error may be avoided by using the IF EXISTS clause at that point if the object we are attempting to dump does not exist. When MySQL detects a missing object, it stops the execution and raises a warning.

How to use the DROP DATABASE, DROP TABLE, and DROP VIEW commands in MySQL using the IF EXISTS clause, with examples and syntax.

1. Drop Database IF EXISTS Command

Syntax:

DROP DATABASE [IF EXISTS] name_of_database;

Name_of_database indicates the database name you want to erase and its components, such as tables, stored procedures, triggers, etc.

If the database called "name_of_database" does not exist on the database server and the DROP DATABASE command is used in MySQL, an error is raised saying that no such database is present. Nevertheless, if the command contains the IF EXISTS condition, a notification rather than an error is generated, indicating that the database with the given name is not present.

The execution of the DROP DATABASE command ends in certain circumstances. The IF EXISTS clause's goal is to stop an error from being raised if a database with the given name does not exist.

First, we must use the following command to open the MySQL connection:

 mysql -u root -p

supplied if you want to use root as your username. If a password is given, a prompt will then appear for the password.

The following output will appear when you run the command mentioned above:

The following command must then be run to list every database on your MySQL database server.

SHOW DATABASES;

In my instance, it produces the following result:

Let's now attempt to remove the database that doesn't exist on our database server and see the results:

DROP DATABASE educate;

The outcome of running the command above is as follows:

An exception with error number 1008 is generated, signifying that the 'educate' database cannot be discarded as it does not exist.

Let's now examine the result of our query statement with the IF EXISTS clause added.

IF education IS EXISTING, DROP DATABASE;

The results of doing the query above are shown as follows:

Even in cases where our database does not include the "educate" database, the report makes it clear that there is no mistake. That indicates the query was executed successfully, with just one warning and no records impacted.

2. Drop-Table IF EXISTS Command

Syntax:

DROP TABLE [IF EXISTS] name_of_table;

Here, name_of_table aids in identifying the name of the database you want to remove, together with all its components, including tables and the structure that includes partitions, constraints, and indexes.

If the database does not have a table with the name "name_of_table," MySQL will give an error stating that it does not exist when you use the DROP TABLE command. The table with the given name is missing, but if you use the IF EXISTS clause in the command, a caution rather than an error is generated. The DROP TABLE command's execution ends in these circumstances. When there isn't a table with the given name, the IF EXISTS clause prevents the error from being raised.

3. Drop View IF EXISTS Command

Syntax:

DROP VIEW [IF EXISTS] name_of_view ;

Here, name_of_view aids in identifying the name of the view and any contents you want to remove. Remember that the original table or tables on which the view definition is based are unaffected.

When using the DROP VIEW command in MySQL, an error stating that no such view is available will be thrown if the idea with the given name (name_of_view) does not exist in the database. A warning indicating that the picture with the given name is not present will be produced in place of an error, however, if the command contains the IF EXISTS condition. At that moment, the execution of the DROP VIEW command ends. When there isn't a view with the given name, the IF EXISTS clause prevents an error from being raised.

Example

Utilizing the following query statement and the basic DROP command, let's attempt to drop the view called viewDemo, which is not in the database named Educate we are now utilizing.

DROP VIEW viewed;

The results of doing the query above are shown as follows:

Since this table does not exist in the EducBA database, an error is raised stating that it is unknown. Though they are not physically present in memory, views are logical tables. When displaying errors and warnings, MySQL also refers to them as tables. Try using the same query statement's IF EXISTS clause now.

DROP VIEW IF EXISTS viewed;

When the query above is run, the following output is generated: a warning and an indication that the question was successfully conducted without altering any rows.

We may run the following command to observe the warning:

SHOW WARNINGS;

That, when the query statement is executed, produces the following output: