CODEWITHSUNDEEP
mysqljoincase-insensitive
Michael Sheaver
Michael Sheaver

Reputation: 2119

MySQL Case-insensitive Join

I have a table named work that contains registration info for everyone in the enterprise, like this:

Table: work

FirstName LastName SponsorshipStatus EnrollmentStatus AdjudicationStatus
--------- -------- ----------------- ---------------- ------------------
JANE      DOE      Complete          Incomplete       Incomplete
JOHN      DOE      Complete          Complete         Incomplete
MONTY     PYTHON   Complete          Complete         Complete
MARY      POPPINS  Complete          Complete         Complete

A department manager gives me a list of her employees like the one immediately below and she needs a status update:

Table: employees

FirstName LastName
--------- --------
John      Doe
Mary      Poppins
Humpty    Dumpty

Knowing that the case of the two tables do not match, I try the following query:

SELECT employees.FirstName, employees.LastName,
    SponsorshipStatus, EnrollmentStatus, AdjudicationStatus
    FROM employees LEFT JOIN work
    ON (UPPER(employees.FirstName) LIKE UPPER(work.FirstName)
    AND UPPER(employees.LastName) LIKE UPPER(work.LastName));

...and it produces the following:

Query Result:

FirstName LastName SponsorshipStatus EnrollmentStatus AdjudicationStatus
--------- -------- ----------------- ---------------- ------------------
JOHN      DOE      NULL              NULL             NULL
MARY      POPPINS  NULL              NULL             NULL
HUMPTY    DUMPTY   NULL              NULL             NULL

This is what I expect to get from the query:

FirstName LastName SponsorshipStatus EnrollmentStatus AdjudicationStatus
--------- -------- ----------------- ---------------- ------------------
JOHN      DOE      Complete          Complete         Incomplete
MARY      POPPINS  Complete          Complete         Complete
HUMPTY    DUMPTY   NULL              NULL             NULL

What am I doing wrong here? The left join is working correctly, but it is not doing the match and pulling in the relevant data from the work table, as evidenced by all the nulls.

I have already looked at numerous posts here, and none of them seem to clearly help me here.

Upvotes: 2

Views: 14408

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53870

Since MySQL 5.0.3, when you retrieve values for a VARCHAR column, any trailing spaces are included. This can be a concern because when using = comparison, trailing spaces are ignored, whereas with LIKE trailing spaces are considered.

SELECT 'foo' = 'foo ';
// Returns true

SELECT 'foo' LIKE 'foo ';
// Returns false

So, be sure to use the appropriate operator.

Since the collation on the column is CASE INSENSITIVE, you don't need the UPPER() function. In fact, dropping UPPER will allow MySQL to utilize an index on the column (using a function on a column generally negates the use of an index).

 // With case insensitive collation
 SELECT 'FOO' = 'foo';
 // Returns true

 // LIKE will work the same
 SELECT 'FOO' LIKE 'foo';
 // Returns true

Since = and LIKE return the same results for you, however, I don't know what the issue is.

You should also specify the table name for all columns in a join to avoid any chance of ambiguous columns (if you were to add similar columns to the other table in the future), and for readability:

SELECT employees.FirstName, employees.LastName,
    work.SponsorshipStatus, work.EnrollmentStatus, work.AdjudicationStatus
    FROM employees
    LEFT JOIN work
    ON employees.FirstName = work.FirstName
    AND employees.LastName = work.LastName;

However, if the columns were ambiguous, you should get an error.

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93795

You're misusing LIKE. Don't use LIKE unless you're pattern-matching. Change 

UPPER(employees.FirstName) LIKE UPPER(work.FirstName)

to

UPPER(employees.FirstName) = UPPER(work.FirstName)

Do the same with the lastname, too.

Upvotes: 10

Related Questions