{"id":401,"date":"2018-05-05T19:33:33","date_gmt":"2018-05-05T12:33:33","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=401"},"modified":"2024-02-25T22:15:37","modified_gmt":"2024-02-25T15:15:37","slug":"sql-server-exists","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-exists\/","title":{"rendered":"SQL Server EXISTS"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server EXISTS<\/code> operator in the condition to test for the existence of rows in a subquery.<\/p>\n\n\n\n

SQL Server EXISTS operator overview #<\/a><\/h2>\n\n\n\n

The EXISTS<\/code> operator is a logical operator that allows you to check whether a subquery<\/a> returns any row. The EXISTS<\/code> operator returns TRUE<\/code> if the subquery<\/a> returns one or more rows.<\/p>\n\n\n\n

The following shows the syntax of the SQL Server EXISTS<\/code> operator:<\/p>\n\n\n

EXISTS ( subquery)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the subquery is a SELECT<\/code> statement only. As soon as the subquery returns rows, the EXISTS<\/code> operator returns TRUE<\/code> and stop processing immediately.<\/p>\n\n\n\n
Note that even though the subquery returns a NULL<\/code> value, the EXISTS<\/code> operator is still evaluated to TRUE<\/code>.<\/p>\n\n\n\n
SQL Server EXISTS operator examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples to understand how EXISTS<\/code> operator works.<\/p>\n\n\n\n
A) Using EXISTS with a subquery returns NULL example #<\/a><\/h3>\n\n\n\n
See the following customers<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example returns all rows from the  customers<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nWHERE<\/span>\n    EXISTS<\/span> (SELECT<\/span> NULL<\/span>)\nORDER<\/span> BY<\/span>\n    first_name,\n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the subquery returned a result set that contains NULL<\/code> which causes the EXISTS<\/code> operator to evaluate to TRUE<\/code>. Therefore, the whole query returns all rows from the customers<\/code> table.<\/p>\n\n\n\n
B) Using EXISTS with a correlated subquery example #<\/a><\/h3>\n\n\n\n
Consider the following customers<\/code> and orders<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example finds all customers who have placed more than two orders:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers c\nWHERE<\/span>\n    EXISTS<\/span> (\n        SELECT<\/span>\n            COUNT<\/span> (*)\n        FROM<\/span>\n            sales.orders o\n        WHERE<\/span>\n            customer_id = c.customer_id\n        GROUP<\/span> BY<\/span>\n            customer_id\n        HAVING<\/span>\n            COUNT<\/span> (*) > 2<\/span>\n    )\nORDER<\/span> BY<\/span>\n    first_name,\n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we had a correlated subquery<\/a> that returns customers who place more than two orders.<\/p>\n\n\n\n
If the number of orders placed by the customer is less than or equal to two, the subquery returns an empty result set that causes the EXISTS<\/code> operator to evaluate to FALSE<\/code>.<\/p>\n\n\n\n
Based on the result of the EXISTS<\/code> operator, the customer will be included in the result set.<\/p>\n\n\n\n
C) EXISTS vs. IN example #<\/a><\/h3>\n\n\n\n
The following statement uses the IN<\/a><\/code> operator to find the orders of the customers from San Jose:<\/p>\n\n\n
SELECT<\/span>\n    *\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (\n        SELECT<\/span>\n            customer_id\n        FROM<\/span>\n            sales.customers\n        WHERE<\/span>\n            city = 'San Jose'<\/span>\n    )\nORDER<\/span> BY<\/span>\n    customer_id,\n    order_date;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the EXISTS<\/code> operator that returns the same result:<\/p>\n\n\n
SELECT<\/span>\n    *\nFROM<\/span>\n    sales.orders o\nWHERE<\/span>\n    EXISTS<\/span> (\n        SELECT<\/span>\n            customer_id\n        FROM<\/span>\n            sales.customers c\n        WHERE<\/span>\n            o.customer_id = c.customer_id\n        AND<\/span> city = 'San Jose'<\/span>\n    )\nORDER<\/span> BY<\/span>\n    o.customer_id,\n    order_date;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
EXISTS vs. JOIN #<\/a><\/h2>\n\n\n\n
The EXISTS<\/code> operator returns TRUE<\/code> or FALSE<\/code> while the JOIN<\/a><\/code> clause returns rows from another table.<\/p>\n\n\n\n
You use the EXISTS<\/code> operator to test if a subquery returns any row and short circuits as soon as it does. On the other hand, you use JOIN<\/code> to extend the result set by combining it with the columns from related tables.<\/p>\n\n\n\n
In practice, you use the EXISTS<\/code> when you need to check the existence of rows from related tables without returning data from them.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server EXISTS<\/code> operator to test if a subquery returns rows.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t