محدودسازی، گروه بندی و مرتب سازی نتایج در MySQL

ساخت دیتابیس، جدول و ورود نمونه اطلاعات

قبل از پرداختن به مباحث اصلی آموزش ابتدا با استفاده از برنامه phpMyAdmin یک دیتابیس با نام دلخواه (ترجیحا Collation به صورت utf8_persian_ci) و دو جدول فرضی با نام های tbl_1_name و tbl_2_name با نمونه ردیف های زیر در آن ایجاد می کنیم، برای جدول tbl_1_name:

id  =>  int(11) AUTO_INCREMENT
name  =>  varchar(255)
age  =>  int(11)
point  =>  int(11)

هم به صورت دستی و هم با اجرای دستورات زیر در قسمت SQL برنامه phpMyAdmin می توانیم جدول بالا را ایجاد کنیم:

CREATE TABLE IF NOT EXISTS `tbl_1_name` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  `age` int(11) UNSIGNED DEFAULT NULL,
  `point` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

و برای جدول tbl_2_name:

id  =>  int(11) AUTO_INCREMENT
name  =>  varchar(255)

برای ساخت این جدول هم علاوه بر شیوه دستی می توانیم در قسمت SQL برنامه phpMyAdmin دستورات زیر را اجرا کنیم:

CREATE TABLE IF NOT EXISTS `tbl_2_name` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

نکته مهم: با توجه به اینکه ممکن است در آینده حروف زبان فارسی را در دیتابیس ذخیره یا از آن دریافت کنیم توصیه می شود Collation دیتابیس و جداول را در برنامه phpMyAdmin به صورت utf8_persian_ci تنظیم کنیم.
برای اینکه نمونه اطلاعاتی جهت تست و بررسی در اختیار داشته باشیم دستورات زیر را در قسمت SQL برنامه phpMyAdmin برای جدول tbl_1_name و tbl_2_name اجرا می کنیم، برای جدول tbl_1_name:

INSERT INTO `tbl_1_name` (`id`, `name`, `age`, `point`) VALUES
(1, 'A', 20, 12),
(2, 'B', 13, 15),
(3, 'C', 15, 33),
(4, 'D', 23, 12),
(5, 'E', 12, 13),
(6, 'F', 16, 35);

و برای جدول tbl_2_name:

INSERT INTO `tbl_2_name` (`id`, `name`) VALUES
(1, 'A'),
(2, 'C');

یا اینکه می توانیم کدهای زیر را در یک فایل PHP درج و با انجام تنظیمات اتصال به دیتابیس در قسمت متغیر conn، فایل PHP را در مرورگر فراخوانی و اجرا کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
$array = array(
    'name' => array('A', 'B', 'C', 'D', 'E', 'F'),
    'age' => array(20, 13, 15, 23, 12, 16),
    'point' => array(12, 15, 33, 12, 13, 35)
);
$count = count($array['name']);
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else {
    //ذخیره نمونه اطلاعات در جدول
    for($i = 0; $i < $count; $i++){
        $sql = "INSERT INTO tbl_1_name(name, age, point) VALUES('" . $array['name'][$i] . "', '" . $array['age'][$i] . "', '" . $array['point'][$i] . "')";
        $query = mysqli_query($conn, $sql);
         
        if(!$query) {
            echo "Inserting Into Table tbl_1_name: Error! " . mysqli_error($conn);
            break;
        } else {
            echo "tbl_1_name: Row " . ($i + 1) . " Inserted!<br>";
        }
    }
    
    $sql = "INSERT INTO tbl_2_name(name) VALUES('" . $array['name'][0] . "')";
    $query = mysqli_query($conn, $sql);
    
    if(!$query) {
        echo "Inserting Into Table tbl_2_name: Error! " . mysqli_error($conn) . '<br>';
    } else {
        echo "tbl_2_name: Row 1 Inserted!<br>";
    }
    
    $sql = "INSERT INTO tbl_2_name(name) VALUES('" . $array['name'][2] . "')";
    $query = mysqli_query($conn, $sql);
    
    if(!$query) {
        echo "Inserting Into Table tbl_2_name: Error! " . mysqli_error($conn) . '<br>';
    } else {
        echo "tbl_2_name: Row 2 Inserted!<br>";
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
$array = array(
    'name' => array('A', 'B', 'C', 'D', 'E', 'F'),
    'age' => array(20, 13, 15, 23, 12, 16),
    'point' => array(12, 15, 33, 12, 13, 35)
);
$count = count($array['name']);
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //ذخیره نمونه اطلاعات در جدول
    for($i = 0; $i < $count; $i++){
        $sql = "INSERT INTO tbl_1_name(name, age, point) VALUES('" . $array['name'][$i] . "', '" . $array['age'][$i] . "', '" . $array['point'][$i] . "')";
        $query = $conn->query($sql);
         
        if(!$query) {
            echo "Inserting Into Table tbl_1_name: Error! " . $conn->error;
            break;
        } else {
            echo "tbl_1_name: Row " . ($i + 1) . " Inserted!<br>";
        }
    }
    
    $sql = "INSERT INTO tbl_2_name(name) VALUES('" . $array['name'][0] . "')";
    $query = $conn->query($sql);
    
    if(!$query) {
        echo "Inserting Into Table tbl_2_name: Error! " . $conn->error . '<br>';
    } else {
        echo "tbl_2_name: Row 1 Inserted!<br>";
    }
    
    $sql = "INSERT INTO tbl_2_name(name) VALUES('" . $array['name'][2] . "')";
    $query = $conn->query($sql);
    
    if(!$query) {
        echo "Inserting Into Table tbl_2_name: Error! " . $conn->error . '<br>';
    } else {
        echo "tbl_2_name: Row 2 Inserted!<br>";
    }
}
//پایان اتصال
$conn->close();
?>

در نهایت اطلاعات جدول tbl_1_name باید به صورت زیر باشد:

id      name      age      point
1        A        20        12
2        B        13        15
3        C        15        33
4        D        23        12
5        E        12        13
6        F        16        35

و اطلاعات جدول tbl_2_name نیز به صورت زیر باشد:

id     name
1       A
2       C

از ساختار و اطلاعات این نمونه جداول برای تست و بررسی پارامترهایی که در ادامه خواهیم گفت استفاده می کنیم.

استفاده از LIMIT

اجرای پرس و جوهای هرچند ساده با استفاده از دستور WHERE به تنهایی و بدون محدود کردن تعداد نتایج با پارامترهای اضافه می تواند منجر به دریافت حجم زیادی از اطلاعات ناخواسته شود که قطعا حالت بهینه نیست و احتمالا باعث کندی برنامه و فشار بی دلیل بر پایگاه داده خواهد شد، از این رو در زبان SQL برای WHERE پارامترهای محدود کننده ای مانند LIMIT در نظر گرفته اند، دستور LIMIT تعداد ردیف های نتایج به دست آمده از اجرای پرس و جوهای دیتابیس را به موارد مشخصی محدود می کند، به فرض در پرس و جوی فرضی زیر صرفا 3 ردیف از اطلاعات به عنوان خروجی درخواست برگردانده می شود:

SELECT * FROM tbl_1_name WHERE name != '' LIMIT 3

id      name      age      point
1        A        20        12
2        B        13        15
3        C        15        33

علاوه بر تعیین تعداد ردیف ها می توانیم نقطه شروع انتخاب ردیف ها را هم برای MySQL مشخص کنیم که در این صورت برای LIMIT از دو پارامتر عددی استفاده می کنیم، عدد اول شماره ردیف برای تعیین نقطه آغاز دریافت نتایج و عدد دوم تعداد نتایجی است که پرس و جو را به آن محدود کرده ایم، به طور مثال در نمونه کد زیر از ردیف 2 به بعد 3 ردیف به عنوان نتیجه برگردانده می شود (شامل خود ردیف دوم نمی شود):

SELECT * FROM tbl_1_name WHERE name != '' LIMIT 2, 3

id      name      age      point
3        C        15        33
4        D        23        12
5        E        12        13

با استفاده از این شیوه می توانیم به صورت داینامیک قابلیت هایی مانند صفحه بندی محتوا را در برنامه های وب ایجاد کنیم که در آموزش های کاربردی MySQL در این خصوص خواهیم گفت، برای تست و بررسی دستور LIMIT کافی است پرس و جوهای بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
 برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT name, age, point FROM tbl_1_name WHERE name != '' LIMIT 2, 3";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_1_name: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' . 
                'Name: ' . $row['name'] . ' - ' . 
                'Age: ' . $row['age'] . ' - ' . 
                'Point: ' . $row['point'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_1_name!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT name, age, point FROM tbl_1_name WHERE name != '' LIMIT 2, 3";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_1_name: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Name: ' . $row['name'] . ' - ' . 
                'Age: ' . $row['age'] . ' - ' . 
                'Point: ' . $row['point'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_1_name!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه کدهای بالا به صورت زیر خواهد بود:

Row Count: 3
Row 1 => Name: C - Age: 15 - Point: 33
Row 2 => Name: D - Age: 23 - Point: 12
Row 3 => Name: E - Age: 12 - Point: 13

همان طور که مشخص است از ردیف دوم به بعد 3 ردیف انتخاب شده اند.

استفاده از GROUP BY

یک قابلیت کاربردی دیگر که در زمینه دسته بندی و نمایش بهتر نتایج حاصل از اجرای پرس و جوهای پایگاه داده MySQL می توانیم از آن استفاده کنیم قابلیت گروه بندی نتایج دارای مقادیر مشترک با GROUP BY و خلاصه کردن نتیجه در یک ردیف است، به فرض در نمونه اطلاعاتی که در دیتابیس بالا درج کرده ایم در جدول tbl_1_name دو ردیف 1 و 4 دارای point یکسان هستند و بر این اساس می توانیم نمونه پرس و جوی متغیر sql را به صورت زیر بنویسیم:

SELECT name, age, point FROM tbl_1_name WHERE name != '' GROUP BY point

با اجرای کدها خروجی به صورت نمونه زیر خواهد بود:

Row Count: 5
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: E - Age: 12 - Point: 13
Row 3 => Name: B - Age: 13 - Point: 15
Row 4 => Name: C - Age: 15 - Point: 33
Row 5 => Name: F - Age: 16 - Point: 35

با در نظر گرفتن دستور GROUP BY برای ستون دارای مقدار مشترک (در اینجا point) تمام ردیف ها در یک ردیف خلاصه می شوند، اگر در نتایج به دست آمده دقت کنیم GROUP BY صرفا در خلاصه کردن نتیجه بدست آمده تاثیرگذار نیست و نحوه چینش را هم بر اساس ستون در نظر گرفته شده تغییر می دهد.

استفاده از HAVING

به همراه گروه بندی با دستور GROUP BY می توانیم با HAVING (برگرفته از have به معنی داشتن) شرط مضاعف تعیین کنیم، به طور مثال می خواهیم در پرس و جوی بالا فقط ردیف هایی را در نتایج داشته باشیم که میزان ردیف سن از 15 بیشتر باشد، برای این منظور می توانیم نمونه پرس و جوی متغیر sql را به صورت زیر بنویسیم:

SELECT name, age, point FROM tbl_1_name WHERE name != '' GROUP BY point HAVING age > 15

با اجرای کدها خروجی به صورت نمونه زیر خواهد بود:

Row Count: 2
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: F - Age: 16 - Point: 35

استفاده از HAVING می تواند به همراه توابع تجمیعی (مانند SUM) باشد که در این خصوص در آموزش پیش رو به صورت اختصاصی صحبت خواهیم کرد.

استفاده از DISTINCT

عبارت DISTINCT به معنی جداسازی و افتراق است و برای اینکه نتایج تکراری را در پرس و جوهای MySQL نداشته باشیم می توانیم از این دستور استفاده کنیم، ممکن است DISTINCT و GROUP BY نتیجه مشابهی داشته باشند اما با دو هدف متفاوت در موتور MySQL تعریف شده اند و در مراحل متفاوتی پردازش می شوند:
- DISTINCT در مرحله انتخاب ردیف ها حذف موارد تکراری را اعمال می کند.
- GROUP BY پس از انتخاب ردیف ها گروه بندی نتایج یکسان را انجام می دهد.
این دو کارکرد متفاوت در هنگام نوشتن پرس و جوهای پیشرفته و چندبخشی به طور مثال در ترکیب با توابع تجمیعی (در آموزش بعدی توضیح خواهیم داد) این امکان را فراهم می کنند که در مراحل مختلف پردازش پرس و جو دستورات متفاوتی را با هم استفاده و نتایج دقیق و و یکتا (Unique) به دست آوریم.
برای آشنایی بیشتر با نحوه کار دستور DISTINCT در نمونه اطلاعاتی که در دیتابیس بالا درج کرده ایم در جدول tbl_1_name دو ردیف 1 و 4 دارای point یکسان هستند و بر این اساس می توانیم نمونه پرس و جوی متغیر sql را به صورت زیر بنویسیم:

SELECT DISTINCT point FROM tbl_1_name WHERE name != ''

با اجرای کدها و اصلاح قسمت while به این شکل:

while($row = mysqli_fetch_array($query)){
    echo 'Row ' . $loop . ' => ' .
    'Point: ' . $row['point'] . '<br><br>';
    
    $loop++;
}

ردیف دوم با مقدار point 12 حذف و خروجی به صورت نمونه زیر خواهد بود:

Row Count: 5
Row 1 => Point: 12
Row 2 => Point: 13
Row 3 => Point: 15
Row 4 => Point: 33
Row 5 => Point: 35

اگر برای دستور DISTINCT چند ستون در نظر بگیریم، به فرض:

SELECT DISTINCT point, name FROM tbl_1_name WHERE name != ''

هر دو ستون (point و name) باید در یک ردیف شامل مقدار مشترک باشند تا در نتیجه پرس و جو ردیف های تکراری آنها حذف شود در غیر اینصورت به فرض اگر فقط یک ستون دارای مقدار مشترک باشد عمل حذف ردیف تکراری انجام نمی شود.

استفاده از ORDER BY

در بسیاری از برنامه ها و صفحات وب نیاز به مرتب سازی و استخراج نتایج سفارشی از پایگاه داده به فرض بر اساس شماره ID، به ترتیب حروف الفبا، طبق تاریخ، بر اساس بزرگتر کوچکتری مقادیر ستون های عددی و مواردی از این قبیل وجود دارد که به همین دلیل پارامتر دیگری تحت عنوان ORDER BY در زبان SQL در نظر گرفته شده است، با استفاده از این دستور در کنار WHERE می توانیم نتایج سفارشی از پرس و جوها استخراج یا آنها را مرتب سازی کنیم، به فرض اگر بخواهیم نتایج پرس و جو در نمونه دیتابیس بالا بر اساس سن کاربران از بزرگتر به کوچکتر دریافت و چینش شوند می توانیم متغیر sql را به شکل زیر تغییر دهیم:

SELECT name, age, point FROM tbl_1_name WHERE name != '' ORDER BY age LIMIT 3

با اجرای کدها خروجی به صورت نمونه زیر خواهد بود:

Row Count: 3
Row 1 => Name: E - Age: 12 - Point: 13
Row 2 => Name: B - Age: 13 - Point: 15
Row 3 => Name: C - Age: 15 - Point: 33

با دقت در نحوه چینش ردیف ها مشخص است که اطلاعات بر اساس سن (مقادیر ستون age) از کوچکتر به بزرگتر انتخاب و مرتب سازی شده اند.

استفاده از ASC، DESC و تابع RAND

در حالت پیش فرض MySQL نتایج را به صورت صعودی (از کوچک به بزرگ یا Ascending) مرتب سازی می کند، اما مرتب سازی به صورت نزولی (از بزرگ به کوچک یا Descending) و همچنین انتخاب اتفاقی (تصادفی یا Random) با افزودن عباراتی که در ادامه بررسی می کنیم امکانپذیر است، برای تعیین نوع انتخاب و مرتب سازی می توانیم یکی از پارامترهای زیر را در کنار دستور ORDER BY استفاده کنیم:
ASC: استخراج و مرتب سازی پیش فرض یا صعودی (از کوچک به بزرگ)
DESC: استخراج و مرتب سازی نزولی (از بزرگ به کوچک)
RAND: استخراج و مرتب سازی به صورت اتفاقی (تصادفی)
بر این اساس کدی که در بالا نوشتیم را می توانیم به صورت های زیر ویرایش و چینش نتایج حاصل از اجرای پرس و جو را به شکل سفارشی و دلخواه تغییر دهیم.
تغییر پرس و جو با استفاده از ASC برای استخراج و نمایش نتایج به صورت چینش صعودی (حالت پیش فرض):

SELECT name, age, point FROM tbl_1_name WHERE name != '' ORDER BY age ASC LIMIT 3

نمونه خروجی:

Row Count: 3
Row 1 => Name: E - Age: 12 - Point: 13
Row 2 => Name: B - Age: 13 - Point: 15
Row 3 => Name: C - Age: 15 - Point: 33

تغییر پرس و جو با استفاده از DESC برای استخراج و نمایش نتایج به صورت چینش نزولی:

SELECT name, age, point FROM tbl_1_name WHERE name != '' ORDER BY age DESC LIMIT 3

نمونه خروجی:

Row Count: 3
Row 1 => Name: D - Age: 23 - Point: 12
Row 2 => Name: A - Age: 20 - Point: 12
Row 3 => Name: F - Age: 16 - Point: 35

همانطور که مشخص است ردیف ها بر اساس بیشترین مقدار ستون age انتخاب و مرتب سازی شده اند.
تغییر پرس و جو با استفاده از تابع RAND برای استخراج و نمایش نتایج به صورت چینش اتفاقی (تصادفی):

SELECT name, age, point FROM tbl_1_name WHERE name != '' ORDER BY RAND() LIMIT 3

نمونه خروجی:

Row Count: 3
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: D - Age: 23 - Point: 12
Row 3 => Name: E - Age: 12 - Point: 13

با دقت در کدها مشخص است که نام هیچ ستونی پس از ORDER BY درج نشده و برای RAND هم از کاراکترهای () استفاده کرده ایم، این کار به دلیل تابع بودن RAND در MySQL است به عبارتی ماهیتا این پارامتر با پارامترهای دیگر متفاوت است و باید Syntax صحیح را در نگارش پرس و جو رعایت کنیم تا برنامه با خطا مواجه نشود.

استفاده از AND و OR

در آموزش های مقدماتی زبان PHP با کاربرد کاراکترهای && و || برای سخت و محدود کردن یا بسط دامنه شمول مقایسه های شرطی آشنا شدیم، گفتیم که در برنامه نویسی اگر بخواهیم شرط را سخت و محدود به بررسی پارامترهای بیشتر کنیم از کاراکتر && و اگر بخواهیم دامنه شمول کدهایمان را بسط داده و با تحقق یکی از شرایط، مقایسه برقرار باشد از کاراکتر || استفاده می کنیم، مشابه این مبحث در نوشتن پرس و جوهای MySQL نیز با دستورات AND (به معنی 'و') و OR (به معنی 'یا') قابل استفاده است، از دستور AND برای محدود کردن هرچه بیشتر ردیف های انتخابی و از OR برای بسط پرس و جو و انتخاب ردیف ها با تحقق یکی از شرایط بهره می گیریم.
به طور مثال اگر بخواهیم از نمونه جدول بالا اسامی را استخراج کنیم که age آنها از 15 بزرگتر و point آنها از 15 کوچکتر باشد خواهیم نوشت:

SELECT name, age, point FROM tbl_1_name WHERE name != '' AND age > 15 AND point < 15

دستور بالا کاربرانی را که سن آنها بالاتر از 15 و امتیاز کسب شده کمتر از 15 باشد را در لیست نتایج پرس و جو نمایش می دهد:

Row Count: 2
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: D - Age: 23 - Point: 12

همین پرس و جو را اگر با OR بنویسیم خروجی متفاوت خواهد بود:

SELECT name, age, point FROM tbl_1_name WHERE name != '' AND age > 15 OR point < 15

Row Count: 4
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: D - Age: 23 - Point: 10
Row 3 => Name: E - Age: 12 - Point: 13
Row 4 => Name: F - Age: 16 - Point: 35

در این حالت ردیف هایی از دیتابیس انتخاب می شوند که یا age آنها از 15 بزرگتر باشد یا اینکه point آنها از 15 کمتر باشد، با برقرار بودن هر کدام از این دو شرط فیلد متناظر انتخاب و به لیست نتایج اضافه می شود.

استفاده از IN و BETWEEN

آخرین دستوراتی که در این آموزش قصد بررسی آنها را داریم IN و BETWEEN هستند، IN وجود مقدار ستون مد نظر در لیست مقادیر یا نتیجه یک پرس و جوی مستقل زیرمجموعه (Subquery) دیگر را بررسی می کند، BETWEEN نیز برای بررسی قرار داشتن مقدار ستون مورد نظر در یک بازه (Range) خاص (شامل اعداد و تاریخ) استفاده می شود.
به فرض اگر بخواهیم از نمونه جداولی که در بالا ایجاد کرده ایم صرفا ردیف هایی از جدول tbl_1_name استخراج شوند که مقادیر ستون name آنها در جدول tbl_2_name وجود داشته باشد می توانیم پرس و جوی متغیر sql را به شکل زیر بنویسیم:

SELECT name, age, point FROM tbl_1_name WHERE name IN (SELECT name FROM tbl_2_name)

خروجی نمونه پرس و جوی بالا به شکل زیر خواهد بود:

Row Count: 2
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: C - Age: 15 - Point: 33

از دستور IN برای جستجو در لیست مقادیر هم می توانیم استفاده کنیم، مثال:

SELECT name, age, point FROM tbl_1_name WHERE name IN ('A', 'D', 'F')

خروجی این حالت به شکل زیر خواهد بود:

Row Count: 3
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: D - Age: 23 - Point: 12
Row 3 => Name: F - Age: 16 - Point: 35

دستور IN در حالت لیست معادل کاربرد چند OR در پرس و جو است.
شکل منفی دستور IN با افزودن NOT قابل تعریف است، در این حالت ردیف هایی انتخاب می شوند که در لیست IN وجود نداشته باشند، مثال:

SELECT name, age, point FROM tbl_1_name WHERE name NOT IN (SELECT name FROM tbl_2_name)

خروجی این پرس و جو به شکل زیر خواهد بود:

Row Count: 4
Row 1 => Name: B - Age: 13 - Point: 15
Row 2 => Name: D - Age: 23 - Point: 12
Row 3 => Name: E - Age: 12 - Point: 13
Row 4 => Name: F - Age: 16 - Point: 35

همان طور که مشخص است ردیف هایی از جدول tbl_1_name استخراج شده اند که مقادیر ستون name آنها در جدول tbl_2_name وجود ندارد.
دستور BETWEEN نیز برای محدود کردن نتایج مورد نظر به بازه خاصی کاربرد دارد، این بازه با توجه به نوع داده ستون مورد نظر می تواند عدد یا تاریخ باشد، به فرض اگر بخواهیم از جدول tbl_1_name صرفا ردیف هایی را استخراج کنیم که مقادیر point آنها بین 13 و 33 باشد قسمت sql را به شکل زیر می نویسیم:

SELECT name, age, point FROM tbl_1_name WHERE point BETWEEN 13 AND 33

خروجی پرس و جوی بالا به شکل زیر خواهد بود:

Row Count: 3
Row 1 => Name: B - Age: 13 - Point: 15
Row 2 => Name: C - Age: 15 - Point: 33
Row 3 => Name: E - Age: 12 - Point: 13

در حالت مثبت BETWEEN اعداد 13 و 33 راهم شامل می شود (در اصطلاح Inclusive) و معادل بزرگتر مساوی و کوچکتر مساوی (<= و >=) است، اما در حالت منفی با NOT اعداد 13 و 33 شامل نمی شوند، مثال:

SELECT name, age, point FROM tbl_1_name WHERE point NOT BETWEEN 13 AND 33

که خروجی این پرس و جو برابر است با:

Row Count: 3
Row 1 => Name: A - Age: 20 - Point: 12
Row 2 => Name: D - Age: 23 - Point: 10
Row 3 => Name: F - Age: 16 - Point: 35

مقایسه BETWEEN در حالت منفی بر مبنای بزرگتر و کوچکتر (< و >) است.
دستور BETWEEN برای استخراج نتایج در بازه زمانی نیز کاربرد دارد، در این حالت به جای دو عدد دو تاریخ با فرمت میلادی (yyyy-mm-dd) یا به صورت برچسب زمان (Unix Timestamp) به عنوان نقطه شروع و پایان بازه جستجو در جدول در نظر می گیریم، مثال:

SELECT col_name FROM tbl_name WHERE col_date BETWEEN '2020-01-02' AND '2020-03-18';

در حالت عادی رفتار BETWEEN به صورت Inclusive است یعنی خود تاریخ شروع و پایان را نیز شامل می شود اما اگر تاریخ ها به همراه زمان یا با نوع داده نامعتبری باشند ممکن است برخی ردیف ها در لیست نتایج انتخاب نشوند، برای دریافت تمام ردیف ها شامل روز شروع و پایان و جلوگیری از مشکلات پیش بینی نشده می توانیم با تابع DATE یا CAST به صورت نمونه های زیر ورودی ها یا مقدار ستون مورد نظر را به فرمت استاندارد تبدیل کنیم:

SELECT col_name FROM tbl_name WHERE col_date BETWEEN CAST('2014-02-01 21:13:16' AS DATE) AND CAST('2014-02-28 06:11:01' AS DATE);

SELECT col_name FROM tbl_name WHERE DATE(col_date) BETWEEN '2014-02-01' AND '2014-02-28';

در صورت کار با تاریخ و زمان به صورت برچسب زمان (Unix Timestamp) تابع UNIX_TIMESTAMP برای تبدیل تاریخ عادی به برچسب زمان و تابع FROM_UNIXTIME برای تبدیل برچسب زمان به تاریخ عادی در پرس و جوهای MySQL کاربرد خواهند داشت، مثال:

SELECT col_name FROM tbl_name WHERE col_date BETWEEN UNIX_TIMESTAMP('2014-02-01 00:00:00') AND UNIX_TIMESTAMP('2014-02-28 00:00:00');

SELECT col_name FROM tbl_name WHERE FROM_UNIXTIME(col_date) BETWEEN '2014-02-01 00:00:00' AND '2014-02-28 00:00:00';