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.
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 tutorial
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
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
This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf
Related Searches to Exists in sql
not exist in sqlsql exists vs insql exists oracleif exists sqldifference between in and exists in sqlsql exists vs joinif exists sql server examplenot exists sql server exampleif exists mysqlselect if exists mysqlmysql existsmysql not existsmysql exists queryexists mysqlnot exists mysqlsql select existssql if exists selectsql select not existsselect exists sqlselect if exists sqlsql select 1 if existsselect existssql insert if not existssql insert where not existsms sql insert if not existssql update insert if not existsif not exists insert sql serversql query existssql exists querysql query not existssql not exists queryexists sql querynot exist sql querysql query to check if data exists in tableif exists sql querysql query if not existsexists sql oracleoracle sql if existsnot exists sql oracleoracle existsoracle not existsoracle sql not exists exampleoracle sql not existsexists oraclenot exists oracle sqlnot exists oracleif exists sql oracleoracle if existsmssql existsmssql if existsmssql not existsexists mssqlnot exists mssqlsql exists subquerysql subquery existst sql exists subquerysql server exists subquerysql server create table if not existscreate if not exists sqlsql server if not exists create tablesql create if not existsql case exists
