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.
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name(s) FROM table_name WHERE condition);
Consider the following two relation “Customers” and “Orders”.
- 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);
- Using NOT with EXISTS
Fetch last and first name of the customers who has not placed any order.
SELECT lname, fname FROM Customer WHERE NOT EXISTS (SELECT * FROM Orders WHERE Customers.customer_id = Orders.c_id);
- 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.cid AND Customers.lname = 'Mehra');
SELECT * FROM Orders;
- 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;
This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
- DBMS | Building a Data Warehouse
- Difference between MySQL and MS SQL Server
- DBMS | Mapping Strategies for File records into Blocks
- DBMS | Attributes to Relationships in ER Model
- Difference between RDBMS and DBMS
- Data-Mining | Fact Constellation in Data Warehouse modelling
- Data Mining | ETL process
- DBMS | OLAP Operations
- Comparisons between Data Warehousing v/s Data Mining
- Comparisons between DELETE vs DROP in SQL
- Introduction to Apache Cassandra
- Comparisons between SQL vs PLSQL
- Primary key Vs Unique key
- Comparison - Centralized, Decentralized and Distributed Systems