Selecting all columns (*)

Concepts covered: Query Basics

An asterisk in place of the column list means every column of the table, in the order the table defines them. SELECT * FROM products returns all 5 columns and all 5 rows, the entire table. It is the quickest way to see what a table you have never opened holds, before you know a single column name, and the result doubles as the column card from the first section with real values filled in.

SELECT
  *
FROM products

product_id comes first and in_stock last because that is how the table was designed, and the star takes that order as given. Set this result beside the 2-column query from the last section: the same 5 rows, with 3 more values on each. The star changed nothing about which rows come back; it widened every row to the table's full width. Flip the table card beside the query to its rows and the result and the card match cell for cell.

Star

Every column of products, in the table's order, with no column names typed.

SELECT
  *
FROM products

Named

The 2 columns a price list needs, in the order the list wants them.

SELECT
  product_name,
  price
FROM products

New table

The star on a table you have never opened shows its columns and sample values in 1 move.

SELECT
  *
FROM suppliers

The third tab is the star doing its real job. Nobody told you that suppliers has a lead_days column, and after 1 run you know it exists and what kind of values it holds: the days each supplier takes to deliver, 12 in the first row and 30 and 9 in the next 2. The next query against suppliers can name lead_days with confidence. Run the star once to learn a table's shape, then write a named list for the queries that do the work.

Getting to know a new table in 3 moves

• Run SELECT * on it once and read the column headers and a few values
• Decide which columns the question in front of you needs
• Rewrite the query with those columns named, and keep that version

Where the star costs you

The star is right for exploring and wrong for anything that will run again tomorrow. A table with 80 columns returns 80 values per row whether 2 or 80 are wanted. On a large table the database reads every one of those columns from storage, and the query gets slower and uses more memory as the table gets wider. When someone adds a column to the table next month, every star query starts returning it. A spreadsheet built for a fixed set of columns breaks, and so does a program that expects one. A query that names product_name and price also tells the next reader what the question was, while a star tells them nothing about why the query exists.

A report runs this query every morning and pastes the result into a spreadsheet laid out for 5 columns. Next week a weight_kg column is added to the products table. What does the report return after that?

SELECT
  *
FROM products

• 6 columns per row, so the spreadsheet's columns no longer line up with the result
• 5 columns per row, because the star was fixed to those 5 when the report was written
• An error, because the table no longer matches the query that reads it
• 5 columns per row, with weight_kg taking the place of in_stock at the end

Do

• Use * to look at a table for the first time
• Follow the star with a named list once you know which columns matter
• Name columns in any query that is saved or shared, and in every scheduled one

Avoid

• Ship * in a report or pipeline that runs on a schedule
• Assume the table's columns will stay the same forever
• Pull 80 columns to read 2

The star replaces the column list rather than 1 column on it. Writing SELECT * gives every column once; adding named columns beside it, as in SELECT *, price, returns price a second time and clutters the result. Keep the 2 styles apart: the star alone while exploring, a named list alone for the query you keep.

A wide star result is also hard to read where you are likely to read it: 80 columns wrap into unreadable lines in a terminal or on a phone, while 3 named columns fit on 1 line.

About this lesson

This section is part of the Query Structure: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.