Главная » Языки программирования

example1-in-function- php MySQL examples | w3resource

На чтение 8 мин
Содержание
  1. MySQL IN
  2. Introduction to the MySQL IN operator
  3. MySQL IN operator and NULL
  4. MySQL IN operator examples
  5. Summary
  6. MySQL IN() function
  7. MySQL: IN Condition
  8. Description
  9. Syntax
  10. Parameters or Arguments
  11. Note
  12. Example — With Character
  13. Example — With Numeric
  14. Example — Using NOT operator

MySQL IN

Summary: in this tutorial, you will learn how to use MySQL IN operator to determine if a specified value matches any value in a list of values.

Introduction to the MySQL IN operator

The IN operator allows you to determine if a value matches any value in a list of values. Here’s the syntax of the IN operator:

value IN (value1, value2, value3. )Code language: SQL (Structured Query Language) (sql)

The IN operator returns 1 (true) if the value equals any value in the list ( value1 , value2 , value3 ,…). Otherwise, it returns 0.

  • First, specify the value to test on the left side of the IN operator. The value can be a column or an expression.
  • Second, specify a comma-separated list of values to match in the parentheses.

The IN operator is functionally equivalent to the combination of multiple OR operators:

value = value1 OR value = value2 OR value = value3 OR . Code language: SQL (Structured Query Language) (sql)

The following example returns 1 because 1 is in the list:

+--------------+ | 1 IN (1,2,3) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)Code language: plaintext (plaintext)

The following example returns 0 because 4 is not in the list:

+--------------+ | 4 IN (1,2,3) | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)Code language: plaintext (plaintext)

In practice, you’ll use the IN operator to form conditions in a WHERE clause of the SELECT , DELETE , and UPDATE statements. Also, you’ll use the IN operator in a query that contains a subquery.

MySQL IN operator and NULL

Generally, the IN operator returns NULL in two cases:

  • The value on the left side of the operator is NULL.
  • The value doesn’t equal any value in the list and one of values in the list is NULL.

The following example returns NULL because the value of the left side of the IN operator is NULL:

SELECT NULL IN (1,2,3);Code language: PHP (php)
+-----------------+ | NULL IN (1,2,3) | +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec)Code language: plaintext (plaintext)

The following example also returns NULL because the 0 is not equal to any value in the list and the list has one NULL:

SELECT 0 IN (1 , 2, 3, NULL);Code language: PHP (php)
+-----------------------+ | 0 IN (1 , 2, 3, NULL) | +-----------------------+ | NULL | +-----------------------+ 1 row in set (0.00 sec)Code language: plaintext (plaintext)

The following example also returns NULL because NULL is not equal to any value in the list and the list has one NULL. Note that NULL is not equal to NULL.

SELECT NULL IN (1 , 2, 3, NULL);Code language: PHP (php)

MySQL IN operator examples

See the following offices table from the sample database:

Читайте также:  Basic Ajax & db interaction

MySQL IN Operator - Sample Table

The following example uses the IN operator to find the offices located in the USA and France:

SELECT officeCode, city, phone, country FROM offices WHERE country IN ('USA' , 'France');Code language: SQL (Structured Query Language) (sql)
+------------+---------------+-----------------+---------+ | officeCode | city | phone | country | +------------+---------------+-----------------+---------+ | 1 | San Francisco | +1 650 219 4782 | USA | | 2 | Boston | +1 215 837 0825 | USA | | 3 | NYC | +1 212 555 3000 | USA | | 4 | Paris | +33 14 723 4404 | France | +------------+---------------+-----------------+---------+ 4 rows in set (0.01 sec)Code language: plaintext (plaintext)

You can also get the same result with the OR operator like this:

SELECT officeCode, city, phone FROM offices WHERE country = 'USA' OR country = 'France';Code language: SQL (Structured Query Language) (sql)

In case the list has many values, you need to construct a very long statement with multiple OR operators. Hence, the IN operator allows you to shorten the query and make it more readable.

Summary

  • Use the IN operator to check if a value is in a set of values.
  • Use the IN operator to form a condition for the WHERE clause.

Источник

MySQL IN() function

MySQL IN() function finds a match in the given arguments.

The function returns 1 if expr is equal to any of the values in the IN list, otherwise, returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules.

Читайте также:  Как использовать формы html

MySQL Version: 5.6

Example: MySQL IN() function

The following MySQL statement will return 1 because the specified value is within the range of values.

Sample Output:

mysql> SELECT 10 IN(15,10,25); +-----------------+ | 10 IN(15,10,25) | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec)

Example : IN() function with where clause

The following MySQL statement checks which books have either 300 or 400 or 500 pages.

SELECT book_name,dt_of_pub,no_page FROM book_mast WHERE no_page IN (300,400,500);

Relational Algebra Expression:

Relational Algebra Expression: MySQL IN() function.

Relational Algebra Tree:

Relational Algebra Tree: MySQL IN() function.

Sample table: book_mast

Sample Output:

mysql> SELECT book_name,dt_of_pub,no_page -> FROM book_mast -> WHERE no_page IN (300,400,500); +-------------------------------------+------------+---------+ | book_name | dt_of_pub | no_page | +-------------------------------------+------------+---------+ | Understanding of Steel Construction | 2003-07-15 | 300 | | Fundamentals of Thermodynamics | 2002-10-14 | 400 | +-------------------------------------+------------+---------+ 2 rows in set (0.09 sec)
         
  query('SELECT book_name,dt_of_pub,no_page FROM book_mast WHERE no_page IN (300,400,500)') as $row) < echo ""; echo ""; echo ""; echo ""; echo ""; > ?>
 
BookDate of publishNumber of pages 
" . $row['book_name'] . "" . $row['dt_of_pub'] . "" . $row['no_page'] . "
 
 
 
            Book Date of publish Number of pages      %> catch (Exception ex) < out.println("Can’t connect to database."); >%>

Online Practice Editor:

Slideshow of MySQL Comparison Function and Operators

MySQL Comparison Function and Operators, slide presentation

Previous: GREATEST()
Next: INTERVAL()

Follow us on Facebook and Twitter for latest update.

  • Weekly Trends
  • Java Basic Programming Exercises
  • SQL Subqueries
  • Adventureworks Database Exercises
  • C# Sharp Basic Exercises
  • SQL COUNT() with distinct
  • JavaScript String Exercises
  • JavaScript HTML Form Validation
  • Java Collection Exercises
  • SQL COUNT() function
  • SQL Inner Join
  • JavaScript functions Exercises
  • Python Tutorial
  • Python Array Exercises
  • SQL Cross Join
  • C# Sharp Array Exercises

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook

Источник

MySQL: IN Condition

totn MySQL

This MySQL tutorial explains how to use the MySQL IN condition with syntax and examples.

Description

The MySQL IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in MySQL is:

expression IN (value1, value2, . value_n);

Parameters or Arguments

expression The value to test. value1, value2, . or value_n These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true. This is a quick method to test if any one of the values matches expression. subquery This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.

Читайте также:  Raw to string java

Note

  • The MySQL IN condition will return the records where expression is value1, value2. or value_n.
  • The MySQL IN condition is also called the MySQL IN operator.

Example — With Character

Let’s look at a MySQL IN condition example using character values.

The following is a MySQL SELECT statement that uses the IN condition to compare character values:

SELECT * FROM contacts WHERE last_name IN ('Johnson', 'Anderson', 'Smith');

This MySQL IN condition example would return all rows from the contacts table where the last_name is either Johnson, Anderson or Smith. Because the * is used in the SELECT, all fields from the contacts table would appear in the result set.

The above IN example is equivalent to the following SELECT statement:

SELECT * FROM contacts WHERE last_name = 'Johnson' OR last_name = 'Anderson' OR last_name = 'Smith';

As you can see, using the MySQL IN condition makes the statement easier to read and more efficient.

Example — With Numeric

Next, let’s look at a MySQL IN condition example using numeric values.

SELECT * FROM suppliers WHERE supplier_id IN (200, 201, 203, 300);

This MySQL IN condition example would return all suppliers where the supplier_id is either 200, 201, 203, or 300.

The above IN example is equivalent to the following SELECT statement:

SELECT * FROM suppliers WHERE supplier_id = 200 OR supplier_id = 201 OR supplier_id = 203 OR supplier_id = 300;

Example — Using NOT operator

Finally, let’s look at an IN condition example using the NOT operator.

SELECT * FROM contacts WHERE last_name NOT IN ('Johnson', 'Anderson', 'Smith');

This MySQL IN condition example would return all rows from the contacts table where the last_name is not Johnson, Anderson, or Smith. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Источник

Оцените статью