Test Your MySQL NOT IN Operator Knowledge with Our SQL Quiz

Progress

True / False

The MySQL "NOT IN" clause can be used to exclude records that match any value in a list of specified values.

True False

The "NOT IN" clause in MySQL is used to filter out records that do not match any value in the specified list.

True / False

In MySQL, the "NOT IN" clause can only be used with numerical values.

True False

The "NOT IN" clause in MySQL can be used with both numerical and textual values.

True / False

MySQL will return an empty result set if "NOT IN" is used with an empty list.

True False

Using "NOT IN" with an empty list will not filter out any records, meaning all records will be returned.

True / False

: In MySQL, "NOT IN" can be used with subqueries to filter out results from another table.

True False

The "NOT IN" clause can be used in conjunction with subqueries to exclude records that match the results of the subquery.

True / False

Using "NOT IN" in MySQL is generally faster than using "NOT EXISTS" for the same purpose.

True False

The performance of "NOT IN" vs. "NOT EXISTS" can vary depending on the context, but "NOT EXISTS" is often faster, especially with large datasets and complex subqueries.

True / False

MySQL's "NOT IN" clause can handle NULL values without any issues.

True False

If the list of values in the "NOT IN" clause contains a NULL, the entire comparison will return NULL, resulting in no rows being returned.

True / False

In MySQL, using "NOT IN" with a subquery that returns a NULL value will result in an empty result set.

True False

If the subquery returns a NULL value, the "NOT IN" clause will not match any rows, effectively returning an empty result set.

True / False

In MySQL, "NOT IN" can be used in conjunction with JOIN operations to filter joined results.

True False

NOT IN" can be used alongside JOIN operations to exclude records based on the joined table's values.

True / False

Using indexes in MySQL can improve the performance of queries using the "NOT IN" clause.

True False

Indexes can enhance query performance, including those using "NOT IN", by reducing the amount of data that needs to be scanned.

Q10

True / False

In MySQL certification exams, you may be asked to optimize a query using "NOT IN" by considering alternative clauses like "NOT EXISTS" or "LEFT JOIN ... IS NULL".

True False

Optimization is a key topic in MySQL certification, and understanding when to use "NOT IN" versus other options like "NOT EXISTS" or "LEFT JOIN ... IS NULL" is essential for improving query performance.

Q11

Single Choice

Which of the following MySQL queries will correctly select all rows where the country column is NOT 'USA', 'Canada', or 'Mexico'?

SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'Mexico'); SELECT * FROM customers WHERE country != ('USA', 'Canada', 'Mexico'); SELECT * FROM customers WHERE country NOT IN ('USA', 'Canada', 'Mexico');SELECT * FROM customers WHERE country = 'USA', 'Canada', 'Mexico';

The NOT IN operator is used to exclude rows that match any value in the specified list.

Q12

Single Choice

What will the following MySQL query return?

SQL Code

SELECT product_name FROM products WHERE category_id NOT IN (1, 2, 3);

Products with category_id equal to 1, 2, or 3 Products with category_id not equal to 1, 2, or 3 All productsNo products

The NOT IN operator excludes rows where category_id is 1, 2, or 3, returning only the rows where category_id is different from these values.

Q13

Single Choice

What does this query do?

SQL Code

Consider the following SQL code:
SELECT employee_id FROM employees WHERE department_id NOT IN (5, 7, 9);

It selects employees from department 5, 7, or 9 It selects employees not from department 5, 7, or 9 It deletes employees from department 5, 7, or 9It updates employees in department 5, 7, or 9

The NOT IN operator is used here to exclude employees from the specified departments.

Q14

Single Choice

What will the following MySQL query return if the region_id column contains NULL values?

SQL Code

SELECT * FROM locations WHERE region_id NOT IN (2, 4, 6);

All rows except those with region_id 2, 4, or 6 All rows where region_id is NULL No rowsAll rows with region_id 2, 4, or 6

When NOT IN is used with a list that contains NULL, it returns no rows because the comparison with NULL yields an unknown result.

Q15

Single Choice

Which of the following queries will correctly exclude orders from customers with customer_id 101, 102, or 103?

SELECT * FROM orders WHERE customer_id IN (101, 102, 103); SELECT * FROM orders WHERE customer_id NOT = (101, 102, 103); SELECT * FROM orders WHERE customer_id != 101 OR customer_id != 102 OR customer_id != 103;SELECT * FROM orders WHERE customer_id NOT IN (101, 102, 103);

The correct way to exclude specific customer_id values is by using the NOT IN operator

Q16

Single Choice

What does it return?

SQL Code

Consider this SQL statement:
SELECT name FROM students WHERE grade NOT IN (SELECT grade FROM students WHERE grade < 50);

Names of students with grades less than 50 Names of students with grades 50 or higher All studentsNo students

The subquery selects grades less than 50, and the NOT IN excludes those grades, effectively selecting students with grades 50 or higher.

Q17

Single Choice

What will the following MySQL query return if the status column contains 'active', 'inactive', and NULL values?

SQL Code

SELECT * FROM users WHERE status NOT IN ('inactive');

Users with status 'active' Users with status 'active' or NULL Users with status 'inactiveUsers with status NULL only

The NOT IN operator will exclude rows with 'inactive', but rows with NULL won't be included because NULL is not compared directly.

Q18

Single Choice

What does it do?

SQL Code

Analyze the following query:
SELECT department_name FROM departments WHERE manager_id NOT IN (SELECT manager_id FROM employees WHERE salary > 10000);

Selects department names where managers earn more than 10000 Selects department names where managers earn less than or equal to 10000 Selects all department namesExcludes all department names

The subquery selects manager_id with a salary greater than 10000, and the NOT IN excludes these, returning departments where managers earn 10000 or less.

Q19

Single Choice

What is the result?

SQL Code

Consider this complex query:
SELECT product_name FROM products WHERE product_id NOT IN (
 SELECT product_id FROM order_items WHERE quantity > 5
);

Products with product_id in orders where quantity is 5 or less Products with product_id in orders where quantity is greater than 5 Products that have never been ordered in quantities greater than 5Products with product_id not in any orders

The subquery returns product_id where the quantity ordered is greater than 5, and the NOT IN operator excludes these products, leaving only those never ordered in such quantities.

Q20

Single Choice

Which of the following MySQL queries correctly finds employees who are not assigned to the departments with department_id 10 or 20?

SELECT employee_id FROM employees WHERE department_id = ANY (10, 20); SELECT employee_id FROM employees WHERE department_id NOT IN (10, 20); SELECT employee_id FROM employees WHERE department_id ALL (10, 20);SELECT employee_id FROM employees WHERE department_id = (10, 20);

The NOT IN operator correctly excludes employees from the specified departments, making this the correct answer.

Q21

Multiple Choice

Which query correctly retrieves records from the 'employees' table where the 'department_id' is not in the list (1, 2, 3)?

SQL Code

SELECT *
FROM employees
WHERE department_id NOT IN (1, 2, 3);

SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3); SELECT * FROM employees WHERE department_id <> 1 AND department_id <> 2 AND department_id <> 3; SELECT * FROM employees WHERE department_id IN (1, 2, 3);SELECT * FROM employees WHERE department_id BETWEEN 1 AND 3;

The NOT IN operator is used to filter rows where the 'department_id' is not in the list of values (1, 2, 3).

Q22

Multiple Choice

Identify the correct SQL query that retrieves records from the 'products' table where 'category_id' is not in the list (5, 10, 15).

SQL Code

SELECT *
FROM products
WHERE category_id NOT IN (5, 10, 15);

SELECT * FROM products WHERE category_id NOT IN (5, 10, 15); SELECT * FROM products WHERE category_id <> 5 AND category_id <> 10 AND category_id <> 15; SELECT * FROM products WHERE category_id IN (5, 10, 15);SELECT * FROM products WHERE category_id BETWEEN 5 AND 15;

The NOT IN operator filters rows where the 'category_id' does not match any of the values in the list (5, 10, 15).

Q23

Multiple Choice

Which query correctly selects records from the 'orders' table where the 'status' is not in ('Pending', 'Shipped', 'Delivered')?

SQL Code

SELECT *
FROM orders
WHERE status NOT IN ('Pending', 'Shipped', 'Delivered');

SELECT * FROM orders WHERE status NOT IN ('Pending', 'Shipped', 'Delivered'); SELECT * FROM orders WHERE status <> 'Pending' AND status <> 'Shipped' AND status <> 'Delivered'; SELECT * FROM orders WHERE status IN ('Pending', 'Shipped', 'Delivered');SELECT * FROM orders WHERE status LIKE 'Pending%' OR status LIKE 'Shipped%' OR status LIKE 'Delivered%';

The NOT IN operator is used to filter rows where the 'status' does not match any of the specified values: 'Pending', 'Shipped', or 'Delivered'.

Q24

Multiple Choice

Determine the correct SQL query that retrieves records from the 'customers' table where 'country' is not in ('USA', 'Canada', 'Mexico').

SQL Code

SELECT *
FROM customers
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

SELECT * FROM customers WHERE country NOT IN ('USA', 'Canada', 'Mexico'); SELECT * FROM customers WHERE country <> 'USA' AND country <> 'Canada' AND country <> 'Mexico'; SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'Mexico');SELECT * FROM customers WHERE country BETWEEN 'USA' AND 'Mexico';

The NOT IN operator filters rows where the 'country' does not match any of the specified values: 'USA', 'Canada', or 'Mexico'.

Q25

Multiple Choice

Which SQL query retrieves records from the 'inventory' table where 'item_id' is not in the list (101, 102, 103)?

SQL Code

SELECT *
FROM inventory
WHERE item_id NOT IN (101, 102, 103);

SELECT * FROM inventory WHERE item_id NOT IN (101, 102, 103); SELECT * FROM inventory WHERE item_id <> 101 AND item_id <> 102 AND item_id <> 103; SELECT * FROM inventory WHERE item_id IN (101, 102, 103);SELECT * FROM inventory WHERE item_id BETWEEN 101 AND 103;

The NOT IN operator is used to filter rows where the 'item_id' does not match any of the values in the list (101, 102, 103).

Q26

Multiple Choice

Identify the correct query that retrieves records from the 'employees' table where 'job_title' is not in ('Manager', 'Engineer', 'Analyst').

SQL Code

SELECT *
FROM employees
WHERE job_title NOT IN ('Manager', 'Engineer', 'Analyst');

SELECT * FROM employees WHERE job_title NOT IN ('Manager', 'Engineer', 'Analyst'); SELECT * FROM employees WHERE job_title <> 'Manager' AND job_title <> 'Engineer' AND job_title <> 'Analyst'; SELECT * FROM employees WHERE job_title IN ('Manager', 'Engineer', 'Analyst');SELECT * FROM employees WHERE job_title BETWEEN 'Manager' AND 'Analyst';

The NOT IN operator filters rows where the 'job_title' does not match any of the specified values: 'Manager', 'Engineer', or 'Analyst'.

Q27

Multiple Choice

Which SQL query correctly retrieves records from the 'sales' table where 'region' is not in ('North', 'South', 'East')?

SQL Code

SELECT *
FROM sales
WHERE region NOT IN ('North', 'South', 'East');

SELECT * FROM sales WHERE region NOT IN ('North', 'South', 'East'); SELECT * FROM sales WHERE region <> 'North' AND region <> 'South' AND region <> 'East'; SELECT * FROM sales WHERE region IN ('North', 'South', 'East');SELECT * FROM sales WHERE region BETWEEN 'North' AND 'East';

The NOT IN operator filters rows where the 'region' does not match any of the specified values: 'North', 'South', or 'East'.

Q28

Multiple Choice

Determine the correct SQL query that selects records from the 'projects' table where 'status_id' is not in (2, 4, 6).

SQL Code

SELECT *
FROM projects
WHERE status_id NOT IN (2, 4, 6);

SELECT * FROM projects WHERE status_id NOT IN (2, 4, 6); SELECT * FROM projects WHERE status_id <> 2 AND status_id <> 4 AND status_id <> 6; SELECT * FROM projects WHERE status_id IN (2, 4, 6);SELECT * FROM projects WHERE status_id BETWEEN 2 AND 6;

The NOT IN operator filters rows where the 'status_id' does not match any of the values in the list (2, 4, 6).

Q29

Multiple Choice

Which query correctly retrieves records from the 'users' table where 'role_id' is not in (1, 3, 5)?

SQL Code

SELECT *
FROM users
WHERE role_id NOT IN (1, 3, 5);

SELECT * FROM users WHERE role_id NOT IN (1, 3, 5); SELECT * FROM users WHERE role_id <> 1 AND role_id <> 3 AND role_id <> 5; SELECT * FROM users WHERE role_id IN (1, 3, 5);SELECT * FROM users WHERE role_id BETWEEN 1 AND 5;

The NOT IN operator filters rows where the 'role_id' does not match any of the values in the list (1, 3, 5).

Q30

Multiple Choice

Identify the correct SQL query that retrieves records from the 'transactions' table where 'payment_method' is not in ('Credit Card', 'PayPal', 'Bank Transfer').

SQL Code

SELECT *
FROM transactions
WHERE payment_method NOT IN ('Credit Card', 'PayPal', 'Bank Transfer');

SELECT * FROM transactions WHERE payment_method NOT IN ('Credit Card', 'PayPal', 'Bank Transfer'); SELECT * FROM transactions WHERE payment_method <> 'Credit Card' AND payment_method <> 'PayPal' AND payment_method <> 'Bank Transfer'; SELECT * FROM transactions WHERE payment_method IN ('Credit Card', 'PayPal', 'Bank Transfer');SELECT * FROM transactions WHERE payment_method BETWEEN 'Credit Card' AND 'Bank Transfer';

The NOT IN operator filters rows where the 'payment_method' does not match any of the specified values: 'Credit Card', 'PayPal', or 'Bank Transfer'.

MySQL Database Quiz Questions