SQL MIN

What is SQL MIN()?

The MIN() function in SQL is an aggregate function that returns the smallest (minimum) value from a specified column. It is commonly used to find the lowest value in a dataset, such as the smallest salary, earliest date, or minimum price.

Syntax

SELECT MIN(column_name)
FROM table_name
[WHERE condition];

• column_name: The column for which you want to find the minimum value.
• WHERE condition: Optional filter to specify rows to include in the calculation.

Key Features of SQL MIN()

1. Works with Numeric, Date, and Text Data: MIN() can operate on numeric, date, and text columns.
2. Excludes NULL Values: Automatically ignores NULL values in the column.
3. Works with GROUP BY: Finds the minimum value for grouped rows.

Examples of SQL MIN()

1. Basic Usage

Find the lowest salary among employees.

SELECT MIN(salary) AS lowest_salary
FROM employees;

2. Using MIN() with a WHERE Clause

Find the earliest order date for a specific customer.

SELECT MIN(order_date) AS earliest_order
FROM orders
WHERE customer_id = 101;

3. Using MIN() with GROUP BY

Find the lowest salary in each department.

SELECT department, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;

4. Using MIN() with a HAVING Clause

Find departments where the lowest salary is below $30,000.

SELECT department, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department
HAVING MIN(salary) < 30000;

5. Using MIN() with Subqueries

Find employees earning the lowest salary in the company.

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

6. Using MIN() with Text Data

Find the alphabetical first name among employees.

SELECT MIN(name) AS first_name
FROM employees;

Common Use Cases for SQL MIN()

1. Finding Earliest or Latest Dates: Retrieve the earliest order, start, or hire date.

   SELECT MIN(order_date) AS earliest_order
   FROM orders;
   

2. Finding Minimum Numeric Values: Identify the lowest salary, price, or stock quantity.

   SELECT MIN(price) AS lowest_price
   FROM products;
   

3. Sorting and Ranking: Use with subqueries or analytical functions for ranking.

Handling NULL Values

The MIN() function ignores NULL values by default. However, if you need to handle NULL values explicitly, use the COALESCE() function or check with IS NULL.

Example

Find the minimum salary, replacing NULL values with 0.

SELECT MIN(COALESCE(salary, 0)) AS lowest_salary
FROM employees;

Using MIN() with Joins

Combine MIN() with joins to find the minimum value in related tables.

Example

Find the earliest order date for each customer.

SELECT customers.name, MIN(orders.order_date) AS earliest_order
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

Performance Tips

1. Index Columns: Use indexes on columns involved in MIN() to speed up query execution.
2. Filter Early: Use the WHERE clause to reduce the dataset size before applying MIN().
3. Optimize Grouping: Be mindful of grouping large datasets as it may impact performance.

Limitations of SQL MIN()

1. Excludes NULL Values: MIN() ignores NULL values by default, which may require additional handling in certain scenarios.
2. No Direct Access to Multiple Rows: To retrieve all rows tied to the minimum value, additional queries like subqueries or JOIN are needed.

Conclusion

The SQL MIN() function is a powerful tool for identifying the smallest value in a dataset. Whether finding the lowest price, earliest date, or alphabetically first value, it simplifies data analysis and reporting.