What select_expr clause in SELECT statements is good for?

Question

Possible Duplicate:
What is the reason not to use select *?

Could you explain what select_expr clause in SELECT statements is good for? I've been always using * for select_expr like this

SELECT * FROM table_name WHERE ...

and it was enough.

Answer 1

There are several reasons:

• You can add calculated columns: select a, b, a+b FROM table
• You can restrict the transferred data volume.
• You can give individual columns a new name.
• You can program more safely and performant.

Add to the first and second point: Especially usually require column restriction.

Add to the fourth point: When doing adhoc queries I'm almost always using *. But the programs should always enumerate the columns. This prevents surprising column reordering and also makes it clear what data is actually used.

Answer 2

An example over and above the others already suggested:

• You need to use aggregate functions:

  SELECT ID, COUNT(*) AS Num_Total
    FROM OrderItem
   GROUP BY OrderID;
  

• You need to get information from two parts of a UNION query. I used this in an answer to another question recently:

  SELECT v1.ID AS OrderID_1, v1.NA AS NS, v2.ID AS OrderID_2, v2.NB AS NL, v3.NC AS NC
    FROM (SELECT ID, COUNT(*) AS NA
            FROM OrderItem
           WHERE OrderID = <specified order ID>
           GROUP BY OrderID
         ) AS v1
    JOIN (SELECT ID, COUNT(*) AS NB
            FROM OrderItem
           WHERE OrderID != <specified order ID>
           GROUP BY OrderID
         ) AS v2
      ON v1.NA <= v2.NB
    JOIN (SELECT I1.ID, COUNT(*) AS NC
            FROM OrderItem AS I1
            JOIN OrderItem AS I2 ON I2.ItemID = I1.ItemID AND I2.OrderID = <specified order ID>
           WHERE I1.OrderID != <specified order ID>
           GROUP BY I1.ID
         ) AS v3
      ON v3.ID = v2.ID
  UNION
  SELECT v2.ID AS OrderID_1, v2.NB AS NS, v1.ID AS OrderID_2, v2.NA AS NL, v3.NC AS NC
    FROM (SELECT ID, COUNT(*) AS NA
            FROM OrderItem
           WHERE OrderID = <specified order ID>
           GROUP BY OrderID
         ) AS v1
    JOIN (SELECT ID, COUNT(*) AS NB
            FROM OrderItem
           WHERE OrderID != <specified order ID>
           GROUP BY OrderID
         ) AS v2
      ON v1.NA > v2.NB
    JOIN (SELECT I1.ID, COUNT(*) AS NC
            FROM OrderItem AS I1
            JOIN OrderItem AS I2 ON I2.ItemID = I1.ItemID AND I2.OrderID = <specified order ID>
           WHERE I1.OrderID != <specified order ID>
           GROUP BY I1.ID
         ) AS v3
      ON v3.ID = v1.ID
  

At no point in that query was the Quantity column from the OrderItem table of any relevance to the computation I was doing. There's no way to write that query without using the 'select_expr' rather than 'SELECT *'. You might note that the initial 'aggregate function' suggestion is one of the sub-queries in this larger query (and it appears twice — you trust that the optimizer will optimize accurately).

Answer 3

Imagine your table has a million columns, you were only interested in one, and you were speaking to your database over a network via a 30 baud connection.