EXIST and NOT EXIST Clause in MySQL

• The `EXISTS` and `NOT EXISTS` are subquery predicates used in combination with the `SELECT` statement to check for the existence or non-existence of rows in a correlated subquery. They are often used in conditional statements or filtering clauses to determine whether a certain condition holds true based on the existence of related data in another table.

• We provide a real-world example of using SQL queries to demonstrate the concepts of "EXISTS" and "NOT EXISTS."

Scenario:

• Imagine you are managing an online bookstore's database and you need to track orders and their associated books. You want to find out if there are any books that have not been ordered yet and identify customers who have made at least one purchase.

Let's assume you have two tables:

• 'books' table with columns:
• `book_id` (unique identifier for the book)
• `title` (title of the book)
• `author` (author of the book)
• `price` (price of the book)

    CREATE TABLE books (

        book_id INT PRIMARY KEY,

        title VARCHAR(255),

        author VARCHAR(255),

        price DECIMAL(10, 2)

    );

    -- Inserting sample books

    INSERT INTO books (book_id, title, author, price)

    VALUES

        (1, 'To Kill a Mockingbird', 'Harper Lee', 12.99),

        (2, '1984', 'George Orwell', 10.50),

        (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 9.99),

        (4, 'Pride and Prejudice', 'Jane Austen', 8.75);

• 'orders' table with columns:
• `order_id` (unique identifier for the order)
• `customer_id` (unique identifier for the customer)
• `book_id` (book ordered in the order)
• `order_date` (date the order was placed)

    CREATE TABLE orders (

        order_id INT PRIMARY KEY,

        customer_id INT,

        book_id INT,

        order_date DATE,

        FOREIGN KEY (customer_id) REFERENCES customers(customer_id),

        FOREIGN KEY (book_id) REFERENCES books(book_id)

    );

    -- Inserting sample orders

    INSERT INTO orders (order_id, customer_id, book_id, order_date)

    VALUES

        (1, 101, 1, '2023-08-05'),

        (2, 102, 2, '2023-08-06'),

        (3, 101, 3, '2023-08-07'),

        (4, 103, 1, '2023-08-08'),

        (5, 102, 4, '2023-08-09');

• Now customers orders some book. we have a schema for customers table.

    CREATE TABLE customers (

        customer_id INT PRIMARY KEY,

        first_name VARCHAR(255),

        last_name VARCHAR(255),

        email VARCHAR(255)

    );

    -- Inserting sample customers

    INSERT INTO customers (customer_id, first_name, last_name, email)

    VALUES

        (101, 'John', 'Doe', 'john@example.com'),

        (102, 'Jane', 'Smith', 'jane@example.com'),

        (103, 'Michael', 'Johnson', 'michael@example.com'),

        (104, 'Emily', 'Brown', 'emily@example.com');

• Now, let's write some SQL queries to implement the concepts of "EXISTS" and "NOT EXISTS."

Find Books That Have Not Been Ordered Yet:

• You want to identify books that haven't been included in any order yet.

    SELECT book_id, title

    FROM books

    WHERE NOT EXISTS (

        SELECT 1

        FROM orders

        WHERE orders.book_id = books.book_id

    );

• This query selects books from the `books` table where there is no corresponding entry in the `orders` table for that book.

Identify Customers Who Have Made At Least One Purchase:

• You want to find customers who have placed at least one order.

    SELECT DISTINCT customer_id

    FROM orders;

• This query selects distinct `customer_id` values from the `orders` table, which represents customers who have made at least one purchase.

Find Customers Who Have Not Made Any Purchase:

• You want to identify customers who haven't placed any orders.

    SELECT customer_id

    FROM customers

    WHERE NOT EXISTS (

        SELECT 1

        FROM orders

        WHERE orders.customer_id = customers.customer_id

    );

• This query selects customer IDs from the `customers` table where there is no corresponding entry in the `orders` table for that customer.

• In these examples, "EXISTS" and "NOT EXISTS" are used to determine whether a correlated subquery (a subquery that references a column from the outer query) returns any rows or not. 

• This helps in identifying records that meet specific conditions or records that do not have corresponding entries in related tables.