Check out some examples of best practices using JOIN in SQL. Are you doing this efficiently?
Specifically, the last version is still used in many SQL codebases, which have not yet migrated to the ANSI JOIN syntax (even if ANSI joins should be preferred for readability reasons).
Not only are these queries more correct, they are also much faster in most SQL databases for a simple reason. The database can stop searching for staff as soon as it has encountered at least one staff for which there is a matching customer. This is also nicely explained in Dan Martensen’s articleSQL Performance of JOIN and WHERE EXISTS. And, we’ve blogged about a related topic here: SQL Tip of the Day: Be Wary of SELECT COUNT(*).
Another exception is when you have a primary key/foreign key relationship that guarantees that an (INNER) JOIN produces no duplicate values, i.e. when you’re joining a one-to-one or many-to-one relationship, then JOIN is a correct solution, but it is usually equally fast, so semi join will still be more readable.
If you need to check whether you have any matches between a table A and a table B, but you only really care about the results from table A, do make sure you’re using a SEMI-JOIN (i.e. an EXISTS or IN predicate), not an(INNER) JOIN.