- 4 years ago
- Zaid Bin Khalid
- 2265 Views
-
4
In this session, you will learn how to select specific records from a table by using Where & Having clause in SQL.
The SQL WHERE Clause.
The WHERE clause is used to filter records. On the other hand, we can express WHERE clause as a clause that extract only those records which fulfill a specified condition.
Selecting Record Based on Condition.
In the previous session, we have learned how to fetch all the records from a table or table columns. But, in the real world, we generally need to select, update or delete only those records which fulfill certain condition like users who belongs to a certain age group, or country, etc.
WHERE Syntax.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Filter Records with WHERE Clause.
The mention below SQL statement will return all the employees from the employee’s table, whose salary is greater than 7000. The WHERE clause simply filtered out unwanted data.
SELECT * FROM employees
WHERE salary > 7000;
After executing statement, the output will show something like this.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
+--------+--------------+------------+--------+---------+
Now you can see easily the output contains only those employees whose salary is greater than 7000.
Operators Allowed in WHERE Clause:
Since for WHERE clause, SQL has extensive support in handling large number of different operators. However, here in this table we have summarized the important one:
| Operator | Description | Example |
| = | Equal | WHERE id = 2 |
| > | Greater than | WHERE age > 30 |
| < | Less than | WHERE age < 18 |
| >= | Greater than or equal | WHERE rating >= 4 |
| <= | Less than or equal | WHERE price <= 100 |
| LIKE | Simple pattern matching | WHERE name LIKE ‘Dav’ |
| IN | Check whether a specified value matches any value in a list or subquery | WHERE country IN (‘USA’, ‘UK’) |
| BETWEEN | Check whether a specified value is within a range of values | WHERE rating BETWEEN 3 AND 5 |
SQL HAVING Clause.
In this session, you will learn how to filter the groups returned by a GROUP BY clause.
The SQL HAVING Clause.
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions
Filtering the Groups Based on Condition.
The HAVING clause is used with the GROUP BY clause to specify a filter condition for a group or an aggregate. Interestingly, one can only use HAVING clause with the SELECT statement
To understand this more easily, take a deep look to the following tables of employees and department.
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
Now, let’s suppose instead of finding just the name of the employees and their departments, you want to find out the names of those departments in which there are no employees. In this type of situation, you can use the HAVING clause with the GROUP BY clause, like this.
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name
HAVING total_employees = 0;
After the execution of the above SQL query, your output will look something like this:
+------------------+-----------------+
| dept_name | total_employees |
+------------------+-----------------+
| Customer Service | 0 |
+------------------+-----------------+
- 4 years ago
- Zaid Bin Khalid
- 2265 Views
-
4