What are logical operators LIKE, IN and NOT in SQL?

Overview

In this shot, we learn about logical operators in SQL and test our understanding with some quizzes.

The main purpose of logical operators is to test for the correctness of some conditions.

Let’s learn about the following logical operators:

  • LIKE
  • IN
  • NOT

The LIKE logical operator

The LIKE operator is very useful when we’re working with text. We use it within a WHERE clause.

The LIKE operator is frequently used with the wildcard %, which means any number of characters. For example, %Abel% means, it can start with any number of characters, it must contain Abel, and it can have any number of characters at the end.

Wildcard characters

Wildcard Description Example
% Any string of zero or more characters WHERE fullName LIKE '%Lifaefi%' finds all students with the word “Lifaefi” anywhere in their full name.
_ (underscore) Any single character WHERE firstName LIKE '_oel' finds all four-lettered first names that end with “oel” (Noel, Joel, etc.).
[] Any single character within the specified range ([a-d]) or set ([abcd]) WHERE name LIKE '[B-K]en' finds usernames that end with “en” and start with any single character between B and K (Ben, Ken, etc.).
[^] Any single character not within the specified range ([^a-d]) or set ([^abcd]) WHERE firstName LIKE '%[^i]el' finds first names of all students that with “el” and where the following letter isn’t “i” (Gael, Abel, Joel, etc.).

Example

Let’s consider the Student table below:

ID Full name College
1 Abel Lifaefi Mbula ICT
2 Sarah Lifaefi Masika Engeniring
3 Patience Kavira Kahola Biodiversity
4 Jean-Pierre Badibanga Arts
5 Joseph Lifaefi Kambale Agronomy
6 Norbert Lifaefi Kakule Building
7 Abel Kahola Mbula ICT
8 Jean-Marie Lifaefi Nasibu ICT

Let’s select all students whose middle name is “Lifaefi.”

SELECT * FROM Student WHERE fullName LIKE '%Lifaefi%';

Here’s the result:

ID Full name College
1 Abel Lifaefi Mbula ICT
2 Sarah Lifaefi Masika Engeniring
5 Joseph Lifaefi Kambale Agronomy
6 Norbert Lifaefi Kakule Building
8 Jean-Marie Lifaefi Nasibu ICT

Quiz

Now that you understand the logical operator LIKE, try to answer the questions below.

  1. Find all students whose full names start with “Abel.”
  2. Find all students whose full names contain the string “Ka” somewhere in the full name.
  3. Find all students whose full names end with “la.”

Note: You are required to use the Student table.

The IN logical operator

We use the IN operator to filter data based on several possible values. We can see it like =, but for more than one item of that particular column.

The IN operator allows us to check one, two, or many column values for which we want to pull data within the same query. Each distinct value is separated with a comma (,).

Note: IN works both for numbers and text.

Example

Given the Student table above, let’s find all information regarding students who study ICT or Biodiversity.

SELECT *

FROM Student

WHERE college IN ('ICT', 'Biodiversity');

Here’s the result:

ID Full name College
1 Abel Lifaefi Mbula ICT
3 Patience Kavira Kahola Biodiversity
7 Abel Kahola Mbula ICT
8 Jean-Marie Lifaefi Nasibu ICT

Quiz

Consider the Accounts table below:

ID Name Person Sales_ID
1 Exxon Mobile Abel Lifaefi 1001
2 Apple Yves Madika 1011
3 Microsoft Patience Kavira 1021
4 AT&T Sarah Lifaefi 1031
5 General ELectric Valentin Nasibu 1041
6 Verizon Gisel Kabamba 1051
7 Amazon Pierre-Etienne Etukumalo 1061

Find the account “Name,” “Person,” and "Sales_ID` for Apple, Microsoft, and Verizon.

The logical operator NOT

The NOT operator is used to find the inverse results for LIKE, IN, and similar operators.

Example

Considering the Accounts table above, let’s find the account name, person, and sales_id for companies other than Apple, Microsoft, and Verizon.

SELECT name, person, sales_id

FROM accounts

WHERE name NOT IN ('Apple', 'Microsoft', 'Verizon');

Here is the result:

Name Person Sales_ID
Exxon Mobile Abel Lifaefi 1001
AT&T Sarah Lifaefi 1031
General Electric Valentin Nasibu 1041
Amazon Pierre-Etienne Etukumalo 1061

Quiz

Now, try to answer the questions below:

  1. Find all students whose full names do not start with “Abel.”
  2. Find all students who do not study ICT or Biodiversity.

Note: You are required to use the Student table.

Summary

Let’s recap.

Logical operators allow us to perform operations similar to using WHERE and =, but for specific cases:

  • LIKE: It is used for cases when we might not know exactly what we are looking for.

  • IN: It is used for more than one condition.

  • NOT: It is used with the two operators above, and helps us negate the query result easily.

Happy coding!

Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)