Understanding the SQL EXISTS Clause

And when you should think about using it

Photo by cottonbro

I want to share something I learned using SQL that I haven’t used in my career until recently. I’ve been coding in SQL for 3.5 years now and I just barely started using the EXISTS clause. So don’t feel bad if you don’t already know it and if you already do — then great!

Quick Introduction to the EXISTS Clause

The EXISTS clause is part of a group of subquery expressions. For more information above available subquery expressions, you can go to the PostgreSQL documentation.

The EXISTS clause is used to compare two tables and check if your table has values that exist in the other table. There is also a NOT EXISTS clause, which checks for those items not in the other reference table.

When comparing one table for existence of a record in another table, this is called a ‘semi-join’. Whenever you use the WHERE clause with an IN keyword this is also referred to as a semi-join. Microsoft uses this definition to describe semi-joins, “Semijoins are U-SQL’s way filter a rowset based on the inclusion of its rows in another rowset”².

The syntax looks like this¹:

SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);