WHERE vs HAVING- Part I

365 Careers
A free video tutorial from 365 Careers
Creating opportunities for Business & Finance students
4.5 instructor rating • 71 courses • 1,381,164 students

Lecture description

In SQL, the conditions defined by the WHERE clause are applied before re-organizing the output into groups, while the conditions set by the HAVING clause are implemented after that moment. In this video, we will elaborate on this key distinction between the two clauses.

Learn more from the full course

SQL - MySQL for Data Analytics and Business Intelligence

SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management

09:32:46 of on-demand video • Updated March 2021

  • Become an expert in SQL
  • Learn how to code in SQL
  • Boost your resume by learning an in-demand skill
  • Create, design, and operate with SQL databases
  • Start using MySQL – the #1 Database Management System
  • Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
  • Adopt professionally tested SQL best practices
  • Gain theoretical insights about relational databases
  • Work with a sophisticated real-life database throughout the course
  • Get maximum preparation for real-life database management
  • Add data analytical tools to your skillset
  • Develop business intuition while solving tasks with big data
  • Study relational database management theory that you will need in your workplace every day
  • Learn how to create a database from scratch
  • The ability to take control of your dataset – insert, update, and delete records from your database
  • Be confident while working with constraints and relating data tables
  • Become a proficient MySQL Workbench user
  • Acquire top-notch coding techniques and best practices
  • Know how to answer specific business questions by using SQL’s aggregate functions
  • Handle complex SQL joins with ease
  • Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
  • Merge coding skills and business acumen to solve complex analytical problems
  • Become a proficient SQL user by writing flawless and efficient queries
  • Tons of exercises that will solidify your knowledge
  • The freedom to query anything you like from a database
English [Auto] It is important to decide whether to use where or having in certain situations it is a topic deserving more of our attention. That's why in this lecture we'll go through an example. Here's a summary of where and having where allows us to set conditions that refer to subsets of individual rows. These conditions are applied before reorganizing the output into groups once the rows that satisfy the where conditions are chosen. They progress in the data retrieval process and can be grouped by distinct values recorded in a certain field or fields. It is not until this moment when the output can be further improved or filtered with a condition specified in the having class. Finally you could sort the records of the final list through the ORDER BY clause to reinforce your understanding of the data retrieval process. Let's see an example containing both a where and a having condition. Here's a task. Extract a list of all names that are encountered less than 200 times. Let the data refer to people hired after the first of January 1999 only. OK so let's create the query step by step. Apparently we must select the first names and the number of times a first name is encountered renaming the second selection as names count. That's OK. The second thing to do is designate the table. We will retrieve data from employees fantastic. Here comes the interesting bit. Should we use only where only having or both keywords while setting are conditions Well there are two conditions to satisfy to solve our problem. One is that the names must be encountered less than 200 times 200 times. Immediately means we must use count which will count the number of times a certain first name appears in the employee's data table count is an aggregate function and as we said in the previous video it must go with having cool the other condition to satisfy is general. All the rows extracted must be of people who were hired after the first of January 1999 this condition refers to all individual rows in the Employees table. No specific aggregate function must be applied. Therefore this condition must go with the WHERE clause between the where and the having blocks. We shouldn't forget to insert the group by segment. We must group by first name not by some other field. Since our task requires us to aggregate our output by the number of times a certain first name is encountered. Good Actually let's order the output by the first name in ascending order. All right great. So this looks like a more daunting query doesn't it. Nine rows of code more elements to consider while creating it as school starts being more fun when you agree lets press control and enter to execute the operation. Eight hundred and eighty six rows were extracted. Awesome. The query worked and solve our problem. We have a list with a number of all distinct names of people hired after the first of January 1999. Fantastic. Let's keep up the pace for our next video. Thanks for watching.