Photos for Mac.... How to search for specific photo in the specific album? I would like to see ONLY results from specific album.

See the article https://support.apple.com/en-in/guide/photos/pht8d0ad5198/mac

note : the photos in system photo library should be added to the created named albums in photo application side bar .

See the article https://support.apple.com/en-in/guide/photos/pht6d60a1f1/mac

Due to the fact that I have so many different albums and different criteria (it could be English baseball, Croatian swimming, etc...), I can not create thousands of smart albums. Or, I did not understand correct your suggestion?

You create only one smart album - call it "Album Search", and modify it, whenever you want to search an album. That is reasonably quick.

It is a pity, that the search term you are looking for, is a part of the title and not a keyword. If your search terms were keyword, you could use the "Showing only" filter. The filter allows you to filter the album you are currently viewing and restrict it to the photos with certain keywords. You can filter your albums for any keyword that you add to the Quick Group. See: How to use the Filters in Photos 4.0 - Apple Community

File -> New Smart Album

album -> is -> The one you want

+

Then set the second line for whichever search term you need.

Your search will then be limited to the named album

For instance:

In the Photos app, if I do the same, I would get EVERYTHING which has “French Football” in the title. Sold, For Sale, Badges, postcards, pennants, etc...

Not using a Smart album as described above. The search is limited to the Album in the first line.

Just try creating a smart album with the following criteria:

Then change the Album to BADGES SOLD and see what you get. It's really very effective and simple.