Showing posts with label value2. Show all posts
Showing posts with label value2. Show all posts

Monday, October 19, 2015

SQL IN Syntax BETWEEN Operator

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

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
 
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
 The following SQL statement selects all customers with a City of "Paris" or "London":

Example

SELECT * FROM Customers
WHERE City IN ('Paris','London'); 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Around the Horn  Thomas Hardy  120 Hanover Sq.  London  WA1 1DP  UK 
11  B's Beverages  Victoria Ashworth  Fauntleroy Circus  London  EC2 5NT  UK 
16  Consolidated Holdings  Elizabeth Brown  Berkeley Gardens 12 Brewery   London  WX1 6LT  UK 
19  Eastern Connection  Ann Devon  35 King George  London  WX3 6FW  UK 
53  North/South  Simon Crowther  South House 300 Queensbridge  London  SW7 1RZ  UK 
57  Paris spécialités  Marie Bertrand  265, boulevard Charonne  Paris  75012  France 
 
 
The BETWEEN operator is used to select values within a range.

The SQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
 
 














Below is a selection from the "Products" table:
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 - 12 oz bottles 19
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10
4 Chef Anton's Cajun Seasoning 1 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 1 2 36 boxes 21.35

BETWEEN Operator Example

 

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
;

 

ProductIDProductNameSupplierIDCategoryIDUnitPrice
Chais  10 boxes x 20 bags  18 
Chang  24 - 12 oz bottles  19 
Aniseed Syrup  12 - 550 ml bottles  10 
15  Genen Shouyu  24 - 250 ml bottles  15.5 
16  Pavlova  32 - 500 g boxes  17.45