JOIN performance considerations in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to join two tables on the common column.

MySQL

SELECT * FROM orders JOIN customers ON orders.customer_id [1] customers.id;

Drag options to blanks, or click blank then click option'

A=

B!=

C<

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' instead of '=' causes no matching rows.

Using '<' or '>' does not correctly join related rows.

The JOIN condition must use '=' to match rows where the customer IDs are equal.

2fill in blank

medium

Complete the code to select only orders with matching customers using INNER JOIN.

MySQL

SELECT orders.id, customers.name FROM orders [1] JOIN customers ON orders.customer_id = customers.id;

Drag options to blanks, or click blank then click option'

AFULL

BLEFT

CRIGHT

DINNER

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN returns unmatched rows from orders.

Using FULL JOIN is not supported in MySQL.

INNER JOIN returns only rows with matching keys in both tables.

3fill in blank

hard

Fix the error in the JOIN condition to improve performance by using indexed columns.

MySQL

SELECT * FROM products JOIN categories ON products.[1] = categories.id;

Drag options to blanks, or click blank then click option'

Adescription

Bname

Ccategory_id

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on non-indexed columns causes slow queries.

Using columns unrelated to the join condition.

Joining on the foreign key 'category_id' which is indexed improves performance.

4fill in blank

hard

Fill both blanks to write a query that uses an index-friendly JOIN and filters results efficiently.

MySQL

SELECT o.id, c.name FROM orders o [1] JOIN customers c ON o.customer_id [2] c.id WHERE c.status = 'active';

Drag options to blanks, or click blank then click option'

AINNER

BLEFT

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN returns unmatched rows, slowing query.

Using '!=' in join condition returns incorrect results.

INNER JOIN with '=' on indexed keys returns matching active customers efficiently.

5fill in blank

hard

Fill all three blanks to write a query that joins three tables efficiently with proper conditions.

MySQL

SELECT p.name, c.name, s.quantity FROM products p [1] JOIN categories c ON p.category_id [2] c.id [3] stock s ON p.id = s.product_id WHERE s.quantity > 0;

Drag options to blanks, or click blank then click option'

AINNER

B=

CINNER JOIN

DLEFT

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN may include unwanted rows.

Using wrong join conditions causes slow queries.

Use INNER JOINs with '=' conditions to join tables on indexed keys and filter stock with quantity > 0.