여러분이 사용하고 계신 브라우저는 HTML5를 지원하지 않기 때문에 몇몇 요소가 제대로 보이도록 JScript를 사용하고 있습니다. 하지만 여러분의 브라우저 설정에서 스크립트 기능이 꺼져있으므로, 현재 페이지를 제대로 확인하시려면 스크립트 기능을 켜주셔야 합니다. SQL - BETWEEN 연산자
SQL – BETWEEN 연산자
3주전 작성
2주전 수정

BETWEEN

BETWEEN 연산자는 지정된 범위 내의 값을 선택한다.
값은 숫자, 텍스트 또는 날짜일 수 있다.

BETWEEN 연산자는 시작 및 끝 값을 포함한다.

구문

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

데이터베이스 예시

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

가격이 10에서 20 사이인 모든 제품을 선택

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
기본 예시
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
15 Genen Shouyu 6 2 24 – 250 ml bottles 15.5
16 Pavlova 7 3 32 – 500 g boxes 17.45
21 Sir Rodney’s Scones 8 3 24 pkgs. x 4 pieces 10
25 NuNuCa Nuß-Nougat-Creme 11 3 20 – 450 g glasses 14
31 Gorgonzola Telino 14 4 12 – 100 g pkgs 12.5
34 Sasquatch Ale 16 1 24 – 12 oz bottles 14
35 Steeleye Stout 16 1 24 – 12 oz bottles 18
36 Inlagd Sill 17 8 24 – 250 g jars 19
39 Chartreuse verte 18 1 750 cc per bottle 18
40 Boston Crab Meat 19 8 24 – 4 oz tins 18.4
42 Singaporean Hokkien Fried Mee 20 5 32 – 1 kg pkgs. 14
44 Gula Malacca 20 2 20 – 2 kg bags 19.45
46 Spegesild 21 8 4 – 450 g glasses 12
48 Chocolade 22 3 10 pkgs. 12.75
49 Maxilaku 23 3 24 – 50 g pkgs. 20
50 Valkoinen suklaa 23 3 12 – 100 g bars 16.25
57 Ravioli Angelo 26 5 24 – 250 g pkgs. 19.5
58 Escargots de Bourgogne 27 8 24 pieces 13.25
66 Louisiana Hot Spiced Okra 2 2 24 – 8 oz jars 17
67 Laughing Lumberjack Lager 16 1 24 – 12 oz bottles 14
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 12.5
70 Outback Lager 7 1 24 – 355 ml bottles 15
73 Röd Kaviar 17 8 24 – 150 g jars 15
74 Longlife Tofu 4 7 5 kg pkg. 10
76 Lakkalikööri 23 1 500 ml 18
77 Original Frankfurter grüne Soße 12 2 12 boxes 13

NOT BETWEEN

예제의 범위를 벗어난 제품을 표시하려면 다음을 사용한다.

구문

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

가격이 10에서 20사이가 아닌 모든 제품을 선택

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
기본 예시
ProductID ProductName SupplierID CategoryID Unit Price
4 Chef Anton’s Cajun Seasoning 2 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 21.35
6 Grandma’s Boysenberry Spread 3 2 12 – 8 oz jars 25
7 Uncle Bob’s Organic Dried Pears 3 7 12 – 1 lb pkgs. 30
8 Northwoods Cranberry Sauce 3 2 12 – 12 oz jars 40
9 Mishi Kobe Niku 4 6 18 – 500 g pkgs. 97
10 Ikura 4 8 12 – 200 ml jars 31
11 Queso Cabrales 5 4 1 kg pkg. 21
12 Queso Manchego La Pastora 5 4 10 – 500 g pkgs. 38
13 Konbu 6 8 2 kg box 6
14 Tofu 6 7 40 – 100 g pkgs. 23.25
17 Alice Mutton 7 6 20 – 1 kg tins 39
18 Carnarvon Tigers 7 8 16 kg pkg. 62.5
19 Teatime Chocolate Biscuits 8 3 10 boxes x 12 pieces 9.2
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81
22 Gustaf’s Knäckebröd 9 5 24 – 500 g pkgs. 21
23 Tunnbröd 9 5 12 – 250 g pkgs. 9
24 Guaraná Fantástica 10 1 12 – 355 ml cans 4.5
26 Gumbär Gummibärchen 11 3 100 – 250 g bags 31.23
27 Schoggi Schokolade 11 3 100 – 100 g pieces 43.9
28 Rössle Sauerkraut 12 7 25 – 825 g cans 45.6
29 Thüringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
30 Nord-Ost Matjeshering 13 8 10 – 200 g glasses 25.89
32 Mascarpone Fabioli 14 4 24 – 200 g pkgs. 32
33 Geitost 15 4 500 g 2.5
37 Gravad lax 17 8 12 – 500 g pkgs. 26
38 Côte de Blaye 18 1 12 – 75 cl bottles 263.5
41 Jack’s New England Clam Chowder 19 8 12 – 12 oz cans 9.65
43 Ipoh Coffee 20 1 16 – 500 g tins 46
45 Røgede sild 21 8 1k pkg. 9.5
47 Zaanse koeken 22 3 10 – 4 oz boxes 9.5
51 Manjimup Dried Apples 24 7 50 – 300 g pkgs. 53
52 Filo Mix 24 5 16 – 2 kg boxes 7
53 Perth Pasties 24 6 48 pieces 32.8
54 Tourtière 25 6 16 pies 7.45
55 Pâté chinois 25 6 24 boxes x 2 pies 24
56 Gnocchi di nonna Alice 26 5 24 – 250 g pkgs. 38
59 Raclette Courdavault 28 4 5 kg pkg. 55
60 Camembert Pierrot 28 4 15 – 300 g rounds 34
61 Sirop d’érable 29 2 24 – 500 ml bottles 28.5
62 Tarte au sucre 29 3 48 pies 49.3
63 Vegie-spread 7 2 15 – 625 g jars 43.9
64 Wimmers gute Semmelknödel 12 5 20 bags x 4 pieces 33.25
65 Louisiana Fiery Hot Pepper Sauce 2 2 32 – 8 oz bottles 21.05
69 Gudbrandsdalsost 15 4 10 kg pkg. 36
71 Fløtemysost 15 4 10 – 500 g pkgs. 21.5
72 Mozzarella di Giovanni 14 4 24 – 200 g pkgs. 34.8
75 Rhönbräu Klosterbier 12 1 24 – 0.5 l bottles 7.75

가격이 10에서 20 사이인 모든 제품을 선택하고 CategoryID가 1,2 또는 3인 제품은 표시하지 않기

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
기본 예시
ProductID ProductName SupplierID CategoryID Unit Price
31 Gorgonzola Telino 14 4 12 – 100 g pkgs 12.5
36 Inlagd Sill 17 8 24 – 250 g jars 19
40 Boston Crab Meat 19 8 24 – 4 oz tins 18.4
42 Singaporean Hokkien Fried Mee 20 5 32 – 1 kg pkgs. 14
46 Spegesild 21 8 4 – 450 g glasses 12
57 Ravioli Angelo 26 5 24 – 250 g pkgs. 19.5
58 Escargots de Bourgogne 27 8 24 pieces 13.25
73 Röd Kaviar 17 8 24 – 150 g jars 15
74 Longlife Tofu 4 7 5 kg pkg. 10

Carnarvon Tigers와 Mozzarella di Giovanni 사이에 ProductName이 있는 모든 제품을 선택

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
기본 예시
ProductID ProductName SupplierID CategoryID Unit Price
18 Carnarvon Tigers 7 8 16 kg pkg. 62.5
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
39 Chartreuse verte 18 1 750 cc per bottle 18
4 Chef Anton’s Cajun Seasoning 2 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 21.35
48 Chocolade 22 3 10 pkgs. 12.75
38 Côte de Blaye 18 1 12 – 75 cl bottles 263.5
58 Escargots de Bourgogne 27 8 24 pieces 13.25
52 Filo Mix 24 5 16 – 2 kg boxes 7
71 Fløtemysost 15 4 10 – 500 g pkgs. 21.5
33 Geitost 15 4 500 g 2.5
15 Genen Shouyu 6 2 24 – 250 ml bottles 15.5
56 Gnocchi di nonna Alice 26 5 24 – 250 g pkgs. 38
31 Gorgonzola Telino 14 4 12 – 100 g pkgs 12.5
6 Grandma’s Boysenberry Spread 3 2 12 – 8 oz jars 25
37 Gravad lax 17 8 12 – 500 g pkgs. 26
24 Guaraná Fantástica 10 1 12 – 355 ml cans 4.5
69 Gudbrandsdalsost 15 4 10 kg pkg. 36
44 Gula Malacca 20 2 20 – 2 kg bags 19.45
26 Gumbär Gummibärchen 11 3 100 – 250 g bags 31.23
22 Gustaf’s Knäckebröd 9 5 24 – 500 g pkgs. 21
10 Ikura 4 8 12 – 200 ml jars 31
36 Inlagd Sill 17 8 24 – 250 g jars 19
43 Ipoh Coffee 20 1 16 – 500 g tins 46
41 Jack’s New England Clam Chowder 19 8 12 – 12 oz cans 9.65
13 Konbu 6 8 2 kg box 6
76 Lakkalikööri 23 1 500 ml 18
67 Laughing Lumberjack Lager 16 1 24 – 12 oz bottles 14
74 Longlife Tofu 4 7 5 kg pkg. 10
65 Louisiana Fiery Hot Pepper Sauce 2 2 32 – 8 oz bottles 21.05
66 Louisiana Hot Spiced Okra 2 2 24 – 8 oz jars 17
51 Manjimup Dried Apples 24 7 50 – 300 g pkgs. 53
32 Mascarpone Fabioli 14 4 24 – 200 g pkgs. 32
49 Maxilaku 23 3 24 – 50 g pkgs. 20
9 Mishi Kobe Niku 4 6 18 – 500 g pkgs. 97
72 Mozzarella di Giovanni 14 4 24 – 200 g pkgs. 34.8

Carnarvon Tigers와 Mozzarella di Giovanni 사이에 없는 ProductName이 있는 모든 제품을 선택

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
기본 예시
ProductID ProductName SupplierID CategoryID Unit Price
17 Alice Mutton 7 6 20 – 1 kg tins 39
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
40 Boston Crab Meat 19 8 24 – 4 oz tins 18.4
60 Camembert Pierrot 28 4 15 – 300 g rounds 34
30 Nord-Ost Matjeshering 13 8 10 – 200 g glasses 25.89
8 Northwoods Cranberry Sauce 3 2 12 – 12 oz jars 40
25 NuNuCa Nuß-Nougat-Creme 11 3 20 – 450 g glasses 14
77 Original Frankfurter grüne Soße 12 2 12 boxes 13
70 Outback Lager 7 1 24 – 355 ml bottles 15
16 Pavlova 7 3 32 – 500 g boxes 17.45
53 Perth Pasties 24 6 48 pieces 32.8
55 Pâté chinois 25 6 24 boxes x 2 pies 24
11 Queso Cabrales 5 4 1 kg pkg. 21
12 Queso Manchego La Pastora 5 4 10 – 500 g pkgs. 38
59 Raclette Courdavault 28 4 5 kg pkg. 55
57 Ravioli Angelo 26 5 24 – 250 g pkgs. 19.5
75 Rhönbräu Klosterbier 12 1 24 – 0.5 l bottles 7.75
73 Röd Kaviar 17 8 24 – 150 g jars 15
28 Rössle Sauerkraut 12 7 25 – 825 g cans 45.6
45 Røgede sild 21 8 1k pkg. 9.5
34 Sasquatch Ale 16 1 24 – 12 oz bottles 14
27 Schoggi Schokolade 11 3 100 – 100 g pieces 43.9
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 12.5
42 Singaporean Hokkien Fried Mee 20 5 32 – 1 kg pkgs. 14
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81
21 Sir Rodney’s Scones 8 3 24 pkgs. x 4 pieces 10
61 Sirop d’érable 29 2 24 – 500 ml bottles 28.5
46 Spegesild 21 8 4 – 450 g glasses 12
35 Steeleye Stout 16 1 24 – 12 oz bottles 18
62 Tarte au sucre 29 3 48 pies 49.3
19 Teatime Chocolate Biscuits 8 3 10 boxes x 12 pieces 9.2
29 Thüringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
14 Tofu 6 7 40 – 100 g pkgs. 23.25
54 Tourtière 25 6 16 pies 7.45
23 Tunnbröd 9 5 12 – 250 g pkgs. 9
7 Uncle Bob’s Organic Dried Pears 3 7 12 – 1 lb pkgs. 30
50 Valkoinen suklaa 23 3 12 – 100 g bars 16.25
63 Vegie-spread 7 2 15 – 625 g jars 43.9
64 Wimmers gute Semmelknödel 12 5 20 bags x 4 pieces 33.25
47 Zaanse koeken 22 3 10 – 4 oz boxes 9.5

데이터베이스 예시

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2

’01-7월-1996’과 ’31-7월-1996′ 사이의 OrderDate가 있는 모든 주문을 선택

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
기본 예시
OrderID CustomerID EmployeeID OrderDate ShipperID
10248  90  7/4/1996 
10249  81  7/5/1996 
10250  34  7/8/1996 
10251  84  7/8/1996 
10252  76  7/9/1996 
10253  34  7/10/1996 
10254  14  7/11/1996 
10255  68  7/12/1996 
10256  88  7/15/1996 
10257  35  7/16/1996 
10258  20  7/17/1996 
10259  13  7/18/1996 
10260  55  7/19/1996 
10261  61  7/19/1996 
10262  65  7/22/1996 
10263  20  7/23/1996 
10264  24  7/24/1996 
10265  7/25/1996 
10266  87  7/26/1996 
10267  25  7/29/1996 
10268  33  7/30/1996 
10269  89  7/31/1996 
참고
Mingg`s Diary
밍구
공부 목적 블로그