Unusual difference between using 'like' and '=' in SQL. Can somebody explain?

Factual Questions
1

select * from table1 where upper(firstname) = (select upper(forename) from table2 where accountnumber = ‘xxxxxxx’ and pinnumber = ‘xxxx’)

select * from table1 where upper(firstname) like (select upper(forename) from table2 where accountnumber = ‘xxxxxxx’ and pinnumber = ‘xxxx’)
the query is part of a larger query I had running from a website.

The weird thing is… the first of the two queries (=) returns data. The second returns ziltch. I would have thought the second would, if anything, return MORE data… since ‘like’ casts a wider net than ‘=’

Have I suffered a massive common sense failure?

edit: Don’t you just love it, as a programmer, when something small f*cks everything up.

2

Hmm… this depends entirely on what the subquery returns, and possibly what variant of SQL you’re using, since I’m not sure that they all have the same like and wildcard rules.

The ‘like’ query can have the possibility of returning more matches, yes, especially if the argument it is given has the proper wildcards in it. ‘%JOHN%’, for instance, will match ‘JOHNNY’ and ‘JOHN’, but as a literal it will not match anything that doesn’t have the percent signs in those specific spots.
‘BILL’ or anything else without wildcard syntax inside should return exactly the same results as = or like.

To find something that would match as a literal and not as a wildcard, you’d probably have to find some weird syntax like ‘[BF]G’, which will only like match on ‘BG’ or ‘FG’.

I do wonder if the database engine just can’t handle the subquery notation in a like clause at all, and that’s why you’re getting no matches.

3

like syntax requires your comparison value to have wildcards, typically a %
You don’t have a wildcard around your second query, so as there’s no wildcard, there’s no match.

4

Shouldn’t it be:

select * from table1 where upper(firstname) in (select upper(forename) from table2 where accountnumber = ‘xxxxxxx’ and pinnumber = ‘xxxx’)

5

SmackFu nailed it. Your subqueries can return multiple records, and depending on the flavor of SQL you’re using, the comparison is likely on the last record returned by the subquery. Due to caching, and again, depending on SQL engine, this could vary between the queries, and even the runs of the queries. At best, you might get the results you want, but SQL really wants a single value when doing the comparisons used in your examples.

6

If the subquery returns more than one row it should fail at run time. It shouldn’t even attempt to return more then one row for the exact reason given above, it won’t know which of the returned values to match.

The question is why = works and like doesn’t.
like requires wildcards if you want partial matches.

7

It appears you want all records in table1 where the firstname matches the record(s) from table2 that have a specific accountnumber and pinnumber. If so, I’d probably use one of these two methods, replacing the “=” with “LIKE” (and wildcards) as desired.



SELECT
	*
FROM
	table1 t1
	
	INNER JOIN table2 t2 ON
		Upper(t1.firstname) = Upper(t2.forename)
WHERE
	t2.accountnumber = 'xxxxxxx'
	AND t2.pinnumber = 'xxxx'
	
SELECT
	*
FROM
	table1 t1
	
	INNER JOIN	(
				SELECT
					forename
				FROM
					table2
				WHERE
					accountnumber = 'xxxxxxx'
					AND pinnumber = 'xxxx'
				) t2 ON
		Upper(t1.firstname) = Upper(t2.forename)

I’d also lose the Upper functions if your environment isn’t case sensitive (some are, some aren’t), as these likely make it non-sargable, depending on environment.