1. SQL Tutorial (7) EXISTS, ANY & ALL,

SQL EXISTS Operator

EXISTS operator

• used to test for the existence of any record in a subquery
• returns TRUE if the subquery returns one or more records

Syntax: returns TRUE/FALSE and then lists the values in the column that satisfies the condition in WHERE

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

Example

SELECT SupplierName
FROM Suppliers
WHERE EXISTS 
(SELECT ProductName FROM Products 
WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

---

SQL ANY AND ALL Operators

allow you to perform a comparison b/w a single column value & a range of other values

ANY Operator

• returns a boolean value as a result
• returns TRUE if ANY of the subquery values meet the condition

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
		(SELECT column_name
		FROM table_name
		WHERE condition);

Example: lists the ProductName if it finds ANY records in the OrderDetails table that has Quantity equal to 10

SELECT ProductName
FROM Products
WHERE ProductID = ANY
		(SELECT ProductID
		FROM OrderDetails
		WHERE Quantity = 10);

ALL Operator

• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• is used w/ SELECT, WHERE and HAVING statements
  ALL Syntax w/ SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

ALL Syntax with WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL 
		(SELECT column_name
		FROM table_name
		WHERE condition);

Example 1: lists all the product names

SELECT ALL ProductName
FROM Products
WHERE TRUE;

Example 2: lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10

SELECT ProductName
FROM Products
WHERE ProductID = ALL
		(SELECT ProductID
		FROM OrderDetails
		WHERE Quantity = 10);

---

This post is based on W3School's articles about SQL.