The character asterisk (*) is used often in SQL queries. You can say it’s very handy and special.
The asterisk (*) is used in conjunction with the SELECT keyword to retrieve data from all columns from a table.
To understand how useful it is, let’s assume that we have a database table users with column names userid, username, firstname, lastname, job_title.
If you want to display all users with every piece of information you have to write this query:
select userid, username, firstname, lastname, job_title
from users
This is relatively easy because we only have 6 columns but in real life, you might have hundred or a few hundred columns depending on your data. So, in that case, to get all those data from each and every column is a time consuming task. But the asterisk (*) makes it easy!
Retrieving All Rows and Columns from a Table
Here’s how you can get all columns and rows using asterisk (*) with SELECT.
select *
from users
You don’t need to worry about column names when you use asterisk to retrieve data.
Get all or specific columns?
But again, everything depends on the data you want to retrieve as well. If you don’t want to get all the columns and just want to get specific columns only then you are better off with writing names of those columns in an SQL query.
Another advantage of getting specific columns is that if you’re retrieving the data to pass it to another program, you’d want to make sure you’re passing the specific columns that were supposed to be passed. In that case if you use asterisk (*) and pass all the columns, you might get an error depending on the program structure.
Use it with WHERE
WHERE clause if used for retrieving a subset of rows from a table.
If you want to limit the number of rows you get in your results, you’d want to use WHERE or HAVING clause.
For e.g if you just want to get one row, you can use this query assuming your userid column has all unique values in it.
select *
from users
WHERE userid = 1
basics database select sql query WHERE
