Comment by jiaaro

Exists is a useful tool that you should certainly know how to use. Whether or not it's faster than distinct depends on the rest of the query. I've optimized queries where distinct is faster than exists. It's been some time, but I think it boils down to the relative sizes of the tables and how many of the exists queries actually find something (and how often they find more than one something).

Also, some databases (like clickhouse) allow for `any` joins which avoid producing duplicate rows. For example:

    select author.*
    from author
    inner any join book on (
        book.author_id = author.id 
        and book.title like 'Book%'
    )