Open In App

SQL | EXISTS

Improve
Improve
Like Article
Like
Save
Share
Report

The EXISTS condition in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not. The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement. Syntax:

SELECT column_name(s) 
FROM table_name
WHERE EXISTS 
  (SELECT column_name(s) 
   FROM table_name
   WHERE condition);

Examples: Consider the following two relation “Customers” and “Orders”.

Queries

  1. Using EXISTS condition with SELECT statement To fetch the first and last name of the customers who placed atleast one order.
SELECT fname, lname 
FROM Customers 
WHERE EXISTS (SELECT * 
              FROM Orders 
              WHERE Customers.customer_id = Orders.c_id);
  1. Output:
  2. Using NOT with EXISTS Fetch last and first name of the customers who has not placed any order.
SELECT lname, fname
FROM Customers
WHERE NOT EXISTS (SELECT * 
                  FROM Orders 
                  WHERE Customers.customer_id = Orders.c_id);
  1. Output:
  2. Using EXISTS condition with DELETE statement Delete the record of all the customer from Order Table whose last name is ‘Mehra’.
DELETE 
FROM Orders
WHERE EXISTS (SELECT *
              FROM customers
              WHERE Customers.customer_id = Orders.c_id
              AND Customers.lname = 'Mehra');
SELECT * FROM Orders;
  1. Output:
  2. Using EXISTS condition with UPDATE statement Update the lname as ‘Kumari’ of customer in Customer Table whose customer_id is 401.
UPDATE Customers
SET lname = 'Kumari'
WHERE EXISTS (SELECT *
              FROM Customers
              WHERE customer_id = 401);
SELECT * FROM Customers;
  1. Output:

Last Updated : 13 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads