How to Filter Database Rows with WHERE in MySQL

TL;DR

Use a MySQL WHERE clause after SELECT and FROM to return only rows that satisfy specified conditions. Comparison operators filter exact values, numbers, and dates, logical operators combine conditions, parentheses control how related conditions are evaluated, and LIKE searches for patterns with percent signs and underscores.

Transcript

hello everybody in this lesson we're going to be taking a look at the where Clause the where Clause is used to help filter our records or our rows of data whereas the select statement is used to help filter or select our actual columns so when we're using the where Clause we're only going to return the rows that fulfill a specific condition let's t... Read More

Key Insights

• The WHERE clause is a row filter that returns only records satisfying a specified condition, whereas SELECT determines which columns are included in the result. A condition begins by naming the column to evaluate and then applying an operator and comparison value.
• The equal sign is a comparison operator that requires an exact match. Filtering where first_name equals Leslie returns the single Leslie row in the demonstrated table, while searching for only part of an exact name does not return that row.
• Greater than and less than comparisons exclude the stated boundary value. A salary greater than 50,000 excludes Tom and Jerry because each makes exactly 50,000, while adding the equal sign includes records at the boundary as well.
• The not-equal operator is written with an exclamation point followed by an equal sign. Applying it to gender with female as the excluded value returns the rows whose gender is male in the demographics table used during the lesson.
• Date values can be filtered with comparison operators using the demonstrated year-month-day format. The condition birth_date greater than 1985-01-01 returns rows with birth dates later than that specified date, showing that WHERE is not limited to numbers and names.
• The AND operator requires every connected condition to be true, while OR requires either connected condition to be true. NOT reverses a condition, as shown when NOT gender equals male is used to represent records whose gender is female.
• Parentheses isolate a group of logical conditions and clarify how AND and OR work together. The example groups first_name equals Leslie and age equals 44, then uses OR to include anyone whose age is greater than 55.
• The LIKE operator searches for patterns instead of exact values. A percent sign allows anything before or after a sequence, while each underscore represents exactly one character position. Combining them can require a prefix and minimum character pattern while permitting additional trailing characters.

Questions & Answers

Q: How do you filter rows with a WHERE clause in MySQL?

Place WHERE after the SELECT and FROM portion of the query, name the column to evaluate, and write a condition using a comparison value. For example, filtering where first_name equals Leslie returns only the row whose first name exactly matches Leslie. The WHERE clause controls which rows qualify, while SELECT controls which columns are returned.

Q: What comparison operators can be used in a MySQL WHERE clause?

The lesson demonstrates equal to, not equal to, greater than, greater than or equal to, less than, and less than or equal to. Equal to searches for an exact value, while not equal to excludes that value. Greater-than and less-than operators compare values, and adding an equal sign includes the specified boundary value.

Q: Why does greater than 50,000 exclude salaries of exactly 50,000?

A greater-than condition includes only values above the stated comparison value, so a salary of exactly 50,000 does not satisfy salary greater than 50,000. In the example, this excludes Tom and Jerry. Changing the operator to greater than or equal to includes both people because the revised condition accepts 50,000 and every higher salary.

Q: How can AND, OR, and NOT combine WHERE conditions?

AND requires both connected conditions to be true for a row to qualify. OR allows a row when either condition is true. NOT reverses the condition that follows it. For example, birth_date greater than 1985 combined with AND gender equals male returns rows satisfying both, while OR returns rows satisfying either condition.

Q: Why should parentheses be used with AND and OR conditions?

Parentheses isolate conditions that should be evaluated as a group. In the lesson, first_name equals Leslie and age equals 44 are placed together, then an OR condition allows ages greater than 55. A row is therefore returned if it satisfies the grouped Leslie and age conditions, or if it satisfies the separate age condition.

Q: How does LIKE differ from an exact match in MySQL?

An exact comparison requires the stored value to match the supplied text completely. Searching for Jerry with an equal sign works, but searching for only Jer does not. LIKE supports patterns, so Jer followed by a percent sign finds a name beginning with Jer even when additional characters appear after that sequence.

Q: What do percent signs and underscores mean in a LIKE pattern?

A percent sign permits anything in the position it covers, including characters before or after the required sequence. An underscore represents one specific character position. The pattern A followed by two underscores therefore matches a three-character name beginning with A, while adding more underscores or a trailing percent sign changes the permitted name length and pattern.

Q: How can LIKE filter names and birth dates by pattern?

LIKE can search names by their beginning, internal sequence, or character positions. A followed by a percent sign returns names beginning with A, while percent signs around er find names containing that sequence. It can also filter birth dates: using a pattern beginning with 1989 and followed by a percent sign returns the demonstrated person born in 1989.

Summary & Key Takeaways

• The WHERE clause filters rows according to a condition, while the SELECT statement determines which columns appear. Conditions can compare a column with text, numbers, or dates. For example, a first name can equal Leslie, a salary can exceed 50,000, or a birth date can be later than 1985-01-01.

• Comparison operators include equal to, not equal to, greater than, greater than or equal to, less than, and less than or equal to. Boundary values matter: a salary greater than 50,000 excludes exactly 50,000, while greater than or equal to 50,000 includes Tom and Jerry in the demonstrated table.

• Logical operators combine conditions, and parentheses isolate related expressions when AND, OR, and NOT appear together. LIKE provides pattern matching rather than exact matching. A percent sign permits any surrounding characters, while each underscore represents one specific character position. Both wildcard types can be combined for more precise searches.