SQL – HAVING vs. WHERE

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly.

As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in more than 90% of the cases.

While you’re at it, you may want to re-write your query using ANSI version of the join:

SELECT  L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)

This would eliminate WHERE that was used as a theta join condition

Related Posts:

  1. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  2. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  3. Case in Select Statement
  4. What is a stored procedure?
  5. Unknown column in ‘field list’ error on MySQL Update query
  6. SQL Inner-join with 3 tables?
  7. Conversion failed when converting date and/or time from character string while inserting datetime
  8. MySQL create table if not exists and insert record only if table was created
  9. error, string or binary data would be truncated when trying to insert
  10. ORA-00907: missing right parenthesis
  11. How do I use properly CASE..WHEN in MySQL
  12. Rename column SQL Server 2008
  13. Why do we need “Relationships” between tables at all?
  14. Using group by on multiple columns
  15. How to declare a variable in MySQL?
  16. ORA-01843 not a valid month- Comparing Dates
  17. How do I escape a single quote in SQL Server?
  18. Teradata: how to convert varchar value (format ‘dd.mm.yyyy’) to date (format ‘yyyy-mm-dd’ )?
  19. How Stuff and ‘For Xml Path’ work in SQL Server?
  20. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  21. Oracle SELECT TOP 10 records
  22. must appear in the GROUP BY clause or be used in an aggregate function
  23. How can I return pivot table output in MySQL?
  24. The multi-part identifier could not be bound
  25. MySQL syntax for Join Update
  26. Get day of week in SQL Server 2005/2008
  27. incorrect syntax error CREATE VIEW must be the only statement in the batch
  28. PLS-00103: Encountered the symbol when expecting one of the following:
  29. SQLite – UPSERT *not* INSERT or REPLACE
  30. how to drop partition without dropping data in MySQL?
  31. Simple way to calculate median with MySQL
  32. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  33. Error 1046 No database Selected, how to resolve?
  34. What is it exactly a BLOB in a DBMS context
  35. missing FROM-clause entry for table
  36. MySQL Error 1264: out of range value for column
  37. How do you create a temporary table in an Oracle database?
  38. Oracle SQL: Update a table with data from another table
  39. How to SUM and SUBTRACT using SQL?
  40. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  41. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  42. Postgresql tables exists, but getting “relation does not exist” when querying
  43. Sql query to insert datetime in SQL Server
  44. SELECT DISTINCT on one column
  45. TSQL PIVOT MULTIPLE COLUMNS
  46. MySQL – UPDATE multiple rows with different values in one query
  47. How to create a MySQL hierarchical recursive query?
  48. Microsoft OLE DB Provider for SQL Server error ‘80004005’
  49. How to divide two columns?
  50. MySQL starts with searching issue
  51. SQL Parentheses use in an OR clause
  52. Is it possible to GROUP BY multiple columns using MySQL?
  53. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  54. SQL “select where not in subquery” returns no results
  55. ListAGG in SQLSERVER
  56. Equivalent of Oracle’s RowID in SQL Server
  57. Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
  58. Best way to test if a row exists in a MySQL table
  59. Subtract one day from datetime
  60. Get current year in TSQL
  61. Is it possible to specify condition in Count()?
  62. SQL Server: Make all UPPER case to Proper Case/Title Case
  63. How do I query for all dates greater than a certain date in SQL Server?
  64. Backup a single table with its data from a database in sql server 2008
  65. updating table rows in postgres using subquery
  66. Temporary table in SQL server causing ‘ There is already an object named’ error
  67. Oracle: If Table Exists
  68. MySQL OPTIMIZE all tables?
  69. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  70. Is there a coalesce-like function in Excel?
  71. How do I count unique items in field in Access query?
  72. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  73. Append table to an existing one: SQL Server
  74. How to order by column A and then by column B?
  75. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  76. MySQL LIKE IN()?
  77. Filter data based on date in sql
  78. What is a postgres superuser
  79. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  80. SQL query to extract only the “current” wp_posts?
  81. register_post_status – show_in_admin_all_list & show_in_admin_status_list does not affect query
  82. How to retrieve sticky post in raw sql?
  83. How to remove in the wordpress database all posts revisions except the last three?
  84. Backticks (`) Instead of Single Quotes (‘) in an SQL Statement?
  85. $wpdb get_var issue
  86. WP Optimization: Removing Orphaned wp_options (especially the autoload ones)
  87. Auto-update tables from database
  88. Inserting rows into a custom table, when plugin is activated
  89. dbDelta not creating table (yes, once more)
  90. Posts modified in the last 48 hours
  91. DB_HOST – is LOCALHOST speedy than domain name? [closed]
  92. post id not displaying
  93. How to export 2 week’s worth of posts
  94. Insert multiple checkbox values
  95. How to get EVENT based on startday, using BETWEEN
  96. wpdb prepare without placeholder
  97. Adding profile data to database
  98. How to get the sum of each post with the same date
  99. How to dump a Microsoft SQL Server database to a SQL script?
  100. Deleting the MySQL database

Leave a Comment

tech