Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-exists-operator/ below:

MySQL EXISTS Operator - GeeksforGeeks

The EXISTS operator in MySQL is a powerful boolean operator used to test the existence of any record in a subquery. It returns true if the subquery yields one or more records, enabling efficient data retrieval and manipulation, particularly in large datasets. The operator is often paired with subqueries to conditionally execute parent queries.

EXISTS operator is used in MySQL to test for the existence of any record in a subquery. EXISTS Operator works with a parent query and a child query. A parent query will execute if the child query returns any value.

Note:  The EXISTS operator can be used with the NOT operator to negate its results, helping to determine if a given record is unique. 

Syntax

EXISTS syntax in MySQL is given below:

SELECT column_name01, column_name02......

FROM table_name

WHERE EXISTS (subquery);

To understand how to use EXISTS Operator in MySQL, let's look at some examples of EXISTS in MySQL. We will demonstrate a completely practical example from the first step to the end. Follow each step, to use MySQL EXISTS.

First, let's create a demo table on which we will perform the MySQL queries.

We create the first table "geeksforgeeks" in this example. To create this table and insert values, use the following SQL queries:

CREATE TABLE geeksforgeeks(
  id varchar(100) PRIMARY KEY,
  name varchar(100),
  rank int
 );
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('vish3001','Vishu',01);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('neeraj20','Neeraj',02);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('aayush15','Aayush',03);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('sumit25','Sumit',04);
SELECT * from geeksforgeeks;

Output:

To create the second table "Courses", write the following SQL queries:

CREATE TABLE courses(
  id varchar(100),
  course_name varchar(100),
  duration int,
 );
INSERT INTO courses(id,course_name,duration)

VALUES('vish3001','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('sumit25','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('vish3001','Java',40);
INSERT INTO courses(id,course_name,duration)
VALUES('aayush15','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('neeraj20','Java',50);
SELECT * FROM courses;

Output:

Now we are done with creating a table, let's move to our example.

In this example, we are going to display all the id, and name of the geeksforgeeks table if and only if we have the same id's in our courses table

SELECT id,name
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id);

Output:

We can notice all the id and names of the geeksforgeeks table are displayed here.

In this example we are going to display all the records of the geeksforgeeks table where there exists a record where the id of the geeeksforgeeks table is equal to the id of the courses table and the course duration should be 30.

SELECT id,name
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id and courses.duration = 30 );

Output:

We can see that all the id(s), and names of the geeksforgeeks table are displayed who has taken a course of duration 30 (i.e. Python).

In this example, we are going to delete all those rows whose course duration is 50. We will use the DELETE statement with EXISTS operator in this example. Let's implement this.

DELETE FROM geeksforgeeks
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id and courses.duration = 50 );
    
SELECT * from geeksforgeeks;

Output:

In the above image, we can see that the row with id "neeraj20" has been deleted from the table. From the course table, we can notice that column id = "neeraj20" is the only row to have duration =50. Therefore, it has been deleted from the table.

Although both the IN operator and EXISTS operator seem to perform similar types of tasks, there is a vast difference between their implementation and uses. Let's see how they are different from each other.

IN Operator

EXISTS Operator

IN operator is used to search a data in the table that matches the data within the specified set or subquery

EXISTS operator checks for the existence of a subquery.

It is less efficient for large datasets as it continues to execute until it traverses the whole.

EXISTS operators are efficient for large datasets as they stop execution when matching data is found

Comparisons between parent queries and subqueries happen.

Comparisons between parent queries and subqueries do not happen.

IN operator is used to decrease the number of "=" operators. We can club all those conditions in one set or subquery.

The exists operator is used to check for the existence of a subquery with more complex conditions than the IN operator conditions.

It checks for the existence of specific values from a finite set or small set.

It checks for the existence of a row matching specific complex conditions.

We are using the geeksforgeeks table to get all the rows with rank lies in the set ('3' , '2', '1').

Note: We are using the same tables from the Examples block.

SELECT *
FROM geeksforgeeks 
WHERE rank IN ('3','2','1');

In the above example, we can observe all those rows with rank columns belonging to the set ('3','2','1') are displayed in output.

Let's fetch all the values from the table geeksforgeeks where the id column of geeksforgeeks matches with the id columns of the courses table.

Query:

SELECT *
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id);

The MySQL EXISTS operator is a valuable tool for efficiently handling subqueries, particularly in large datasets. It allows for conditional execution of queries based on the presence of specific records, offering flexibility and performance benefits over the IN operator in complex scenarios. Understanding how to effectively use the EXISTS operator can enhance your ability to manage and query relational databases in MySQL.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4