{"id":11739,"date":"2020-11-30T12:48:51","date_gmt":"2020-11-30T02:48:51","guid":{"rendered":"https:\/\/database.guide\/?p=11739"},"modified":"2024-09-17T07:00:40","modified_gmt":"2024-09-16T21:00:40","slug":"sql-exists-operator-for-beginners","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-exists-operator-for-beginners\/","title":{"rendered":"SQL EXISTS Operator for Beginners"},"content":{"rendered":"\n

In SQL<\/a>, the EXISTS<\/code> operator specifies a subquery to test for the existence of rows. It returns TRUE<\/code> when the subquery returns one or more rows.<\/p>\n\n\n\n

A subquery is a query that is nested inside another query (or even another subquery)<\/p>\n\n\n\n

This article contains some basic examples of the EXISTS<\/code> operator.<\/p>\n\n\n\n\n\n\n\n

Source Tables<\/h2>\n\n\n\n

The following tables are used for the examples on this page.<\/p>\n\n\n\n

SELECT * FROM PetTypes;\nSELECT * FROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+-----------+\n| PetTypeId   | PetType   |\n|-------------+-----------|\n| 1           | Bird      |\n| 2           | Cat       |\n| 3           | Dog       |\n| 4           | Rabbit    |\n+-------------+-----------+\n(4 rows affected)\n+---------+-------------+-----------+-----------+------------+\n| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |\n|---------+-------------+-----------+-----------+------------|\n| 1       | 2           | 3         | Fluffy    | 2020-11-20 |\n| 2       | 3           | 3         | Fetch     | 2019-08-16 |\n| 3       | 2           | 2         | Scratch   | 2018-10-01 |\n| 4       | 3           | 3         | Wag       | 2020-03-15 |\n| 5       | 1           | 1         | Tweet     | 2020-11-28 |\n| 6       | 3           | 4         | Fluffy    | 2020-09-17 |\n| 7       | 3           | 2         | Bark      | NULL       |\n| 8       | 2           | 4         | Meow      | NULL       |\n+---------+-------------+-----------+-----------+------------+\n(8 rows affected)<\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate the EXISTS<\/code> operator.<\/p>\n\n\n\n
SELECT \n    pt.PetTypeId,\n    pt.PetType\nFROM PetTypes pt\nWHERE EXISTS ( \n    SELECT p.PetTypeId \n    FROM Pets p \n    WHERE p.PetTypeId = pt.PetTypeId \n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+-----------+\n| PetTypeId   | PetType   |\n|-------------+-----------|\n| 1           | Bird      |\n| 2           | Cat       |\n| 3           | Dog       |\n+-------------+-----------+<\/pre>\n\n\n\n
This example shows us how many pet types are in our pet hotel. There are actually four pet types, but only three of those match with an actual pet in the Pets<\/code> table.<\/p>\n\n\n\n
This provides the same result that we would have gotten with the following query that uses the IN<\/code> operator<\/a>.<\/p>\n\n\n\n
SELECT \n    PetTypeId,\n    PetType\nFROM PetTypes\nWHERE PetTypeId IN ( SELECT PetTypeId FROM Pets );<\/code><\/pre>\n\n\n\n
Using NOT EXISTS<\/code><\/h2>\n\n\n\n
We could add the NOT<\/code> operator to negate the results and see how many pet types are not<\/em> in our pet hotel.<\/p>\n\n\n\n
SELECT \n    pt.PetTypeId,\n    pt.PetType\nFROM PetTypes pt\nWHERE NOT EXISTS ( \n    SELECT p.PetTypeId \n    FROM Pets p \n    WHERE p.PetTypeId = pt.PetTypeId \n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+-----------+\n| PetTypeId   | PetType   |\n|-------------+-----------|\n| 4           | Rabbit    |\n+-------------+-----------+<\/pre>\n\n\n\n
In this case, our database contains a pet type of Rabbit<\/code>, but we don’t currently have any rabbits as pets.<\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL, the EXISTS operator specifies a subquery to test for the existence of rows. It returns TRUE when the subquery returns one or more rows. A subquery is a query that is nested inside another query (or even another subquery) This article contains some basic examples of the EXISTS operator.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[15,85,48,173,20],"class_list":["post-11739","post","type-post","status-publish","format-standard","hentry","category-sql","tag-create-query","tag-operators","tag-sql","tag-subqueries","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11739","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=11739"}],"version-history":[{"count":1,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11739\/revisions"}],"predecessor-version":[{"id":11743,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11739\/revisions\/11743"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=11739"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=11739"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=11739"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}