SQL Exists Operator - Scaler Topics

Exists in SQL is a powerful keyword used to optimize query performance by testing for any record exists in sql. It returns true if the subquery contains one or more records, making it an efficient tool for conditional checks in complex database queries.

SQL EXISTS Syntax

The syntax of exists operator in SQL is:

Here, SELECT column_name FROM table_name WHERE condition, is the subquery. If the subquery yields any records in its outcome, the EXISTS clause will be considered true, satisfying the condition. Conversely, if the subquery doesn't yield any records, the EXISTS clause will be evaluated as false, thus not meeting the condition.

Demo Database

Let's suppose we have two tables, namely students and books. The student_details table contains the fields such as first_name, last_name, book_id_issued, and the roll_number. The books table contains the fields such as book_id, and the publish_dateand some data has been stored in these fields of both the tables.

students:

books:

SQL EXISTS Examples

Using EXISTS condition with SELECT statement

Given the tables students and books, let's craft a query that utilizes the EXISTS condition with a SELECT statement to find all students who have issued a book that is present in the books table.

Output:

This query checks for the existence of a book_id in the books table that matches the book_id_issued in the students table. The output lists all students who have issued a book that exists in the books table, as per the given data.

Using EXISTS condition with DELETE statement

For this scenario, let's say we want to delete records from the students table where the issued book is no longer present in the books table. This query will use the EXISTS condition with a DELETE statement to achieve this.

Output:

Since this is a DELETE operation, there's no output table to show. However, to understand the effect of this operation, we can describe the expected result based on the given data:

• Before deletion, the students table has entries for 5 students.
• After running the query, since all students have issued books that exist in the books table (based on the provided data), no records would be deleted in this specific scenario.

To check the impact, you could run a SELECT statement before and after the DELETE operation to see the records in the students table.

Note: Always be cautious with DELETE operations and ensure proper conditions are set to avoid unintended data loss.

Using EXISTS condition with UPDATE statement

Suppose we want to update the last name of students who have issued a book published before 2020 to "Senior".

Output:

No direct output since this is an UPDATE operation. If you run a SELECT statement after this update, you would see the following changes in the students table for those who have issued books published before 2020:

Using EXISTS condition with INSERT statement

Imagine we only want to insert a new student record if there is a book with book_id 8 in the books table.

Output:

Since this is an INSERT operation based on a condition, there's no direct output table. However, if the condition is met (and in this case, it is because book_id 8 exists in the books table), the new student record would be added. Running a SELECT statement after this operation would include:

This new record assumes that the roll_number is manually set and does not conflict with existing roll_number values in the students table.