{"id":33813,"date":"2024-01-09T11:54:37","date_gmt":"2024-01-09T01:54:37","guid":{"rendered":"https:\/\/database.guide\/?p=33813"},"modified":"2024-01-09T11:54:38","modified_gmt":"2024-01-09T01:54:38","slug":"sql-exists-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-exists-explained\/","title":{"rendered":"SQL EXISTS Explained"},"content":{"rendered":"\n

The SQL EXISTS<\/code> predicate is used to specify a test for a non-empty set. It returns TRUE<\/code> or FALSE<\/code>, depending on the outcome of the test. <\/p>\n\n\n\n

When we incorporate the EXISTS<\/code> predicate operator into our SQL queries, we specify a subquery to test for the existence of rows. If there are any rows, then the subquery is TRUE<\/code>. If there are no rows, then the subquery is FALSE<\/code>.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s a quick example:<\/p>\n\n\n\n

SELECT ArtistName \nFROM Artists ar\nWHERE EXISTS (\n    SELECT * FROM Albums al\n    WHERE al.ArtistId = ar.ArtistId\n    AND ReleaseDate < '2000-01-01'\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ArtistName  |\n+-------------+\n| Iron Maiden |\n| AC\/DC       |\n| Jim Reeves  |\n| Buddy Rich  |\n| Tom Jones   |\n+-------------+<\/pre>\n\n\n\n
Here, my query returns the names of all artists that released at least one album before the year 2000.<\/p>\n\n\n\n
The subquery returns all albums from each artist that have a ReleaseDate<\/code> prior to 2000-01-01<\/code>. The outer query uses the SQL EXISTS<\/code> operator to test for the existence of any rows that are returned by the subquery. Basically, we only get artists that have at least one row returned by the subquery. In this case, EXISTS<\/code> returns TRUE<\/code> and we get a row for that artist. <\/p>\n\n\n\n
The above query is the equivalent of the following:<\/p>\n\n\n\n
SELECT ArtistName \nFROM Artists ar\nWHERE ar.ArtistId IN (\n    SELECT ar.ArtistId FROM Albums al\n    WHERE al.ArtistId = ar.ArtistId\n    AND ReleaseDate < '2000-01-01'\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ArtistName  |\n+-------------+\n| Iron Maiden |\n| AC\/DC       |\n| Jim Reeves  |\n| Buddy Rich  |\n| Tom Jones   |\n+-------------+\n5 rows in set (0.00 sec)<\/pre>\n\n\n\n
The difference is that the second query uses the SQL IN<\/code> predicate<\/a> instead of EXISTS<\/code>. When we use IN<\/code>, we specify a column name that should be returned by the subquery. When we use EXISTS<\/code>, we don’t need to specify any such column.<\/p>\n\n\n\n
A NOT EXISTS<\/code> Example<\/h2>\n\n\n\n
We can use NOT EXISTS<\/code> in order to return the opposite of EXISTS<\/code>:<\/p>\n\n\n\n
SELECT ArtistName \nFROM Artists ar\nWHERE NOT EXISTS (\n    SELECT * FROM Albums al\n    WHERE al.ArtistId = ar.ArtistId\n    AND ReleaseDate < '2000-01-01'\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------+\n| ArtistName             |\n+------------------------+\n| Allan Holdsworth       |\n| Devin Townsend         |\n| Maroon 5               |\n| The Script             |\n| Lit                    |\n| Black Sabbath          |\n| Michael Learns to Rock |\n| Carabao                |\n| Karnivool              |\n| Birds of Tokyo         |\n| Bodyjar                |\n+------------------------+\n11 rows in set (0.00 sec)<\/pre>\n\n\n\n
Note that this is not the same as changing the less than sign<\/a> to a greater than sign<\/a> in our subquery. Here’s what happens when we do that:<\/p>\n\n\n\n
SELECT ArtistName \nFROM Artists ar\nWHERE EXISTS (\n    SELECT * FROM Albums al\n    WHERE al.ArtistId = ar.ArtistId\n    AND ReleaseDate > '2000-01-01'\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------+\n| ArtistName             |\n+------------------------+\n| Devin Townsend         |\n| The Script             |\n| Michael Learns to Rock |\n| Tom Jones              |\n| Allan Holdsworth       |\n+------------------------+\n5 rows in set (0.00 sec)<\/pre>\n\n\n\n
Only five rows are returned, compared to the 11 rows returned when using NOT EXISTS<\/code>. <\/p>\n\n\n\n
The logic between these two examples is different:<\/p>\n\n\n\n