AssRisk

The asterisk in SQL

SELECT * returns every column from the tables in the query. COUNT(*) counts rows, including rows full of NULLs, while COUNT(column) counts only the rows where that column isn't NULL. Between two numbers, * multiplies, as it does everywhere else.

SELECT * and table.*

SELECT * expands to every column, in the order the table defines them. In a join, table.* limits that to one table:

SELECT * FROM users;                     -- every column of users

SELECT u.*, o.total                      -- every users column, plus one from orders
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

COUNT(*) vs. COUNT(column)

The * in COUNT(*) doesn't mean "every column". It means "count the rows, and don't look at any values". That's why it's the only form that counts rows where everything is NULL:

CREATE TABLE signups (email TEXT, plan TEXT);
INSERT INTO signups VALUES
  ('[email protected]', 'pro'),
  (NULL, 'free'),
  ('[email protected]', NULL),
  (NULL, NULL);

SELECT COUNT(*), COUNT(email), COUNT(DISTINCT email) FROM signups;
-- 4 | 2 | 1
Expression Counts Result above
COUNT(*) Every row 4
COUNT(email) Rows where email isn't NULL 2
COUNT(DISTINCT email) Different non-NULL values of email 1
COUNT(1) Every row, same as COUNT(*) 4

COUNT(1) isn't faster than COUNT(*) in any major database. COUNT(*) is the SQL standard's own way to say "count rows", so it's the clearer choice.

When to avoid SELECT *

SELECT * is fine for exploring data by hand. In code that runs in production, list the columns instead:

  • Schema changes leak through. Add a column and every query returns it, including to code that serializes rows straight into an API response.
  • More data moves. Wide text and JSON columns travel over the network even when nobody reads them.
  • Indexes help less. If a query needs only indexed columns, the database can answer from the index alone. SELECT * forces it back to the table.
  • Joins get ambiguous. Two tables with an id column give you two ids, and some client libraries silently keep only one.
  • INSERT INTO archive SELECT * FROM orders depends on both tables having the same columns in the same order, forever.

The exception is EXISTS (SELECT * FROM ...). There the database only checks whether a row exists and never reads the columns, so * costs nothing.

Every column except one

Standard SQL has no way to say "all but these". Several engines added their own:

Database Syntax
BigQuery, Databricks SELECT * EXCEPT (password_hash) FROM users
DuckDB, Snowflake SELECT * EXCLUDE (password_hash) FROM users
PostgreSQL, MySQL, SQL Server, SQLite Not supported; list the columns

SQL's wildcard isn't *

In LIKE patterns, SQL uses % for "any characters" and _ for "one character". A * there is just an asterisk, so 'abc' LIKE 'a*' is false and 'a*' LIKE 'a*' is true. The exception is Microsoft Access, whose default query mode uses * and ? as LIKE wildcards, like Excel does.

The other two asterisks

price * quantity multiplies, and /* ... */ is a comment that can span several lines. Some tools treat /*+ ... */ as an optimizer hint, notably Oracle and MySQL, so a comment that starts with a plus sign isn't always ignored.