TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

MySQL IN Operator

Overview

In this tutorial, we will understand MySQL IN Operator. We can use this operator in the SELECT
WHERE clause to search and find the rows in the specified set of values.

Syntax

The general syntax for the operator usage is as follows:

mysql> SELECT column_list
              FROM table_name
             WHERE search_column IN (list of values to search for);

The set of values in the IN operator is specified as a comma-delimited list.

Example

In this example, we will use the City database table from the world MySQL database. To know the columns and the data types of the columns of the table; Connect to the world MySQL database and issue the following command.

mysql> DESC City;

MySQL World City Table Describe

The query returns the rows of the City table and the selected columns where the city name in the list provided using the IN operator.

mysql> SELECT Id, Name, District
-> FROM City
-> WHERE Name IN (‘El Paso’,’New York’,’Santa Monica’);
+——+————–+————+
| Id | Name | District |
+——+————–+————+
| 3793 | New York | New York |
| 3815 | El Paso | Texas |
| 4060 | Santa Monica | California |
+——+————–+————+
3 rows in set (0.00 sec)

 

MySQL IN Operator

IN Operator Subquery

mysql> SELECT column_list
              FROM table_name
             WHERE search_column IN (Sub_Query);

The IN operator when used with a subquery, the condition is true for rows in the outer query that match any row returned by the subquery. The NOT IN construct is the exact opposite of IN, and is true for rows in the outer query that match no rows returned by the subquery.

The following example uses the IN keyword for a subquery that returns the Country codes for African countries, which are then used in the outer query to return the Population of each of these countries.

mysql> SELECT Name, Population FROM City
-> WHERE CountryCode IN
-> (SELECT Code FROM Country WHERE Continent = ‘Africa’)
-> LIMIT 10;
+————+————+
| Name | Population |
+————+————+
| Luanda | 2022000 |
| Huambo | 163100 |
| Lobito | 130000 |
| Benguela | 128300 |
| Namibe | 118200 |
| Bujumbura | 300000 |
| Cotonou | 536827 |
| Porto-Novo | 194000 |
| Djougou | 134099 |
| Parakou | 103577 |
+————+————+
10 rows in set (0.00 sec)

To limit the rows we have used the LIMIT clause.

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL SUBSTRING Function› MySQL DESCRIBE Statement

Recent Posts

  • Update draw.io on Windows
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com