SQL ALL Operator

SQL ALL Operator

The SQL ALL operator is used to compare a value to all values in another result set. This command is essential for performing conditional checks across a complete set of values, enhancing query precision and flexibility.

Syntax

-- Using ALL with a comparison operator
SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ALL (SELECT column_name FROM table_name WHERE condition);

-- Example with ALL and a subquery
SELECT product_id
FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');
  • SELECT column_name(s): Specifies the columns to retrieve.
  • FROM table_name: Specifies the table to query data from.
  • WHERE column_name comparison_operator ALL: Applies the comparison operator to compare the column value to all values in the result set returned by the subquery.
  • SELECT column_name FROM table_name WHERE condition: A subquery that returns a result set for comparison.
  • comparison_operator: Specifies the operator to use for comparison (e.g., >, =, <).

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the ALL operator to perform a query.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named products within the previously created database.

USE example_db;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

Here, we define the products table with columns for product_id, product_name, category, and price. The product_id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (product_name, category, price) VALUES ('Laptop', 'Electronics', 999.99);
INSERT INTO products (product_name, category, price) VALUES ('Smartphone', 'Electronics', 499.99);
INSERT INTO products (product_name, category, price) VALUES ('Tablet', 'Electronics', 299.99);
INSERT INTO products (product_name, category, price) VALUES ('Desk Chair', 'Furniture', 199.99);
INSERT INTO products (product_name, category, price) VALUES ('Bookshelf', 'Furniture', 89.99);
INSERT INTO products (product_name, category, price) VALUES ('Coffee Table', 'Furniture', 149.99);

Here, we insert six rows of data into the products table.

Step 4: Using the ALL Operator

This step involves using the ALL operator to find products that have a price greater than all products in the 'Electronics' category.

SELECT product_id, product_name
FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');

This command retrieves the product_id and product_name of products whose price is greater than all the prices of products in the 'Electronics' category.

In this example, the query will return the product_id and product_name of the 'Laptop' product, as its price is higher than any price in the 'Electronics' category.