Exists in sql - sql - sql tutorial - learn sql




 exists in 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 tutorial

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 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  
  • 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 tutorial

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  


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