Re: What are some common questions related to SQL optimization?

[Phillip Ward]

MySQl or SQL Server?

Answering for MySQL:

Quote

P: Even if there is no GROUP BY clause in a SQL query, it can have a HAVING clause.

False.
Syntactically, HAVING must follow GROUP BY. If there is no GROUP BY, then you're aggregating at the table level, so WHERE conditions do exactly the same job.

Quote

Q: If a SQL query has a GROUP BY clause, it can only have a HAVING clause.

False.
You can have both WHERE and HAVING clauses.

Quote

R: The SELECT clause must include all attributes used in the GROUP BY clause.

True.
Unless you tell MySQL that you really do want to do stupid things with your aggregate queries and allow yourself to write queries that every other DBMS will arbitrarily throw out the window.

Quote

S: The SELECT clause does not have to include all of the attributes used in the GROUP BY clause.

False ... unless... See previous answer.

BTW: None of these have anything to do with Query Optimisation - more a basic understanding of SQL syntax.