There are three ways we can select all columns data with a select query. This Oracle tutorial will show you different ways with the help of syntax and examples.
This page contains (Skip to topic)..
SELECT All Columns Using * (star)
We can select all the columns using *. So an example for the same is as follows.
SELECT * FROM employees;
You can refer to the syntax and detail explanation by having a look at SQL Select Query syntax with example page.
SELECT All Columns Using table_name.*
Whenever we select a column or all columns, Oracle implicitly accesses them with the “xyz.*” format. This basically tells Oracle that, get me all columns of xyz table.
The syntax for this would be,
SELECT table_name.* fROM table_name;
Now, let us select all columns from the employee table in this way.
SELECT employees.* FROM employees;
SELECT All Columns Using table_aliase.*
The table name may be lengthy sometimes. It will look complicated if we write the same before columns. So to avoid that, we can give a temporary name to the table.
Its syntax is,
SELECT table_aliase.* FROM table_name table_aliase;
With the knowledge from the above explanation, let us try to select all columns from employees table.
SELECT emp.* FROM employees emp;
SELECT All Columns By Mentioning All Columns
So you might think can we select all the columns of a table without using a star? The answer is yes. We can do that by mentioning the column names separated by the comma.
The syntax for this is,
SELECT column1, column2, column3, ....., columnN FROM table_name;
Now applying the above syntax in the employees’ table to retrieve all the data would look something like this.
SELECT employee_id , first_name , last_name , email , phone_number , hire_date , job_id , salary , commission_pct , manager_id , department_id FROM employees;
The result will look as shown below.
Now that we understood how to retrieve all columns data with a select query, let us see how we can retrieve specific column or columns data with a select query in the next post.