MariaDB Tutorial / Filter Rows with WHERE, AND, OR, IN, BETWEEN, and LIKE

WHERE keeps rows whose condition evaluates to true. Combine comparisons with AND and OR, and use IN, BETWEEN and LIKE for lists, ranges and text patterns; check parentheses and NULL behavior so the filter selects the intended rows.

Define the condition separately from the output columns

SELECT product_name chooses what to display; WHERE price >= 8000 chooses which rows qualify. A false or unknown condition does not pass WHERE. Compare numeric columns with appropriate numeric values and strings with quoted strings rather than relying on unnecessary implicit conversion. Apply the NULL rules in Compare NULL Correctly with IS NULL and COALESCE to missing values, then separately define result order and size using Sort and Paginate Results with ORDER BY and LIMIT.

Check the MariaDB documentation for the syntax and conditions used here: LIKE, operator precedence.

Prepare the comparison data

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_15_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_15_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p15_price CHECK (price >= 0),
  CONSTRAINT ck_p15_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_15_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

Stationery contains IDs 1, 2 and 5; household contains 3 and 4; electronics contains 6. Zero-stock products are 2 and 6. Change one condition at a time against this fixed fixture so the reason for each result difference is visible.

Compare boundaries and AND/OR parentheses

SELECT product_id, product_name, price FROM products_15_lab
WHERE price >= 8000 ORDER BY product_id;

The inclusive 8000 comparison returns IDs 3, 4 and 6. Changing >= to > removes the two products priced exactly 8000 and leaves ID 4. Restore the original condition after comparing. Finding the highest-priced product additionally needs an ordering and a result-size rule.

SELECT product_id FROM products_15_lab
WHERE (category = '문구' OR category = '생활') AND stock > 0
ORDER BY product_id;
SELECT product_id FROM products_15_lab
WHERE category = '문구' OR category = '생활' AND stock > 0
ORDER BY product_id;

The parenthesized query returns 1, 3, 4 and 5: stationery or household products that have stock. Without the parentheses, AND binds more tightly than OR, so the zero-stock stationery product 2 is also included. Write the intended grouping explicitly instead of asking readers to reconstruct precedence.

Use IN lists and inclusive BETWEEN ranges

SELECT product_id FROM products_15_lab
WHERE category IN ('문구', '전자') ORDER BY product_id;
SELECT product_id FROM products_15_lab
WHERE price BETWEEN 1500 AND 8000 ORDER BY product_id;

The IN example returns 1, 2, 5 and 6. BETWEEN 1500 AND 8000 returns 1, 2, 3, 5 and 6, including both endpoint prices. For this numeric condition it is equivalent to price >= 1500 AND price <= 8000.

A date-and-time endpoint needs more care. An upper endpoint at midnight excludes later times on that day. A start-inclusive, next-day-exclusive range is often easier to express correctly; this exercise uses numeric prices to make the endpoints unambiguous.

Distinguish LIKE wildcards from literal characters

SELECT product_id, product_name FROM products_15_lab
WHERE product_name LIKE '%컵%' ORDER BY product_id;
SELECT product_id, product_name FROM products_15_lab
WHERE product_name LIKE '__' ORDER BY product_id;

The percent wildcard matches zero or more characters and an underscore matches one character. The Korean substring pattern selects the mug, ID 3. Two underscores select the two-character Korean product names at IDs 1, 2 and 5. Character counts are not UTF-8 byte counts.

SELECT '진행률 10%' LIKE '%!%%' ESCAPE '!' AS has_percent;

The literal-percent example declares ! as the escape character, so !% means a percent sign rather than a wildcard; has_percent is 1. Parameterize user-provided values and decide separately whether a user’s percent or underscore should act as a wildcard. Collation affects text comparisons, including case behavior.

Investigate empty or overbroad results

Display the primary key and every filtered column, then add conditions gradually. Check AND versus OR, inclusive boundaries, unexpected spaces and actual stored types. Before reusing a condition in UPDATE or DELETE, inspect its selected rows. Use IS NULL instead of = NULL, and review the NULL comparison guide before using an exclusion list that may contain NULL.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_15_lab;