Looking through my tables, I don’t have any where a self-join makes any sense. I do have one table where it will work. In the Minimal Pairs game, each word is associated with another word. A pair might be “stop – top” or “rake – wake”. We have a picture for each word and by doing a self-join, we can see which pictures are duplicated.
SELECT *
FROM Pairs AS t1
INNER JOIN Pairs AS t2
ON t1.word1 = t2.word2
The result shows that two images, key and car, are used twice.
If you like WHERE syntax, this is it:
SELECT *
FROM Pairs AS t1, Pairs AS t2
WHERE t1.word1 = t2.word2