SQL Clean Code in SQL SELECT *
Example
SELECT * returns all columns in the same order as they are defined in the table.
When using SELECT *, the data returned by a query can change whenever the table definition changes. This increases the risk that different versions of your application or your database are incompatible with each other.
Furthermore, reading more columns than necessary can increase the amount of disk and network I/O.
So you should always explicitly specify the column(s) you actually want to retrieve:
--SELECT * don't
SELECT ID, FName, LName, PhoneNumber -- do
FROM Emplopees;
(When doing interactive queries, these considerations do not apply.)
However, SELECT * does not hurt in the subquery of an EXISTS operator, because EXISTS ignores the actual data anyway (it checks only if at least one row has been found). For the same reason, it is not meaningful to list any specific column(s) for EXISTS, so SELECT * actually makes more sense:
-- list departments where nobody was hired recently
SELECT ID,
Name
FROM Departments
WHERE NOT EXISTS (SELECT *
FROM Employees
WHERE DepartmentID = Departments.ID
AND HireDate >= '2015-01-01');
