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
- 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);
- Output:
- 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);
- Output:
- 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;
- Output:
- 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;
- Output:
Last Updated :
13 Sep, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...