Is SELECT * clear enough to specify which query we are referring to?

I am new to mySQL.
I am following Mosh’s tutorial to familiarize myself to SQL.

Here’s my question for the following code.

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price*quantity > 30

When I looked up about SELECT *, it says: * means to return all all columns of the queried tables. Then I think SELECT * means that it grabs all tables from all schema.
My question is: Isn’t it a bit inefficient and confusing to return all column provided my understanding is right? If the database become bigger and bigger, it will consume unnecessary effort to look up the keyword, so I think SELECT should specify what table it is referring to. Thanks for reading! 🥰

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT * does not fetch all tables from all schema. It only fetches the columns from the table you reference in your FROM clause. It only fetches the rows that match your WHERE clause.

The mistake is understandable given this statement in the MySQL documentation:

A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables:

  SELECT * FROM t1 INNER JOIN t2 ...

What they mean by “all tables” is only all tables referenced in this query. And only those in FROM or JOIN clauses. Not all tables everywhere.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0