=================================
Rule: Avoid Using `select *`
=================================

**Rule Code:** ``AM04``

**Name:** ``column_count``

Overview
--------

Using `select *` in SQL queries is considered an anti-pattern because it retrieves all columns from a table, which can lead to inefficient queries, ambiguity, and maintenance challenges. It is always better to explicitly define the columns you need, rather than selecting all columns by default.

Explanation
-----------

Anti-pattern: Using `select *`
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When you use `select *`, it fetches every column from the table, regardless of whether you actually need all of them. This can cause several issues:

- **Performance:** Retrieving unnecessary columns increases the amount of data transferred and processed, especially in large tables.
- **Ambiguity:** If a table structure changes (e.g., columns are added or removed), `select *` can lead to unexpected results.
- **Maintainability:** Queries using `select *` are harder to read and understand because it’s not clear which columns are actually being used in the result.

**Example of Using `select *` (Anti-pattern):**

.. code-block:: sql

    select *
      from foo;

In this example, all columns from the `foo` table are retrieved, even if only a few are actually needed for the query. This makes the query inefficient and harder to maintain.

Best Practice: Specify Columns Explicitly
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Instead of using `select *`, it is better to explicitly specify the columns that are needed. This improves both performance and maintainability by ensuring that only the necessary data is retrieved.

**Refactored Example Without `select *` (Best Practice):**

.. code-block:: sql

    select foo.id,
           foo.name
      from foo;

In this refactored query, only the `id` and `name` columns are retrieved, reducing data overhead and making the query more readable and predictable.

Conclusion
----------

Using `select *` is an anti-pattern because it introduces performance inefficiencies, ambiguity, and maintenance challenges. It is best to explicitly define the columns you need in your queries, which leads to clearer, more efficient, and more maintainable SQL code.

Groups:
-------

- `all <../..>`_
- `ambiguous <../..#ambiguous-rules>`_