Exists in sql - sql - sql tutorial - learn sql
- EXISTS is a Boolean operator used in a subquery to test whether the inner query returns any row.
- If it does, then the outer query proceeds.
- If not, the outer query will not execute, and the entire SQL statement returns nothing.
Syntax:
SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
(SELECT *
FROM "table_name2"
WHERE "condition");
- Note that instead of *, you can select one or more columns in the inner query. The effect will be identical.
Example
Table Product_Information
| Product_Name | Prices | Txn_Date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2017 |
| San Diego | 250 | Jan-07-2017 |
| Los Angeles | 300 | Jan-08-2017 |
| Boston | 700 | Jan-08-2017 |
Table Characteristics
| Region_Name | Store_Name |
|---|---|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
- The following SQL query,
SELECT SUM(Prices) FROM Product_Information WHERE EXISTS
(SELECT * FROM Characteristics
WHERE Region_Name = 'West');
produces the result below
SUM(Prices)
2750- At first, this may appear confusing, because the subquery includes the [region_name = 'West'] condition, yet the query summed up sales for stores in all regions.
- Upon closer inspection, we find that since the subquery returns more than zero row, the EXISTS condition is true, and the rows returned from the query "SELECT SUM(Sales) FROM Store_Information" become the final result.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExamples:
Examples:
A. Using NULL in a subquery to still return a result set
- The following example returns a result set with NULL specified in the subquery and still evaluates to TRUE by using EXISTS.
Uses AdventureWorks
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialB. Comparing queries by using EXISTS and IN
B. Comparing queries by using EXISTS and IN
- The following example compares two queries that are semantically equivalent. The first query uses EXISTS and the second query uses IN.
Uses AdventureWorks
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson');
GO
The following query uses IN. Uses AdventureWorks
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson');
GO
- Here is the result set for either query.
Output
| FirstName | LastName |
|---|---|
| Barry | Johnson |
| David | Johnson |
| Willis | Johnson |
- (3 row(s) affected)
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialC. Comparing queries by using EXISTS and = ANY
C. Comparing queries by using EXISTS and = ANY
- The following example shows two queries to find stores whose name is the same name as a vendor.
- The first query uses EXISTS and the second uses =”ANY”.
Uses AdventureWorks
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor AS v
WHERE s.Name = v.Name) ;
GO
The following query uses = ANY. Uses AdventureWorks
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor AS v ) ;
GO
D. Comparing queries by using EXISTS and IN
- The following example shows queries to find employees of departments that start with P.
Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.BusinessEntityID = edh.BusinessEntityID
AND d.Name LIKE 'P%');
GO
- The following query uses IN.
Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO