SQL In

SQL In

This page covers SQL In operator.

The “in” operator is part of the logical operators.

This operator represents the “or” operator, and it allows the choice between multiple values.

Within this section, we also introduce the concept of subqueries.

For more information on operators here.

This tutorial encompasses:

• in
• not in
• subqueries
• subqueries with in

Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

In

The “in” operator provides the choice of multiple specific selections.

For instance, with the “where” statement you can specify a filter that retrieves data with only one value (e.g. WHERE age=21).

With the “in” logical operator, we can specify multiple values.

Let’s use the “student_info” table and select all students 19, 20, and 21 years old students.

SELECT * FROM student_info
WHERE age IN (19, 20, 21);

id	firstName	lastName	age
1	John	Smith	21
3	Sam	Adams	19
5	Patrick	Dumont	21

Not In

The “in” operator can be combined with the “not” operator.

The following statement retrieves all students that are not 19, 20, or 21 years old.

SELECT * FROM student_info
WHERE age NOT IN (19, 20, 21);

id	firstName	lastName	age
2	Patrick	Casey	26

The output is 1 student. Please note that the 4th student has a NULL (empty) age value, hence the exclusion.

Subqueries

Many programming languages have the concept of nested objects, or in this instance subqueries.

Nested refers to an object inside another object of the same type. Subqueries represent the same idea.

For example, a “select” statement inside another “select” statement.

The main statement is called either the parent or outer statement. Likewise, the second statement refers to either the child or inner statement.

The child statement executes first, followed by the parent statement.

Subqueries with In

Some of the logical operators work well with subqueires.

The “in” operator is a good combination with a subquery. Below is an example.

SELECT * FROM student_info
WHERE id IN (SELECT studentID FROM grade);

Remember that the child (inner) statement executes first. It selects all student ids from the “grade” column.

Next is the main “select” statement, which selects all students with retrieves all student information from the “student_info” table. It filters only students where the ids match the ids in the “grade” table.

The 4th record in the “grade” table contains a NULL value (empty cell). Therefore, the output displays only 4 of the students.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
5	Patrick	Dumont	21

---

Next: SQL Exists