CODEWITHSUNDEEP
sqlpostgresql
Thedog
Thedog

Reputation: 13

What is the difference between using '*' and '.*' in the first line of a select statement?

I'm hoping if someone could explain the difference between the two following statements:

SELECT * FROM employee;

and

SELECT employee.* FROM employee;

If someone could provide their reasoning for choosing employee.* instead of *, or when it would be appropriate to choose one over the other, it would be greatly appreciated.

Kind regards,

Joe.

Upvotes: 1

Views: 140

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

SELECT * ...

selects all columns from the result set generated in the FROM clause.

SELECT employees.* ...

selects all columns in the result set generated in the FROM clause that originated from the employees table (note that employees may also be an alias rather than a table name).

When the FROM clause only contains a single table, those two describe the same set of columns.1

As to why someone has chosen to write it this way when only a single table is present, I couldn't say. If they were an absolutist who insisted on prefixing all columns in the select clause with their originating table, I'm surprised that they're comfortable using * in there at all rather than explicit column names.

1But note that it's the FROM clause (including any JOINs etc) and the WHERE, GROUP BY and HAVING clauses that affect which rows are displayed. The SELECT clause effectively chooses columns.

Upvotes: 4

Igor
Igor

Reputation: 62228

In your example there is no difference. The reason the syntax allows for this is to allow you to display all the columns from only one object in your FROM clause. It would make more sense with an example.

The following query will display all columns from both tables employee and manager

SELECT * FROM employee inner join manager on employee.managerid = manager.managerid

The following query is functionally equivalent to the one above it the difference being is that we explicitly specify the order of the tables in the displayed column set.

SELECT employee.*, manager.* FROM employee inner join manager on employee.managerid = manager.managerid

The following query will only display columns from table employee and not manager

SELECT employee.* FROM employee inner join manager on employee.managerid = manager.managerid

Upvotes: 2

Related Questions