MySQL EXISTS: Examples

Examples
Sample Database

#1

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);

#2

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);

#3

SELECT 
    employeenumber, firstname, lastname, extension
FROM
    employees
WHERE
    EXISTS( SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco'
                AND offices.officeCode = employees.officeCode);

#4

SELECT 
    *
FROM
    customers_archive;

#5

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (SELECT 
            customerNumber
        FROM
            orders);

#6

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (SELECT 
            customerNumber
        FROM
            orders);

#7

SELECT 
    employeenumber, firstname, lastname
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            offices.city = 'San Francisco');

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result