SELECT * in SQL

SELECT * in SQL

Anh Nhat Tran's photo
Anh Nhat Tran
·

1 min read

Table of contents

What is SELECT * mean?

The asterisk character *, in the SELECT statement, is shorthand for all the columns in the table or the relation when you do the join that currently querying.

For example, if you have a table with 3 columns (id, email, password), SELECT * FROM users WHERE id = 1; will give you all fields in the row.

Screenshot from 2021-11-27 20-38-48.png

Why is SELECT * considered harmful?

  • Not all fields are indexed and the query uses the full table scan.
  • If for some reason, you ended up adding some columns, it will return more data than you need.
  • If you want to do some formatting first or have some fields that you do not want to display.
  • "SELECT * will produce more TCP segments". I read it from Hussein but haven't tested it yet.

Screenshot from 2021-11-27 21-29-11.png

When should SELECT * be used?

It's acceptable to use SELECT * when there's the explicit need for every column in the table(s) involved, as opposed to every column that existed when the query was written. The database will internally expand the * into the complete list of columns - there's no performance difference.

SQL