Example
Consider a database with the following two tables.
Employees table:
| Id | FName | LName | DeptId |
|---|---|---|---|
| 1 | James | Smith | 3 |
| 2 | John | Johnson | 4 |
Departments table:
| Id | Name |
|---|---|
| 1 | Sales |
| 2 | Marketing |
| 3 | Finance |
| 4 | IT |
Simple select statement
* is the wildcard character used to select all available columns in a table.
When used as a substitute for explicit column names, it returns all columns in all tables that a query is selecting FROM. This effect applies to all tables the query accesses through its JOIN clauses.
Consider the following query:
SELECT * FROM Employees
It will return all fields of all rows of the Employees table:
| Id | FName | LName | DeptId |
|---|---|---|---|
| 1 | James | Smith | 3 |
| 2 | John | Johnson | 4 |
Dot notation
To select all values from a specific table, the wildcard character can be applied to the table with dot notation.
Consider the following query:
SELECT
Employees.*,
Departments.Name
FROM
Employees
JOIN
Departments
ON Departments.Id = Employees.DeptId
This will return a data set with all fields on the Employee table, followed by just the Name field in the Departments table:
| Id | FName | LName | DeptId | Name |
|---|---|---|---|---|
| 1 | James | Smith | 3 | Finance |
| 2 | John | Johnson | 4 | IT |
Warnings Against Use
It is generally advised that using * is avoided in production code where possible, as it can cause a number of potential problems including:
- Excess IO, network load, memory use, and so on, due to the database engine reading data that is not needed and transmitting it to the front-end code. This is particularly a concern where there might be large fields such as those used to store long notes or attached files.
- Further excess IO load if the database needs to spool internal results to disk as part of the processing for a query more complex than
SELECT <columns> FROM <table>. - Extra processing (and/or even more IO) if some of the unneeded columns are:
- computed columns in databases that support them
- in the case of selecting from a view, columns from a table/view that the query optimiser could otherwise optimise out
- The potential for unexpected errors if columns are added to tables and views later that results ambiguous column names. For example
SELECT * FROM orders JOIN people ON people.id = orders.personid ORDER BY displayname- if a column column calleddisplaynameis added to the orders table to allow users to give their orders meaningful names for future reference then the column name will appear twice in the output so theORDER BYclause will be ambiguous which may cause errors ("ambiguous column name" in recent MS SQL Server versions), and if not in this example your application code might start displaying the order name where the person name is intended because the new column is the first of that name returned, and so on.
When Can You Use *, Bearing The Above Warning In Mind?
While best avoided in production code, using * is fine as a shorthand when performing manual queries against the database for investigation or prototype work.
Sometimes design decisions in your application make it unavoidable (in such circumstances, prefer tablealias.* over just * where possible).
When using EXISTS, such as SELECT A.col1, A.Col2 FROM A WHERE EXISTS (SELECT * FROM B where A.ID = B.A_ID), we are not returning any data from B. Thus a join is unnecessary, and the engine knows no values from B are to be returned, thus no performance hit for using *. Similarly COUNT(*) is fine as it also doesn't actually return any of the columns, so only needs to read and process those that are used for filtering purposes.
