MySQL Exists Operator

In MySQL exists is an operator, a type of boolean operator said to return true or false as a result. EXISTS is usually used with a subquery and checks the existence of the specified data in the subquery.

TRUE represents 1, and FALSE as 0 in EXISTS, so we can use this with SELECT, UPDATE, DELETE, and INSERT statements.

Syntax

SELECT columns  
FROM tablename  
WHERE  (NOT)EXISTS (  
    subquery    
);  

• The NOT is used in the query to negate the EXISTS operator.
• It will eventually return true when the subquery does not produce any row otherwise false.

Example

Let us see how this works in real time.

Let us create two tables, WorkEmployee and EmpJoinDetails.

CREATE TABLE WorkEmployee(  
  cID int NOT NULL,  
  name varchar(35),  
  Domain varchar(25));  
  
CREATE TABLE EmpJoinDetails (  
    cID int,   
    empstatus varchar(45),  
    join_date date  
);  

• From this, we will check whether the CID equally matches that of the CID in both the tables matching data using the EXIST operator.
• Now let us insert some data into it.

INSERT INTO WorkEmployee(cID, name, Domain)   
VALUES (101, 'George', 'Tester'),  
(102, 'Ape', 'Developer'),  
(103, 'Ursula', 'BA'),
(104, NULL, NULL);  

INSERT INTO EmpJoinDetails (cID, empstatus, join_date)   
VALUES ('101', 'Probation', '2020-01-10'),  
('103', 'Permanent', '2020-02-12'),  
('104', 'Probation', '2020-02-15');

So these are the tables.

Now using the EXISTS operator, let us check the name and domain of the employee match with the id on the EmpJoinDetails.

SELECT name, Domain FROM WorkEmployee we  
WHERE EXISTS (SELECT * FROM EmpJoinDetails ejd
WHERE we.cID = ejd.cID);  

Now let us use the not EXISTS operator to check the CID does not have a match.

SELECT name, Domain FROM WorkEmployee we  
WHERE NOT EXISTS (SELECT * FROM EmpJoinDetails ejd   
WHERE we.cID = ejd.cID); 

So this CID is not present in one of the tables.

Conclusion:

So this is how we can use the EXISTS operator more effectively.