What does the EXISTS operator do in MySQL?
A
Checks if a column exists in a table
B
Checks if a subquery returns any rows
C
Checks for duplicate records
D
Checks if a value exists in a list
Analysis & Theory
EXISTS returns TRUE if the subquery returns one or more rows.
Which of the following best describes how EXISTS works?
A
It retrieves all matching rows from a subquery
B
It returns TRUE if the subquery returns at least one row
C
It only checks for NULL values
Analysis & Theory
EXISTS returns a boolean result depending on whether the subquery returns any rows.
What will this query do?
`SELECT name FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.id = orders.customer_id);`
B
Returns customers who have at least one order
C
Returns orders placed by customers
Analysis & Theory
EXISTS checks if there is any order matching the customer's ID, returning only such customers.
What is the result if the subquery in an EXISTS clause returns no rows?
B
The main query returns all rows
C
EXISTS returns FALSE, and the main query excludes that row
Analysis & Theory
If the subquery returns no rows, EXISTS evaluates to FALSE.
Can EXISTS be used with correlated subqueries?
A
No, it only works with non-correlated subqueries
B
Yes, and it's most effective when used that way
D
Only with aggregate functions
Analysis & Theory
EXISTS is often used with correlated subqueries that depend on values from the outer query.
What is a key performance advantage of EXISTS?
A
It sorts results by default
B
It returns all matching rows
C
It stops processing once it finds the first match
D
It ignores WHERE conditions
Analysis & Theory
EXISTS stops evaluating as soon as a single row is found, making it efficient for existence checks.
Which of the following is equivalent to EXISTS when checking for non-existence?
Analysis & Theory
NOT EXISTS is used to return TRUE if the subquery returns no rows, the opposite of EXISTS.
What does this query return?
`SELECT name FROM suppliers WHERE NOT EXISTS (SELECT * FROM products WHERE suppliers.id = products.supplier_id);`
A
Suppliers with products
C
Suppliers without products
D
Products without suppliers
Analysis & Theory
This query finds suppliers for whom no products exist in the `products` table.
What type of subquery is typically used with EXISTS?
Analysis & Theory
EXISTS commonly uses correlated subqueries that refer to the outer query's row.
Which query correctly uses EXISTS?
A
SELECT name FROM employees WHERE EXISTS employees.salary > 50000;
B
SELECT name FROM employees WHERE salary EXISTS (50000);
C
SELECT name FROM employees WHERE EXISTS (SELECT * FROM salaries WHERE salaries.emp_id = employees.id AND amount > 50000);
D
SELECT EXISTS FROM employees;
Analysis & Theory
The third option correctly uses EXISTS with a correlated subquery to check if a salary exists above 50,000.