select a.artno, a.name
from Art a
inner join store s on a.Artno <> s.Artno`
Running this query took me more than 1 min producing more 899K rows while was supposed to bring out 7.9K results.
select artno
from art
except
(select artno from store)
This line of code provides me with 7.9K rows which is true for me.
The first codes seems to be working code but takes a hack of a time and produces a large result set. Wondering why?
It's generally NOT a good idea to use a <> operator with an INNER JOIN unless you really know that you want a lot of records. In other words, the JOIN is a great tool for inclusion, not exclusion.
When you do an INNER JOIN using a <> operator (especially on the keys), the query brings back every combination of art and store records except where the Artno keys match.
So, if your have 4 art records and 5 store records, only one of which had a matching ArtNo value, you would end up with 4 x 5 - 1 = 19 records.
The second query simply displays all art records that aren't in any store.
Well the two queries are different. The first query is joining the results from both tables, and while the condition may be the same, based on your results it's a one-to-many relationship between the two tables.
In contrast the second query is not joining the two results but rather selecting from the ART table and excluding the art numbers you supplied from another table.
Finally, the reason the second query is taking a lot longer without knowing a lot about your database is a guess but I'm going to give it a shot.
The first bottleneck is that it's joining two tables that are clearly not one-to-one, but the second bottleneck is probably indexing OR the size of the left hand table. Keep in mind that in a JOIN like the one you have the left hand table is scanned and ideally the right hand is a seek.
Does that make sense?
