IN Operator & HAVING Clause in SQL

10 Mar,2022

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Syntax:

SELECT column1, column2,..
FROM table
WHERE column IN (value1, value2, value3,...)
-- or
SELECT column1, column2,..
FROM table
WHERE column IN (SELECT query)

For the demo purpose, we will use the following tables in all examples.

Employee TableEmpIdFirstNameLastNameEmailSalaryDeptId1'John’’King’’john.king@abc.com’3300012'James’’Bond’13'Neena’’Kochhar’’neena@test.com’1700024'Lex’’De Haan’’lex@test.com’1500015'Amit’’Patel’1800036'Abdul’’Kalam’’abdul@test.com’250004

Department TableDeptIdName1'Finance’2'HR’3'Sales’4'Admin’

Consider the following query.

SQL Script: IN Operator

Copy

SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE EmpId IN (1, 3, 5, 6)

The above query will return records where EmpId is 1 or 3 or 5 or 6. The above query will display the following result.

Get Riturajbelwal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

EmpIdFirstNameLastNameSalary1'John’’King’330003'Neena’’Kochhar’170005'Amit’’Patel’180006'Abdul’’Kalam’25000

The following query uses the string values with the IN operator.

SQL Script: IN Operator

Copy

SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE FirstName IN ('james','john','abdul');

EmpIdFirstNameLastNameSalary1'John’’King’330002'James’’Bond’6'Abdul’’Kalam’25000

Note that wildcard characters ‘%’, ‘_’, etc. cannot be used with the string values.

HAVING CLAUSE IN SQL

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following code block shows the position of the HAVING Clause in a query.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following code block has the syntax of the SELECT statement including the HAVING clause −

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

This would produce the following result −

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+
|  2 | Khilan |  25 | Delhi   | 1500.00 |
+----+--------+-----+---------+---------+