In MySQL, ASC and DESC are keywords used in the ORDER BY clause to sort query results in ascending or descending order, respectively. They determine the sequence in which the rows are returned based on the values in specified columns.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• column1, column2, ...: The columns by which to sort the results.
• ASC: Sorts the results in ascending order (default).
• DESC: Sorts the results in descending order.

Key Concepts

1. Ascending Order (ASC)

   The ASC keyword is used to sort the results in ascending order. This is the default sorting order if no keyword is specified. For numeric columns, it sorts from the smallest to the largest value. For text columns, it sorts alphabetically from A to Z. For date columns, it sorts from the earliest to the latest date.

   Example:

   SELECT name, salary
   FROM employees
   ORDER BY salary ASC;
   

   This query sorts the employees by their salary in ascending order, showing the lowest salaries first.

2. Descending Order (DESC)

   The DESC keyword is used to sort the results in descending order. For numeric columns, it sorts from the largest to the smallest value. For text columns, it sorts alphabetically from Z to A. For date columns, it sorts from the latest to the earliest date.

   Example:

   SELECT name, salary
   FROM employees
   ORDER BY salary DESC;
   

   This query sorts the employees by their salary in descending order, showing the highest salaries first.

3. Multiple Columns

   You can sort by multiple columns, specifying ASC or DESC for each column:

   SELECT name, department, salary
   FROM employees
   ORDER BY department ASC, salary DESC;
   

   This query first sorts the results by department in ascending order. Within each department, it then sorts the employees by salary in descending order.

4. Default Sorting

   If no sorting order is specified, MySQL defaults to ascending order (ASC):

   SELECT name, salary
   FROM employees
   ORDER BY salary;
   

   This query is equivalent to:

   SELECT name, salary
   FROM employees
   ORDER BY salary ASC;
   

5. Performance Considerations

   • Indexing: Index columns used in the ORDER BY clause to improve query performance, especially with large datasets.
   • Query Optimization: Ensure that sorting operations do not impact performance adversely by optimizing queries and indexes.

Examples

1. Single Column Sorting

   SELECT * FROM products
   ORDER BY price ASC;
   

   This query retrieves all products sorted by price in ascending order.

2. Single Column Descending

   SELECT * FROM products
   ORDER BY price DESC;
   

   This query retrieves all products sorted by price in descending order.

3. Multiple Columns Sorting

   SELECT * FROM orders
   ORDER BY order_date DESC, total_amount ASC;
   

   This query sorts orders by order_date in descending order and, within the same date, sorts by total_amount in ascending order.