MySQL में BETWEEN के साथ शुरुआत और अंत बिंदु को बाहर कैसे करें?

यदि आप BETWEEN में शुरुआत और अंत मान को शामिल नहीं करना चाहते हैं, तो NOT IN का उपयोग करें।

सिंटैक्स

SELECT * FROM yourTableName 
WHERE yourColumnName BETWEEN yourStartingValue AND yourEndingValue 
AND yourColumnName NOT IN (yourStartingValue, yourEndingValue);

उदाहरण के साथ समझें

सबसे पहले एक टेबल बनाते हैं:

mysql> CREATE TABLE BetweenWithoutEndPoints (
    -> Id INT NOT NULL AUTO_INCREMENT,
    -> Name VARCHAR(20),
    -> Age INT,
    -> PRIMARY KEY(Id)
    -> ); 
Query OK, 0 rows affected (0.54 sec)

रिकॉर्ड डालें

mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Mike', 23); 
Query OK, 1 row affected (0.21 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Larry', 25); 
Query OK, 1 row affected (0.21 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('David', 28); 
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Sam', 26); 
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Carol', 21); 
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('John', 29); 
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('James', 20); 
Query OK, 1 row affected (0.41 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Robert', 26); 
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO BetweenWithoutEndPoints(Name, Age) VALUES('Michael', 30); 
Query OK, 1 row affected (0.16 sec)

सभी रिकॉर्ड देखें

mysql> SELECT * FROM BetweenWithoutEndPoints;

आउटपुट

+----+---------+------+
| Id | Name    | Age  |
+----+---------+------+
| 1  | Mike    | 23   |
| 2  | Larry   | 25   |
| 3  | David   | 28   |
| 4  | Sam     | 26   |
| 5  | Carol   | 21   |
| 6  | John    | 29   |
| 7  | James   | 20   |
| 8  | Robert  | 26   |
| 9  | Michael | 30   |
+----+---------+------+
9 rows in set (0.00 sec)

शुरुआत और अंत बिंदु (3 और 8) को बाहर करके BETWEEN

mysql> SELECT * FROM BetweenWithoutEndPoints 
    -> WHERE Id BETWEEN 3 AND 8 AND Id NOT IN (3, 8);

आउटपुट

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
| 4  | Sam   | 26   |
| 5  | Carol | 21   |
| 6  | John  | 29   |
| 7  | James | 20   |
+----+-------+------+
4 rows in set (0.04 sec)

Id 3 और 8 को बाहर कर दिया गया, केवल 4, 5, 6, 7 बचे।