Tutorialspoint.dev

SQL | EXISTS


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);

    Output:

  2. 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);

    Output:

  3. 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;

    Output:
  4. 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:

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



This article is attributed to GeeksforGeeks.org

tags:

DBMS DBMS

You Might Also Like

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter