SQL | EXISTS AND NOT EXISTS OPERATORS

In this article, we'll learn about the EXISTS and NOT EXISTS operators used in Correlated Subqueries. If you want to get hold of Correlated Subqueries you can check out this link. For ALL and ANY operators, you can check this link. 1. EXISTS: It is used to check the existence of any record in a subquery. It returns true if one or more than one tuples(records) are present in the table. Syntax: SELECT column_name_1, column_name_2, .., column_name_n
FROM table_name
WHERE EXISTS(SELECT column_name FROM table_name WHERE condition); Example: To fetch the product name of the orders with a single quantity. Products Orders SELECT product_name
FROM Products
WHERE EXISTS(SELECT * FROM Orders WHERE Products.product_id =
Orders.product_id AND quantity=1);) 2. NOT EXISTS: It negates the logic of EXISTS Operator. The NOT EXISTS operator returns true if the subquery returns no record. Syntax: SELECT column_name_1, column_name_2, .., column_name_n
FROM table_name
WHERE NOT EXISTS(SELECT column_name FROM table_name WHERE condition); Example: To match all products records that have no associated orders quantity less than 2. SELECT product_name
FROM Products
WHERE NOT EXISTS(SELECT * FROM Orders WHERE Products.product_id =
Orders.product_id AND quantity>=2); Happy Learning! Follow us on Instagram @programmersdoor Join us on Telegram @programmersdoor Do comment if you find any error in the above article, or if you have an easier way to explain the topic. Follow Programmers Door for more. #blog #interview #placement #learn #computer #science #CorrelatedSubquery #Subquery #DBMS #ALL #ANY

        Contact Us

programmersdoor@gmail.com

  • LinkedIn
  • Facebook
  • Instagram

©2023 by Programmers Door