CODEWITHSUNDEEP
sqldatabasems-access
Tigris
Tigris

Reputation: 129

SQL: Not Like produces different results than what would be Like's opposite

So, I'm practicing for an exam (high school level), and although we have never been thought SQL it is necessarry know a little when handling MS Access.

The task is to select the IDs of areas which names does not correspond with the town's they belong to.

In the solution was the following example:

SELECT name 
FROM area 
WHERE id not in (SELECT areaid 
           FROM area, town, conn 
           WHERE town.id = conn.townid 
           AND area.id = conn.areaid AND 
               area.name like "*"+town.name+"*");

It would be the same with INNER JOINS, just stating that, because Access makes the connection between tables that way.

It works perfectly (well, it was in the solution), but what I don't get is that why do we need the "not in" part and why can't we use just "not like" instead of "like" and make the query in one step.

I rewrote it that way (without the "not in" part) and it gave a totally different result. If I changed "like" with "not like" it wasn't the opposite of that, but just a bunch of mixed data. Why? How does that work? Please, could someone explain?

Edit (after best answer): It was more like a theoretical question on how SQL queries work, and does not needed a concrete solution, but an explanation of the process. (Because of this I feel like the sql tag however belongs here)

Upvotes: 1

Views: 98

Answers (3)

BICube
BICube

Reputation: 4681

One thing that would create a difference is to consider this example 

areaid areaname townname
 1        AA      AA
 1        AA      BB

So your first query would exclude both records from the outcome. Because the inner query would identify areaid =1 to be among those to be excluded. Therefore, both records will not show up in the output.

Using not like however would exclude the first record and return to you the second record. Because the first record satisfies the condition with not like but the second doesn't satisfy the condition.

In other words, the first query would exclude any area (and corresponding records) that have at least one townname that is like an areaname. The second approach, would exclude only incidences where areaname is like townname but doesn't necessarily exclude all records for that area.

Upvotes: 1

sarin
sarin

Reputation: 5307

It depends on what the relationship between area, town and conn are. If you have many towns in an area, you will see the area duplicated in your row set. Your original query simply asks "Show me the areas that are in the following list:". Your query in one-step asks a different question: "Show me the 'conns' in towns, in areas which have an area name not like the town name...

SELECT name 
FROM area, town, conn
WHERE area.id = conn.areaid 
AND town.id = conn.townid 
AND area.name NOT like "*"+town.name+"*");

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

The reason is because there can be more than one town in an area, right?

So if there is a town in an area that has a similar name, then that area will be found in the LIKE subquery.

If there is another town in the SAME AREA that does not have a similar name, then that area will ALSO be found in the NOT LIKE subquery.

So the same area can be returned whether you use LIKE or NOT LIKE, because of the one-to-many relationship to towns.

Make sense?

Upvotes: 1

Related Questions