SELECT * WHERE NOT EXISTS – MySQL

MySQL is a structured query language that can manage and manipulate data stored in database tables. More specifically, the NOT EXIST operator is a logical operator used within the WHERE clause to retrieve the data by excluding the records that match a specified condition. The command “SELECT * WHERE NOT EXISTS” contains “*,” which means to select all the columns of a table and then apply the condition present in the WHERE clause.

This post will discuss the usage of “SELECT * WHERE NOT EXISTS” in MySQL.

How to Use “SELECT * WHERE NOT EXISTS” in MySQL?

Before using the command in question, understand its different sections first. In MySQL:

• The “SELECT” statement retrieves the data
• The “WHERE” clause retrieves the data that matches a certain condition
• The “NOT EXIST” operator in the WHERE clause will exclude the records that match a specific condition

Syntax

To use “SELECT * WHERE NOT EXISTS”, follow the provided syntax:

The “*” represents all columns, and the query inside the brackets is a subquery on the bases of which data will be retrieved from table 1. Here, the “SELECT * WHERE NOT EXISTS” query extracts records from one table, based on excluding the data that match a certain “condition” from the other table.

Method 1: How to Use “SELECT * WHERE NOT EXISTS” With a Simple Subquery?

Let’s see an example to filter data from the “Product” table by excluding records based on a condition that the “ProductId” of the “OrderItem” table is equal to the “Id” of the “Product” table, by running a query:

The output displays the record that doesn’t meet the specified condition as that data is excluded successfully:

Let’s see another example to extract records from the “Supplier” table where the “Id” of this table is not equal to the “SupplierId” of the “Product” table:

Method 2: “SELECT * WHERE NOT EXISTS” With Logical and Relational Operators

The Relational, as well as Logical operators, can be utilized in the conditions with the NOT EXISTS operator to exclude the data based on multiple conditions.

For example, it is required to retrieve the record of the “Product” table by excluding records where the “Id” of this table is equal to the “ProductId” of the “OrderItem” table “AND” the “UnitPrice” is equal to “22”:

Let’s see another example if you want to extract the data of the “Product” table by excluding records where the “Id” of this table is equal to the “ProductId” of the “OrderItem” table and the “UnitPrice” is less than “6”:

The output will display, the extracted records after excluding the records specified in condition:

Method 3: How to Use “SELECT * WHERE NOT EXISTS” With “LIMIT” Clause?

The “LIMIT” Clause can be used with the “SELECT * WHERE NOT EXISTS” query to specify the numbers of output for the result. For example, if you want to extract only “7” rows from the table “Product” by excluding the records where the “Id” of this table is equal to “ProductId” of “OrderItem” AND “UnitPrice” is less than “6”:

In the output, the table will contain 7 rows of the data after excluding the records according to the condition.

Conclusion

In MySQL, The “SELECT * WHERE NOT EXISTS” query extracts the non-existence records based on the specified condition. Moreover, the logical and relational operators and the LIMIT clause can be added to the relevant query. This article demonstrated the usage of the “SELECT * WHERE NOT EXISTS” query in MySQL.