Equals and not equals (=, !=)

Concepts covered: Filtering

= is the operator Tom's query used, and it is stricter than the word equals suggests. The database takes the value stored in the column and the literal written in the query and compares them character for character. 'Engineering' matches 'Engineering' and nothing else. 'engineering', with a lowercase e, fails: E is not the same character as e. 'Engineering ' with a trailing space fails, because the stored value has 11 characters and the literal has 12. A match means every character identical in every position, with no character missing and none extra.

PostgreSQL compares text by that strict rule, as do SQLite and Oracle, and the examples on this page follow it. What decides it is the column's collation, the setting that says how 2 strings compare. MySQL and SQL Server ship with case-insensitive collations by default, so on those engines department = 'engineering' finds all 3 Engineering rows. SQL Server, like MySQL's older PAD SPACE collations, also ignores trailing spaces in =. A literal written exactly as the column stores it matches on every engine, which makes that the only spelling worth relying on.

The quotes tell the database what kind of value it is reading. Text goes in single quotes, so 'Sales' is the 5-character word. A number goes bare, so salary = 80000 compares the stored number 80000 to the literal 80000, and employee_id = 6 picks Ruiz by key. In standard SQL, double quotes name an identifier such as a column. PostgreSQL follows that rule, as do Oracle and SQL Server, so there department = "Sales" asks whether department equals a column called Sales, which does not exist, and the query fails before it reads a row. MySQL in its default mode reads double quotes as text, and SQLite falls back to text when no such column exists, so there the same query returns Ruiz and Adams without any error. Single quotes are the only spelling that means text on every engine.

Everyone except

Rachel's second request was the reverse of the first: everyone outside Engineering. != reads as does not equal, and <> is the same operator in an older spelling. Both are accepted in every major database, and a team usually settles on one so that queries read consistently. The test is the mirror image of =: the row passes when the stored value and the literal differ in any character. Here it runs over the same 8 rows.

SELECT emp_name, department
FROM employees
WHERE department != 'Engineering'

This time 5 rows pass, and they are the exact 5 that failed the first query: Osei, Tanaka, Ruiz, Adams and Novak. = 'Engineering' and != 'Engineering' split the table in 2 with nothing left over, 3 rows on one side and 5 on the other. That clean split holds as long as every row has a department. A row with NULL in department fails both tests and lands in neither result; the advanced tier of this track, in its section on NOT IN and NULL, explains why a missing value refuses to be compared.

Match

Exact match keeps the 3 Engineering rows.

SELECT emp_name, department
FROM employees
WHERE department = 'Engineering'

Exclude

The same literal with != keeps the other 5.

SELECT emp_name, department
FROM employees
WHERE department != 'Engineering'

Lowercase

One lowercase letter and the match finds 0 rows.

SELECT emp_name, department
FROM employees
WHERE department = 'engineering'

The third tab is the mistake that costs the most time in practice, because the query runs without complaint and returns an empty result. The syntax is valid. The literal 'engineering' matches none of the 8 stored values, so the result is correct and useless at the same time. When a text filter returns 0 rows unexpectedly, the first thing to check is the spelling and casing of the literal against the values the column really holds, which a quick SELECT department FROM employees with no filter will show.

The same strictness applies to numbers, in a quieter way. salary = 80000 keeps Ruiz and nobody else, because an exact salary is a rare thing to share. salary != 80000 keeps the other 7. Equality on a numeric column is useful for keys and codes, where values are meant to repeat, and rarely useful for amounts, where the next section's operators do the work.

Under a case-sensitive collation != is just as strict: department != 'sales' with a lowercase s keeps all 8 rows, including the 2 in Sales, because no stored value equals that literal. On MySQL or SQL Server defaults the same query keeps 6.

About this lesson

This section is part of the Filtering: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.