Simple SELECT Query
SELECT * FROM a table returns every column and every row, the quickest way to see what a table holds.
Novice · T-SQL · SQL · Updated
After this lesson you can
- Return every column and row of a table with SELECT *
Key takeaways
SELECT * FROM Productsreturns every column and every row of the table:*means all columns.SELECT *is the quickest way to explore a table: which columns it has, and what its data looks like.- In application code, list the columns you need instead of using
*.
The simplest query returns everything in a table: every column, every row. Open a New Query window in SSMS, choose Northwind in the database drop-down, and run:
SELECT *
FROM Products;
The * means all columns. FROM Products says which table. The result is all 77 products, with every column the table has:
| ProductId | ProductName | SupplierId | CategoryId | QuantityPerUnit | UnitPrice | UnitsInStock | … |
|---|---|---|---|---|---|---|---|
| 1 | Chai | 1 | 1 | 10 boxes x 20 bags | 18.00 | 39 | … |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19.00 | 17 | … |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10.00 | 13 | … |
In the bottom-right corner of the results, SSMS shows how many rows came back: 77 rows.
When to use SELECT *
SELECT * is the quickest way to see what's in a table while you're exploring: which columns it has, and what the data looks like. Try it on a few more:
SELECT * FROM Customers;
SELECT * FROM Employees;
SELECT * FROM Categories;
Run the three together and SSMS shows three result grids, one under another.
In application code, though, list the columns you need instead of using *. The next lesson explains why.
Do it: see everything in the Categories table
The designer of a new product catalog is planning its sections, one per category. Before planning, she wants to see everything the Categories table holds: every column and every row.
The box has two tables, Products and Categories (the Tables tab shows the SQL that makes them). Categories has these rows:
| CategoryId | CategoryName | Description |
|---|---|---|
| 1 | Beverages | Soft drinks, coffees, teas, beers, and ales |
| 2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings |
| 3 | Confections | Desserts, candies, and sweet breads |
| 4 | Dairy Products | Cheeses |
| 5 | Grains/Cereals | Breads, crackers, pasta, and cereal |
Your query should give exactly that: the three columns CategoryId, CategoryName and Description, in that order, and all 5 rows, in any order.
Your job: write one query that returns every column and every row of Categories.
- Don't type the column names: use the symbol that means all columns.
- Read from
Categories, notProducts.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
1 question to try on the lesson page.
So what
SELECT * is your flashlight: point it at any table to see what's inside before you write the query you really need.