SQL ALL Operator

ALL Operator

ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows. For example, ALL means greater than every value, means greater than the maximum value. Suppose ALL (1, 2, 3) means greater than 3.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

Parameters:

Pictorical Presentation: SQL ALL Operator

DBMS Support: ALL Operator

Example: SQL ALL operator

To get 'des_date','des_amount' and 'ord_amount' columns from the 'despatch' table with following conditions -

1. 'des_amount' of 'despatch' table is more than 'ord_amount' from 'orders' table which satisfies the condition bellow:

  a) 'ord_amount' must be equal to 2000,

the following SQL statement can be used:

SELECT des_date,des_amount,ord_amount
FROM despatch
WHERE des_amount>ALL(
SELECT ord_amount FROM orders
WHERE ord_amount=2000);

Sample table: orders

Sample table: despatch

Explanation:

The inner query returns 'ord_amount' from 'orders' table for 'ord_amount' is equal to 2000
The outer query select 'des_date', 'des_amount' and 'ord_amount' form 'despatch' table whose 'des_amount' is greater than all the 'ord_amount' from 'orders' table whose 'ord_amount' is equal to 2000.

Output:

DES_DATE  DES_AMOUNT ORD_AMOUNT
--------- ---------- ----------
12-JAN-08       3800       4000
19-OCT-08       4000       4000
24-JUL-08       4500       3500

See our Model Database

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: ANY
Next: SOME