دستورات و توابع متفرقه ی SQL

با سلامی گرم خدمت شما همراهان همیشگی روکسو، امروز می خواهیم در مورد دستورات و توابعی صحبت کنیم که شاید استفاده ی روزانه نداشته باشند و تنها در مواقع خاصی از آن ها استفاده کنیم. به طور مثال دستوراتی مانند INSERT INTO SELECT و یا توابعی مانند ()IFNULL.

دستور INSERT INTO SELECT

این دستور داده ها را از یک جدول کپی کرده و داخل جدول دیگری قرار می دهد. تفاوت آن با SELECT INTO این است که:

برای INSERT INTO SELECT یکی بودن نوع داده ها در دو جدول الزامی است.
داده هایی که از قبل در یک جدول وجود دارند دست نخورده باقی می مانند.

ساختار کلی این دستور به شکل زیر است.

اگر می خواهید تمام ستون ها را از یک جدول وارد جدول دیگر کنید:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

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

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

ما می خواهیم مثال هایمان را روی قسمت هایی از دو جدول Customers و Suppliers پیاده کنیم بنابراین بیایید به آن ها نگاهی بیندازیم.

جدول Customers:

Country	PostalCode	City	Address	ContactName	CustomerName	CustomerID
Germany	12209	Berlin	Obere Str. 57	Maria Anders	Alfreds Futterkiste	1
Mexico	05021	México D.F.	Avda. de la Constitución 2222	Ana Trujillo	Ana Trujillo Emparedados y helados	2
Mexico	05023	México D.F.	Mataderos 2312	Antonio Moreno	Antonio Moreno Taquería	3

جدول Suppliers:

Country	PostalCode	City	Address	ContactName	SupplierName	SupplierID
UK	EC1 4SD	London	49 Gilbert St.	Charlotte Cooper	Exotic Liquid	1
USA	70117	New Orleans	P.O. Box 78934	Shelley Burke	New Orleans Cajun Delights	2
USA	48104	Ann Arbor	707 Oxford Rd.	Regina Murphy	Grandma Kelly's Homestead	3

مثال های کار با INSERT INTO SELECT

مثال اول - دستور SQL زیر جدول Suppliers را در Customers ادغام می کند. ردیف هایی که خالی از داده باشند مقدار NULL میگیرند:

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

اجرای این دستور در پایگاه داده ی Northwind

مثال دوم- دستور SQL زیر جدول Suppliers را در Customers ادغام می کند. تفاوت این دستور با دستور قبلی آنجاست که در این مثال تمام ستون ها پر می شوند:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

اجرای این دستور در پایگاه داده ی Northwind

مثال سوم - دستور SQL زیر تنها تامین کننده (suppliers) هایی را به Customers می فرستد که آلمانی باشند:

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

اجرای این دستور در پایگاه داده ی Northwind

دستور CASE

نحوه ی کار دستور CASE بسیار ساده است. دستور CASE شرط های مختلف را بررسی می کند و زمانی که اولین شرط صحیح را پیدا کند، مقداری را بر می گرداند. زمانی که شرط صحیح را پیدا کند، دیگر کد را ادامه نمی دهد و برایش مهم نیست که ممکن است شروط صحیح دیگری نیز در ادامه وجود داشته باشند. اگر هیچ شرطی صحیح نباشد (صحیح بودن به معنای برقرار بودن شرط یا همان true بودن است) مقدار ELSE را بر می گرداند و اگر ELSE را تعریف نکرده باشیم نیز مقدار NULL را بر می گرداند.

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

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

بیایید نگاهی به قسمتی از جدول OrderDetails بندازیم تا مثال های خود را روی آن پردازش کنیم:

Quantity	ProductID	OrderID	OrderDetailID
12	11	10248	1
10	42	10248	2
5	72	10248	3
9	14	10249	4
40	51	10249	5

مثال های کار با CASE

مثال اول - ما در دستور SQL زیر چندین شرط مختلف تعریف کرده ایم و آن ها را به دستور CASE داده ایم. دستور CASE نیز شروع به بررسی آن ها می کند و مقدار اولین شرطی را که صحیح باشد برمیگرداند:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

اجرای این دستور در پایگاه داده ی Northwind

مثال دوم - دستور SQL زیر مشتریان (customer) را بر اساس شهر هایشان مرتب می کند و اگر مقدار شهر برای هر کدام از آن ها NULL بود، آن ها را بر اساس کشور مرتب می کند:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

اجرای این دستور در پایگاه داده ی Northwind

نکته: حتما از قسمت های قبلی به یاد دارید که این مرتب کردن بر اساس حروف الفبای انگلیسی است و در غیر اینصورت بی معنی خواهد بود.

توابع متفرقه SQL

در این قسمت با توابع ()IFNULL و ()ISNULL و ()COALESCE و ()NVL آشنا می شویم.

به جدول زیر به نام Products نگاهی بیندازید:

UnitsOnOrder	UnitsInStock	UnitPrice	ProductName	P_Id
15	16	10.45	Jarlsberg	1
	23	32.56	Mascarpone	2
20	9	15.67	Gorgonzola	3

فرض ما این است که ستون UnitsOnOrder الزامی نیست و می تواند خالی بماند (مقدار NULL بگیرد). حالا به دستور زیر نگاه کنید:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

در این دستور اگر هر کدام از خانه های UnitsOnOrder خالی باشند (مقدار NULL)، نتیجه ی کوئری نیز مقدار NULL خواهد بود. راه حل چیست؟

راه حل در MYSQL

در MYSQL باید از ()IFNULL استفاده کنید. این دستور به شما اجازه می دهد که در صورت مواجهه با مقادیر NULL، مقدار جایگزینی را برگردانید:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products

راه حل دیگر برای MYSQL استفاده از ()COALESCE است:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products

تابع ()IFNULL در صورت برخورد با مقدار NULL، مقدار جایگزینی را برمیگرداند. تابع ()COALESCE در صورت برخورد با مقدار NULL از لیستی که به آن داده شده است اولین مقدار غیر NULL را بر میگرداند اما در این مثال عملا هر دو روش یک کار را انجام می دهند.

راه حل در SQL Server

استفاده از تابع ()ISNULL که دقیقا مشابه ()IFNULL عمل می کند:

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products

راه حل در MS Access

از تابع ()IsNull استفاده کنید؛ اگر شرط ما NULL باشد مقدار TRUE (همان 1-) و اگر غیر آن باشد FALSE (یا 0) را برمیگرداند:

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products

راه حل در Oracle

از تابع ()NVL استفاده کنید تا دقیقا همان نتایج قبلی را به دست بیاورید:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products

امیدوارم از این قسمت لذت برده باشید. قسمت بعد در رابطه با stored procedure ها خواهد بود و بعد از آن به سراغ مباحث دستورات SQL برای ساخت یک پایگاه داده می رویم.