تستعرض هذه المقالة عددًا من أنواع الدوال، مثل الدوال التجميعية (Aggregate Functions) والدوال التحليلية (Analytic Functions) والدوال العددية.

الدوال التجميعية aggregate functions

تستعرض هذه الفقرة مجموعة من الدوال التجميعية المُستخدمة في SQL، وهي دوال تأخذ مجموعة من القيم، وتعيد قيمة واحدة.

التجميع الشرطي Conditional aggregation

إليك جدول المدفوعات التالي:

تحسب الشيفرة التالية المجموع الكلي لرصيد أو دين كل موظف في الجدول:

select customer,
     sum(case when payment_type = 'credit' then amount else 0 end) as credit,
     sum(case when payment_type = 'debit' then amount else 0 end) as debit
from payments
group by customer

سنحصل على النتيجة التالية:

إليك الآن المثال التالي:

select customer,
     sum(case when payment_type = 'credit' then 1 else 0 end) as credit_transaction_count,
     sum(case when payment_type = 'debit' then 1 else 0 end) as debit_transaction_count
from payments
group by customer

هذا هو الخرج الناتج:

دورة علوم الحاسوب

دورة تدريبية متكاملة تضعك على بوابة الاحتراف في تعلم أساسيات البرمجة وعلوم الحاسوب

اشترك الآن

ضمّ القوائم List Concatenation

تجمّع عملية ضمّ القوائم (List Concatenation) عناصر عمود أو تعبيرًا عن طريق دمج القيم في سلسلة نصية واحدة لكل مجموعة. يمكن أيضًا تحديد سلسلة نصية لفصل القيم (إما سلسلة نصية فارغة أو فاصلة عند حذفها)، كما يمكن تحديد ترتيب القيم المُعادة. ورغم أنّها ليست جزءًا من معيار SQL القياسي، إلا أنّ كلّ أنظمة قواعد البيانات العلائقية تدعمها.

• MySQL

SELECT ColumnA
   , GROUP_CONCAT(ColumnB ORDER BY ColumnB SEPARATOR ',') AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• Oracle و DB2

SELECT ColumnA
   , LISTAGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• PostgreSQL

SELECT ColumnA
   , STRING_AGG(ColumnB, ',' ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• SQL Server قبل 2016

 WITH CTE_TableName AS (
   SELECT ColumnA, ColumnB
       FROM TableName)
SELECT t0.ColumnA
 ,   STUFF((
     SELECT ',' + t1.ColumnB
     FROM CTE_TableName t1
     WHERE t1.ColumnA = t0.ColumnA
     ORDER BY t1.ColumnB
         FOR XML PATH('')), 1, 1, '') AS ColumnBs
   FROM CTE_TableName t0
 GROUP BY t0.ColumnA
 ORDER BY ColumnA;

• SQL Server 2017 و SQL Azure

SELECT ColumnA
 ,   STRING_AGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• SQLite بدون ترتيب:

SELECT ColumnA
   , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

يتطلب الترتيب استخدام استعلامً فرعي (subquery)، أو تعبيرًا جدوليًا CTE، وهو مجموعة ننائج مؤقتة يمكنك الرجوع إليها داخل عبارات SELECT أو INSERT أو UPDATE أو DELETE الأخرى:

 WITH CTE_TableName AS (
    SELECT ColumnA, ColumnB
       FROM TableName
       ORDER BY ColumnA, ColumnB)
SELECT ColumnA
       , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
    FROM CTE_TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

SUM

تجمع الدالة ‎Sum‎ قيم صفوف مجموعة النتائج. وفي حال حذف العبارة group by، فستُجمَع قيم كلّ الصفوف.

المثال التالي لا يستخدم العبارة group by:

select sum(salary) TotalSalary
from employees;

سنحصل على الخرج التالي:

إليك مثال يستخدم group by:

select DepartmentId, sum(salary) TotalSalary
from employees
group by DepartmentId;

الخرج الناتج:

المتوسط AVG

تعيد الدالة التجميعية ‎‎AVG()‎‎ متوسط قيم تعبير معيّن، والتي عادةً ما تكون قيمًا رقمية في عمود.

لنفترض أنّ لدينا جدولًا يحتوي على تعداد سكان مدن العالم. مثلا، سجلّ مدينة نيويورك سيكون من هذا القبيل:

يحسب الاستعلام التالي متوسط عدد سكان مدينة نيويورك في الولايات المتحدة الأمريكية في السنوات العشر الماضية:

select city_name, AVG(population) avg_population
from city_population
where city_name = 'NEW YORK CITY';

لاحظ كيف لم توضع السنة في الاستعلام، وذلك لأنّنا نريد حساب متوسط عدد السكان بمرور الوقت.

سنحصل على النتائج التالية:

تنبيه: تحوّل الدالة AVG القيم إلى أعداد، وهذا أمر ينبغي أن تأخذه بالحسبان دائمًا، خصوصا عندما تعمل بقيم التاريخ والوقت.

Count

يمكنك استخدام الدالة Count لحساب عدد الصفوف:

SELECT count(*) TotalRows
FROM employees;

النتيجة:

يعدّ المثال التالي الموظفين في كل قسم:

SELECT DepartmentId, count(*) NumEmployees
FROM employees
GROUP BY DepartmentId;

الخرج الناتج:

يمكنك العدّ بحسب الأعمدة أو التعابير مع عدم احتساب القيم المعدومة ‎NULL‎:

SELECT count(ManagerId) mgr
FROM EMPLOYEES;

النتيجة:

(هناك قيمة واحدة فقط معدومة في العمود managerID)

يمكنك أيضًا استخدام DISTINCT داخل دالة أخرى (مثل COUNT) لتجبنّب إعادة العناصر المكرّرة على النحو التالي:

 SELECT COUNT(ContinentCode) AllCount
  ,         COUNT(DISTINCT ContinentCode) SingleCount
 FROM Countries;

ستعيد الشيفرة أعلاه قيمًا مختلفة. إذ لن تحسب SingleCount إلا عدد القارّات الفريدة (أي غير المكررة)، وذلك على خلاف AllCount التي ستعيد التكرارات أيضًا.

إذا طبّقنا الشيفرة أعلاه على جدول القارات التالي:

فسنحصل على الخرج التالي:

AllCount: 7 SingleCount: 5 

القيمة الدنيا Min

تبحث الدالة Min عن أصغر قيمة في العمود:

select min(age) from employee;

سيعيد المثال أعلاه أصغر قيمة في العمود ‎age‎ من جدول ‎employee‎.

القيمة القصوى Max

تبحث الدالة Max عن القيمة القصوى في العمود:

select max(age) from employee;

سيعيد المثال أعلاه أكبر قيمة في العمود ‎age‎ من جدول ‎employee‎.

الدوال العددية والصفّية Scalar/Single Row Functions

توفّر SQL العديد من الدوال العددية (scalar functions) المُضمّنة. والتي تأخذ قيمة واحدة كمُدخل، وتعيد قيمة واحدة لكل صفّ في مجموعة النتائج.

يمكنك استخدام الدوال العددية في أيّ موضع تكون التعابير جائزة فيه داخل ‏‏‏‏عبارات T-SQL .

التاريخ والوقت

في SQL، يُستخدم النوعان date و time لتخزين المعلومات المتعلقة بالوقت. يتضمّن هذان النوعان الوقت (time) والتاريخ (date) والتوقيت الصغير (smalldatetime) والتوقيت (datetime) والتوقيت 2 - مبني على 24 ساعة - (datetime2) والتوقيت الإزاحي - أي فارق التوقيت مع التوقيت العالمي الموحد UTC‏ - (datetimeoffset).

لكل واحد من هذه الأنواع تنسيق خاص كما يوضّح الجدول التالي:

تعيد الدالة ‎DATENAME‎ اسم أو جزء محدّد من قيمة التاريخ.

SELECT DATENAME (weekday,'2017-01-14') as Datename

الخرج الناتج عن الشيفرة أعلاه:

يمكنك استخدام الدالة ‎GETDATE‎ لتحديد التاريخ والوقت الحاليين لجهاز الكمبيوتر الذي ينفّذ شيفرة SQL الحالية كما هو موضّح في المثال التالي (لا تشمل هذه الدالة اختلاف المنطقة الزمنية.)

SELECT GETDATE() as Systemdate

الخرج الناتج:

تعيد الدالة ‎DATEDIFF‎ الفرق بين تاريخين. ويحدد المعامل الأوّل الممرّر إلى هذه الدالة الجزء الذي تريد استخدامه من التاريخ لحساب الاختلاف. يمكن أن يساوي: year أو month أو week أو day أو hour أو minute أو second أو millisecond. يحدّد المعامل الثاني والثالث تاريخ البداية وتاريخ الانتهاء اللذين تريد حساب الفرق الزمني بينها على التوالي.

إليك المثال التالي:

SELECT SalesOrderID, DATEDIFF(day, OrderDate, ShipDate)
AS 'Processing time'
FROM Sales.SalesOrderHeader

الخرج الناتج:

تتيح لك الدالة ‎DATEADD‎ إضافة مجال زمني إلى جزء محدّد من التاريخ كما يوضّح المثال التالي:

SELECT DATEADD (day, 20, '2017-01-14') AS Added20MoreDays

الخرج الناتج:

التعديلات على الحروف Character modifications

توفّر SQL بعض الدوال التي يمكنها معالجة الأحرفِ، مثلا، يمكن تحويل الأحرف إلى أحرف كبيرة أو صغيرة، أو تحويل الأرقام إلى أرقام منسّقة تنسيقًا خاصًّا.

تحوّل الدالة ‎lower(char)‎ الأحرف المُمرّرة إليها إلى أحرف صغيرة.

SELECT customer_id, lower(customer_last_name) FROM customer;

يعيد الاستعلام أعلاه الاسم الأخير صغيرًا، أي يحوّل SMITH إلى smith.

دوال الإعدادات والتحويل

الدالة ‎@@SERVERNAME‎ هي إحدى أمثلة دوال الإعدادات في SQL. توفّر هذه الدالة اسم الخادم المحلي الذي ينفّذ تعليمات SQL.

SELECT @@SERVERNAME AS 'Server'

الناتج:

في SQL، تحدث معظم عمليات تحويلات البيانات ضمنيًا، ودون أيّ تدخل من المستخدم. إن أردت تنفيذ عملية تحويل لا يمكن إجراؤها ضمنيًا، فيمكنك استخدام الدالتين ‎CAST‎ أو ‎CONVERT‎.

صياغة ‎CAST‎ أبسط من صياغة ‎CONVERT‎، بيْد أنّ إمكانياتها محدودة. سنستخدم في المثال التالي كلا الدالتين ‎CAST‎ و ‎CONVERT‎ لتحويل نوع بيانات الوقت (datetime) إلى النوع ‎varchar‎. تستخدم الدالة ‎CAST‎ دائمًا التنسيق الافتراضي. على سبيل المثال، تُمثّل التواريخ والأوقات بالتنسيق YYYY-MM-DD. بالمقابل، تستخدم الدالة ‎CONVERT‎ تنسيق التاريخ والوقت الذي تحدّده أنت. سنختار في المثال التالي التنسيق 3، والذي يمثّل التنسيق dd / mm / yy.

USE AdventureWorks2012
GO
SELECT FirstName + ' ' + LastName + ' was hired on ' +
     CAST(HireDate AS varchar(20)) AS 'Cast',
     FirstName + ' ' + LastName + ' was hired on ' +
     CONVERT(varchar, HireDate, 3) AS 'Convert'
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
GO

ستحصل على الخرج التالي:

هناك مثال آخر على دوال التحويل، وهي الدالة ‎PARSE‎. تحوّل هذه الدالة سلسلة نصية إلى نوع بيانات آخر.

في صياغة الدالة، عليك تحديد السلسلة النصية التي ترغب في تحويلها متبوعة بالكلمة المفتاحية ‎AS‎، ثمّ تكتب نوع البيانات المطلوب. اختياريًا، يمكنك أيضًا تحديد الإعداد الثقافي، والذي يحدّد تنسيق السلسلة النصية. في حال لم تحدّده، فستُستخدم لغة الجلسة.

إذا تعذّر تحويل السلسلة النصية إلى تنسيق عددي أو تاريخ أو وقت ، فسيُطرَح خطأ. وسيتعيّن عليك حينئِذ استخدام ‎CAST‎ أو ‎CONVERT‎ لإجراء عملية التحويل.

SELECT PARSE('Monday, 13 August 2012' AS datetime2 USING 'en-US') AS 'Date in English'

الخرج التالي:

الدوال المنطقية والرياضية

تقدّم SQL دالتين منطقيتين، وهما CHOOSE و IIF. تعيد الدالة ‎CHOOSE‎ عنصرًا من قائمة من القيم استنادًا إلى فهرسه في القائمة.

ينبغي أن يكون المعامل الأول، الذي يمثل الفهرس، عددًا صحيحًا. المعاملات التالية تحدّد قيم القائمة.

في المثال التالي، سنستخدم الدالة ‎CHOOSE‎ لإعادة المُدخَل الثاني في قائمة الإدارات.

SELECT CHOOSE(2, 'Human Resources', 'Sales', 'Admin', 'Marketing' ) AS Result;

النتيجة:

تعيد الدالة ‎IIF‎ القيمة true إن تحقّق شرطها، خلاف ذلك، تُعيد القيمة false.

في صياغة عبارة الشرط، يحدّد معامل التعبير الشرطي (booleanexpression) التعبير المنطقي. فيما يحدّد المعامل الثاني (truevalue) القيمة التي يجب إعادتها إذا لم يتحقّق الشرط، ويحدّد المعامل الثالث (false_value) القيمة التي يجب أن تُعاد خلاف ذلك.

يستخدم المثال التالي الدالة IIF لإعادة إحدى قيمتين. إذا كانت مبيعات الموظف السنوية تتجاوز 200000، فسيكون ذلك الموظف مؤهّلاً للحصول على مكافأة. خلاف ذلك لن يكون مؤهّلا للحصول على مكافأة.

SELECT BusinessEntityID, SalesYTD,
     IIF(SalesYTD > 200000, 'Bonus', 'No Bonus') AS 'Bonus?'
FROM Sales.SalesPerson
GO

هذا هو الناتج:

تتضمّن SQL العديد من الدوال الرياضية التي يمكنك استخدامها لإجراء عمليات حسابية على المُدخلات ثمّ إعادة نتائج عددية.

أحد أمثلة ذلك هي الدالة ‎SIGN‎، والتي تُعيد قيمة تمثّل إشارة التعبير. إذ تشير القيمة ‎‎-1 إلى تعبير سلبي، فيما تشير القيمة ‎‎+1 إلى تعبير موجب ، أمّا 0 فيشير إلى الصفر!

في المثال التالي، القيمة المُدخلة هي عدد سالب، لذا تُعاد ‎‎النتيجة ‎‎-1.

SELECT SIGN(-20) AS 'Sign'

الناتج:

هناك دالة رياضية أخرى، وهي الدالة ‎POWER‎. والتي تحسب أسّ تعبير مرفوع إلى قوة محددة.

في صياغة الدالة، يحدّد المعامل الأول التعبير العددي، فيما يحدّد المعامل الثاني الأسّ.

SELECT POWER(50, 3) AS Result

النتيجة:

الدوال التحليلية

تُستخدم الدوال التحليلية لحساب قيمة معيّنة بناءً على مجموعة من القيم. على سبيل المثال، يمكنك استخدام الدوال التحليلية لحساب المجاميع الجارية (running totals)، أو النسب المئوية، أو النتيجة الأكبر داخل مجموعة.

LAG و LEAD

توفر الدالة ‎LAG‎ البيانات الخاصّة بالصفوف التي تسبق الصف الحالي في مجموعة النتائج. على سبيل المثال ، في عبارة ‎SELECT‎، يمكنك موازنة قيم الصف الحالي مع قيم الصف السابق. يمكنك استخدام تعبير عددي لتحديد القيم التي يجب موازنتها.

يمثّل معامل الإزاحة (offset) عدد الصفوف السابقة للصف الحالي التي ستُستخدم في المقارنة. في حال عدم تحديده، فستُستخدم القيمة الافتراضية 1.

يحدّد المعامل الافتراضي default القيمة التي يجب إعادتها عندما يكون التعبير الموجود في الموضع offset معدومًا (‎NULL‎). إذا لم تحدّد قيمة لهذا المعامل، فستُستخدم القيمة الافتراضية ‎NULL‎.

توفّر الدالة ‎LEAD‎ بيانات عن الصفوف التي تعقُب الصفّ الحالي في مجموعة الصفوف. على سبيل المثال، في عبارة ‎SELECT‎، يمكنك موازنة قيم الصف الحالي مع قيم الصف اللاحق. يمكن تحديد القيم التي يجب موازنتها باستخدام تعبير رقمي.

يمثّل معامل الإزاحة (offset) عدد الصفوف اللاحقة للصف الحالي التي ستُستخدم في المقارنة. يحدد المعامل default القيمة التي ينبغي أن تُعاد عندما يكون التعبير الموجود عند موضع الإزاحة معدومًا (‎NULL‎). إذا لم تحدد هذين المعاملين، فستُستخدم القيمتان الافتراضيتان لهذين المعاملين، واللتان تساويان 1 و ‎NULL‎ على التوالي.

يستخدم المثال التالي الدالتين LEAD و LAG لمقارنة قيم المبيعات الحالية لكل موظف مع قيم الموظفين المذكورين قبله وبعده، مع ترتيب السجلات بناءً على قيمة العمود BusinessEntityID.

SELECT BusinessEntityID, SalesYTD,
LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

الخرج الناتج:

PERCENTILEDISC و PERCENTILECONT

تسرد الدالة ‎PERCENTILE_DISC‎ قيمة أوّل مُدخَل يكون التوزيع التراكمي (cumulative distribution) عنده أعلى من المئين الذي قدّمته باستخدام المعامل ‎numeric_literal‎.

تُجمَّع القيم حسب مجموعة الصفوف (rowset) أو حسب التوزيع (partition) كما هو محدّد في عبارة ‎WITHIN GROUP‎.

تشبه ‎PERCENTILE_CONT‎ الدالة ‎PERCENTILE_DISC‎، بيْد أنّها تُعيد متوسّط مجموع أول مُدخل يحقق الشرط مع المُدخل التالي.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
    CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
    AS "Cumulative Distribution",
    PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
       OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

لإيجاد القيمة التي تطابق أو تتجاوز المئين 0.5، عليك تمرير المئين كقيمة عددية حرفية (numeric literal) إلى دالة المئين الكسري ‎PERCENTILE_DISC‎. ينتج عن تطبيق هذه الدالة على مجموعة النتائج قائمة مؤلفة من قيم الصف التي يكون التوزيع التراكمي عندها أعلى من المئين المحدّد.

يمكنك أيضًا استخدام دالة المئين المتصل - Percentile Continuous‏ - ‎PERCENTILE_CONT‎، والتي ينتج عن تطبيقها على مجموعة النتائج متوسط مجموع قيمة النتيجة مع أعلى قيمة موالية تحقق الشرط.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
    CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
    AS "Cumulative Distribution",
    PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
       OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
       OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;

الخرج الناتج:

FIRST_VALUE

يمكنك استخدام الدالة ‎FIRST_VALUE‎ لتحديد القيمة الأولى في مجموعة نتائج مرتّبة:

SELECT StateProvinceID, Name, TaxRate,
     FIRST_VALUE(StateProvinceID)
     OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

في هذا المثال، تُستخدم الدالة ‎FIRST_VALUE‎ لإعادة قيمة الحقل ‎ID‎ الخاص بالولاية أو المقاطعة التي لها أدنى معدّل للضريبة. فيما تُستخدم العبارة ‎OVER‎ لترتيب معدّلات الضريبة للحصول على أدنى معدّل.

إليك جدول الضرائب:

LAST_VALUE

تعيد الدالة ‎LAST_VALUE‎ القيمة الأخيرة في مجموعة نتائج مرتبة.

SELECT TerritoryID, StartDate, BusinessentityID,
    LAST_VALUE(BusinessentityID) 
    OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

يستخدم المثال أعلاه الدالة ‎LAST_VALUE‎ لإعادة القيمة الأخيرة لكل مجموعة من الصفوف في مجموعة القيم المُرتبة.

PERCENTRANK و CUMEDIST

تحسب الدالة ‎PERCENT_RANK‎ ترتيب الصفّ بالنسبة لمجموعة الصفوف. تُحسب النسبة المئوية نسبةً إلى عدد الصفوف في المجموعة التي تقلّ قيمتها عن الصف الحالي.

تُعطى للقيمة الأولى في مجموعة النتائج دائمًا النسبة المئوية 0. بالمقابل، فالنسبة المئوية للقيمة العليا - أو الأخيرة - في المجموعة تساوي دائمًا 1.

تحسب الدالة ‎CUME_DIST‎ الموضع النسبي (relative position) لقيمة معيَّنة في مجموعة من القيم من خلال تحديد النسبة المئوية للقيم التي تصغُر أو تساوي تلك القيمة. تُسمّى هذه العملية التوزيع التراكمي (cumulative distribution).

سنستخدم في هذا المثال عبارة ‎ORDER‎ لتقسيم - أو تصنيف - الصفوف التي أعَادتها العبارة ‎SELECT‎ بناءً على المسمّيات الوظيفية للموظّفين، مع ترتيب النتائج في كل مجموعة على أساس عدد ساعات الإجازات المرضية التي استخدمها الموظفون.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
     AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
     AS "Cumulative Distribution"
FROM Employee;

الخرج الناتج:

ترتّب الدالة ‎PERCENT_RANK‎ المُدخلات في كل مجموعة. فمقابل كل مُدخل، تحسب النسبة المئوية للمدخلات الأخرى في المجموعة التي لها قيم أصغر من المُدخل الممرّر.

الدالة ‎CUME_DIST‎ مشابهة للدالة السايقة، بيْد أنّها تُعيد النسبة المئوية للقيم التي تصغُر القيمة الحالية أو تساويها.

دوال النافذة Window Functions

التحقق من وجود قيم مكررة في عمود

لنفترض أن لدينا جدول البيانات التالي:

يعيد المثال التالي كل هذه الصفوف مع راية تحدّد ما إذا كان الوسم tag مُستخدمًا من قبل صفّ آخر.

SELECT id, name, tag, COUNT(*) OVER (PARTITION BY tag) > 1 AS flag FROM items

سنحصل على الخرج التالي:

في حالة لم تكن قاعدة بياناتك تدعم OVER و PARTITION، فيمكنك استخدام الشيفرة التالية للحصول على النتيجة نفسها:

SELECT id, name, tag, (SELECT COUNT(tag) FROM items B WHERE tag = A.tag) > 1 AS flag FROM items A

إيجاد السجلات الخارجة عن التسلسل باستخدام الدالة LAG

إليك الجدول التالي:

يجب أن تُرتب العناصر بحسب قيمة الحقل ‎STATUS‎، بداية من القيمة "ONE" ثمّ "TWO" ثمّ "THREE".

لاحظ أنّ التسلسل في الجدول غير مرتب، إذ أنّ هناك انتقالًا فوريًا من "ONE" إلى "THREE". عليك إيجاد طريقة للعثور على المستخدمين (‎STATUS_BY‎) الخارجين عن الترتيب.

تساعد الدالة التحليلية ‎LAG()‎ في حل هذه المشكلة، إذ تعيد لكل صفّ، قيمة الصف السابق له:

SELECT * FROM (
 SELECT
  t.*,
 LAG(status) OVER (PARTITION BY id ORDER BY status_time) AS prev_status
 FROM test t
) t1 WHERE status = 'THREE' AND prev_status != 'TWO'

في حالة لم تكن قاعدة بياناتك تدعم LAG، يمكنك استخدام الشيفرة التالية للحصول على النتيجة نفسها:

SELECT A.id, A.status, B.status as prev_status, A.status_time, B.status_time as prev_status_time
FROM Data A, Data B
WHERE A.id = B.id
AND   B.status_time = (SELECT MAX(status_time) FROM Data where status_time < A.status_time and id =
A.id)
AND   A.status = 'THREE' AND NOT B.status = 'TWO'

حساب المجموع الجاري running total

إليك جدول البيانات التالي:

بحسب المثال التالي المجموع الجاري للعمود amount في الجدول أعلاه:

SELECT date, amount, SUM(amount) OVER (ORDER BY date ASC) AS running
FROM operations
ORDER BY date ASC

الخرج الناتج:

إضافة إجمالي الصفوف المُختارة لكل صف

يضيف المثال التالي إجمالي الصفوف المختارة لكل صف:

SELECT your_columns, COUNT(*) OVER() as Ttl_Rows FROM your_data_set

بدلاً من استخدام استعلامين، الأول للحصول على المجموع، والثاني للحصول على الصفّ، يمكنك استخدام التجميع - aggregate - كدالة نافذة (window function) واستخدام مجموعة النتائج الكاملة كنافذة (window). يمكن أن يجنّبك هذا تعقيدات عمليات الضمّ الذاتي (self joins) الإضافية.

الحصول على أحدث N صفًّا في عدة مجموعات

إليك البيانات التالية:

إن استخدمت القيمة n = 1 في المثال التالي، ستحصل على أحدث صفّ لكل معرِّف ‎user_id‎:

;with CTE as
(SELECT *,
 ROW_NUMBER() OVER (PARTITION BY User_ID
 ORDER BY Completion_Date DESC) Row_Num
FROM Data)
SELECT * FORM CTE WHERE Row_Num <= n

الخرج سيكون:

ترجمة -وبتصرّف- للفصول من 42 إلى 45 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: دوال التعامل مع النصوص في SQL
• المقال السابق: مواضيع متقدمة في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0

ستعرض هذه المقالة عددًا من المواضيع المتقدمة في SQL، مثل إدارة الصلاحيات، واستخدام ملفات XML في الاستعلامات، والمفاتيح الرئيسية والفهارس وأرقام الصفوف.

GRANT و REVOKE

تُستخدم العبارة GRANT لمنح الإذن لمستخدم ما بإجراء عملية على قاعدة البيانات، فيما تُستخدم العبارة REVOKE لسحب الإذن أو الصلاحية منه.

يمنح المثال التالي الإذن للمستخدمَين ‎User1‎ و ‎User2‎ بإجراء العمليتين ‎SELECT‎ و ‎UPDATE‎ على الجدول ‎Employees‎.

GRANT SELECT, UPDATE
ON Employees
TO User1, User2;

يسحب المثال التالي من المستخدمَين ‎User1‎ و ‎User2‎ صلاحية تنفيذ العمليتين ‎SELECT‎ و ‎UPDATE‎ على جدول الموظفين.

REVOKE SELECT, UPDATE
ON Employees
FROM User1, User2;

استخدام ملفات XML في SQL

يمكن تعريف جدول بيانات من ملف XML واستخدامه في استعلامات SQL مثل أيّ جدول عادي:

DECLARE @xmlIN XML = '<TableData>
<aaa Main="First">
    <row name="a" value="1" />
    <row name="b" value="2" />
    <row name="c" value="3" />
</aaa>
<aaa Main="Second">
    <row name="a" value="3" />
    <row name="b" value="4" />
    <row name="c" value="5" />
</aaa>
<aaa Main="Third">
    <row name="a" value="10" />
    <row name="b" value="20" />
    <row name="c" value="30" />
</aaa>
</TableData>'
SELECT t.col.value('../@Main', 'varchar(10)') [Header],
t.col.value('@name', 'VARCHAR(25)') [name], 
t.col.value('@value', 'VARCHAR(25)') [Value]
FROM @xmlIn.nodes('//TableData/aaa/row') AS t (col)

الخرج الناتج:

Header        name      Value
First          a          1
First          b          2
First          c          3
Second         a          3
Second         b          4
Second         c          5
Third          a          10
Third          b          20
Third          c          30

المفاتيح الرئيسية Primary Keys

تُستخدَم المفاتيح الرئيسية لتمييز صفوف جدول من قاعدة بيانات. ولا يُسمح إلا بمفتاح رئيسي واحد في كلّ جدول.

تنشئ الشيفرة التالية جدولًا للموظفين، مع جعل المُعرّف Id مفتاحه الرئيسي:

CREATE TABLE Employees (
    Id int NOT NULL,
    PRIMARY KEY (Id),
    ...
);

يمكن أيضًا تركيب مفتاح من حقل واحد أو أكثر، هذا النوع من المفاتيح يُسمّى المفاتيح المُركّبة، وتُصاغ على النحو التالي:

CREATE TABLE EMPLOYEE (
    e1_id INT,
    e2_id INT,
   PRIMARY KEY (e1_id, e2_id)
)

استخدام الزيادة التلقائية Auto Increment

تسمح العديد من قواعد البيانات بزيادة قيمة المفتاح الرئيسي تلقائيًا عند إضافة مفتاح جديد. يضمن هذا السلوك أن تكون كلّ المفاتيح مختلفة عن بعضها.

إليك الأمثلة التوضيحية التالية:

• MySQL

CREATE TABLE Employees (
    Id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (Id)
);

• PostgreSQL

CREATE TABLE Employees (
    Id SERIAL PRIMARY KEY
);

• SQL Server

CREATE TABLE Employees (
    Id int NOT NULL IDENTITY,
    PRIMARY KEY (Id)
);

• SQLite

CREATE TABLE Employees (
    Id INTEGER PRIMARY KEY 
);

الفهارس Indexes

الفهارس هي بنيات تحتوي على مؤشّرات تشير إلى محتويات جدول مُرتّب ترتيبًا محدّدًا، تُستخدم الفهارس لتحسين أداء الاستعلامات. فهي تشبه فهرس الكتاب، حيث تُفهرَس الصفحات (صفوف الجدول) بأرقام مميّزة تسهّل الوصول إليها.

توجد عدّة أنواع من الفهارس، ويمكنك إنشاؤها على أيّ جدول. يحسّن استخدَام فهارس الأعمدة في عبارات WHERE أو JOIN أو ORDER BY أداء الاستعلامات تحسينًا.

الفهارس المُرتّبة Sorted Index

إذا كانت الفهارس مُرتّبة بنفس الترتيب الذي ستُسترجع به، فلن تجري التعليمة ‎SELECT‎ أيّ ترتيب إضافي أثناء الاسترجاع.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

عند تنفيذ الاستعلام التالي:

SELECT * FROM scoreboard ORDER BY score DESC;

لن يُجري نظام قاعدة البيانات أيّ ترتيب إضافي طالما أنّه سيبحث في الفهرس وفق ذلك الترتيب.

الفهرس الجزئي أو المُصفّى Partial or Filtered Index

تتيح SQL Server و SQLite إنشاء فهارس تحتوي جزءًا من الأعمدة، وكذلك جزءًا من الصفوف.

لنعتبر كمّية متزايدة من الطلبات يساوي الحقل ‎order_state_id‎ الخاص بها القيمة 2 (والتي تمثّل الحالة "مكتملة")، وكمّية أخرى ثابتة من الطلبات يساوي الحقل ‎order_state_id الخاص بها القيمة 1 (والتي تمثّل الحالة "غير مكتملة").

إليك الاستعلام التالي:

SELECT id, comment
     FROM orders
  WHERE order_state_id =  1
     AND product_id = @some_value;

يتيح لك استخدام الفهرسة الجزئية تقييد الفهرس (limit the index)، بحيث لا تُضمَّن إلا الطلبات التي لم تكتمل بعدُ:

CREATE INDEX Started_Orders
 ON orders(product_id)
 WHERE order_state_id = 1;

سيؤدي هذا إلى تقليل كمّية المؤشّرات، ويوفّر مساحة التخزين، ويقلّل من تكلفة تحديث الفهارس.

إنشاء فهرس

تنشئ الشيفرة التالية فهرسًا للعمود EmployeeId في الجدول Cars.

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

يحسّن استخدام الفهرس سرعة الاستعلامات التي تحاول أن ترتّب أو تختار الصفوف وفقًا لقيم EmployeeId، كما في المثال التالي:

SELECT * FROM Cars WHERE EmployeeId = 1

يمكن أن يحتوي الفهرس أكثر من عمود واحد كما في المثال التالي:

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

في هذه الحالة، سيكون الفهرس مفيدًا للاستعلامات التي تحاول أن ترتّب أو تختار السجلات وفقًا لجميع الأعمدة المُضمّنة في حال كانت مجموعة الشروط مُرتّبة بالطريقة نفسها. هذا يعني أنه عند استرداد البيانات، سيكون بمقدورك العثور على الصفوف المُراد إعادتها باستخدام الفهرس بدلًا من البحث في كامل الجدول.

يستخدم المثال التالي الفهرس الثاني:

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

يفقد الفهرس هذه المزايا في حال كان الترتيب مختلفًا كما يبيّن المثال التالي:

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

لم يعد الفهرس مفيدًا الآن، لأنّه يتوجّب على قاعدة البيانات أن تسترجع الفهرس بالكامل، وعبر جميع قيم EmployeeId و CarID بُغية إيجاد العناصر التي تحقق الشرط ‎OwnerId = 17‎.

رغم كل ما قلناه، إلا أنّه من الممكن أن يُستخدم الفهرس رغم كل شيء؛ فقد يخمّن محسّن الاستعلامات (query optimizer) أنّ استرداد الفهرس، والتصفية بحسب قيمة ‎OwnerId‎، ثم استرداد الصفوف المطلوبة حصرًا، سيكون أسرع من استرداد الجدول بالكامل ، خاصةً إن كان الجدول كبيرًا.

محو فهرس أو تعطيله وإعادة إنشائه

تُستخدم التعليمة ‎DROP‎ لحذف الفهرس. في هذا المثال، تمحو ‎DROP‎ فهرسًا يُسمّى ix_cars_employee_id في الجدول Cars:

DROP INDEX ix_cars_employee_id ON Cars; 

تحذف ‎DROP‎ الفهرس نهائيًا، وفي حال كان الفهرس مُجمّعًا (clustered)، فسيُزال التجميع، ولن يكون بالإمكان إعادة بنائه دون إعادة إنشاء الفهرس، وهي عمليّة يمكن أن تكون بطيئة ومكلفة من الناحية الحسابية.

هناك حلّ آخر، وهو تعطيل (disable) الفهرس بدل حذفه:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE;

يسمح هذا للجدول بالاحتفاظ ببنية الفهرس وبياناته الوصفية (metadata). إضافة إلى إحصائيات الفهرس، وهكذا سيسهُل تقييم التغيير الحاصل. وإذا لزم الأمر، سيكون من الممكن إعادة بناء الفهرس لاحقًا دون الاضطرار إلى إعادة إنشائه من الصفر:

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

الفهارس المجمّعة Clustered أو الفريدة Unique أو المُرتّبة Sorted

لكل فهرس عدد من الخصائص، هذه الخصائص يمكن أن تُحدّد ساعة إنشاء الفهرس، أو يمكن أن تعيَّن لاحقًا.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email); 

تنشئ عبارة SQL أعلاه فهرسًا مُجمّعا (clustered index) جديدًا لجَدول الموظفين Employees. الفهارس المُجمّعة هي فهارس تتحكّم في البنية الفعلية للجدول؛ إذ يُرتَّب الجدول بحيث يُطابق بنية الفهرس. نتيجة لهذا، لا يمكن أن يكون للجدول أكثر من فهرس مُجمّع واحد. ما يعني أنّ الشيفرة أعلاه ستفشل في حال كان الجدول يتوفّر سلفًا على فهرس مُجمّع (تسمّى الجداول التي لا تحتوي على فهارس مُجمّعة "كوْمَات" heaps).

ينشئ المثال التالي فهرسًا فريدًا (unique index) للعمود Email في جدول العملاء Customers. علاوة على تسريع الاستعلام، يفرض الفهرس أن تكون عناوين البريد الإلكتروني فريدة (غير مكرّرة) في العمود. وإن حاولت إدراج صفّ يحتوي بريدًا إلكترونيًا موجودًا سلفا، فستفشل عملية الإدراج أو التحديث (افتراضيًا).

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

ينشئ المثال التالي فهرسًا لجدول العملاء Customers، هذا الفهرس يضع على الجدول قيودًا تنصّ على أنّ الحقل EmployeeID ينبغي أن يكون فريدًا. (ستفشل هذه العملية إن لم يكن العمود فريدًا - أي، إن كان هناك موظف آخر يحمل نفس القيمة.)

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

تنشئ الشيفرة التالية فهرسًا مُرتّبا ترتيبًا تنازليًا. افتراضيا، تُرتّب الفهارس (على الأقل في MSSQL server) تصاعديًا، لكن يمكن تغيير هذا السلوك كما يوضّح المثال التالي:

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

إعادة بناء الفهرس

مع مرور الوقت، قد تصبح الفهارس المتشعّبة من النمط ب B-Tree مُجزّأة (fragmented) نتيجة عمليات التحديث والحذف والإدراج. في نظام SQLServer، هناك نوعان من الفهارس، الفهارس الداخلية، والتي تكون فيها صفحة الفهرس نصف فارغة (half empty)، والفهارس الخارجية، والتي لا يتطابق فيها ترتيب الصفحة المنطقي مع الترتيب الفعلي). إعادة بناء الفهارس تشبه إلى حدّ بعيد حذفها ثمّ إعادة إنشائها.

يمكن إعادة بناء الفهرس باستخدام الصياغة التالية:

ALTER INDEX index_name REBUILD;

إعادة بناء الفهارس هي عملية منقطعة (offline) افتراضيًا، أيّ أنّها تقفِل الجدول أثناء عملها وتمنع التعديل عليه، لكنّ العديد من أنظمة معالجة قواعد البيانات (RDBMS) تسمح بإعادة البناء عبر الشبكة (online). كما توفّر بعض أنظمة قواعد البيانات بدائل أخرى لإعادة بناء الفهارس، مثل ‎REORGANIZE‎ (في SQLServer) أو ‎COALESCE‎ / ‎SHRINK SPACE‎ (في Oracle).

الإدراج باستخدام فهرس فريد

ستفشل الشيفرة التالية في حال تم تعيين فهرس فريد للعمود Email في جدول العملاء:

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1;

تقترح هذه الشيفرة بديلًا ممكنًا في مثل هذه الحالة:

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1 ON DUPLICATE KEY;

رقم الصف Row number

يمكن استخدام أرقام الصفوف في استعلامات SQL عبر الدالة ROW_NUMBER.

يحذف المثال التالي جميع السجلات خلا السجلّ الأخير (جدول من نوع "واحد إلى كثير" - 1‎‎ to Many)

WITH cte AS (
   SELECT ProjectID,
     ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY InsertDate DESC) AS rn
   FROM ProjectNotes
)
DELETE FROM cte WHERE rn > 1;

تضمّن الشيفرة التالية رقم الصف وفقًا لترتيب الطلبية.

SELECT
   ROW_NUMBER() OVER(ORDER BY Fname ASC) AS RowNumber,
   Fname,
   LName
FROM Employees

يقسّم المثال التالي أرقام الصفوف إلى مجموعات وفقًا لمعيار محدّد.

SELECT
  ROW_NUMBER() OVER(PARTITION BY DepartmentId ORDER BY DepartmentId ASC) AS RowNumber,
  DepartmentId, Fname, LName
FROM Employees

الفرق بين Group By و Distinct في SQL

تُستخدم العبارة ‎GROUP ‎BY‎ في SQL مع دوال التجميع (aggregation functions).

إليك الجدول التالي:

يستخدم الاستعلام أدناه ‎GROUP BY‎ لإجراء عمليات حسابية تجميعية (aggregated calculations).

SELECT
    storeName,
    COUNT(*) AS total_nr_orders,
    COUNT(DISTINCT userId) AS nr_unique_customers,
    AVG(orderValue) AS average_order_value,
    MIN(orderDate) AS first_order,
    MAX(orderDate) AS lastOrder
FROM
    orders
GROUP BY
    storeName;

يُعاد الخرج التالي:

بالمقابل، تُستخدم ‎DISTINCT‎ لسرد توليفة فريدة (unique combination) من القيم المختلفة للأعمدة المحدّدة.

SELECT DISTINCT
    storeName,
    userId
FROM
    orders;

سنحصل على الخرج:

البحث عن التكرارات في جزء من الأعمدة

يستخدم المثال التالي دالة نافذة - Window Function - (أي دالة تجري حسابات على مجموعة من الصفوف، كما تتيح الوصول إلى بيانات السجلات التي تسبق السجل الحالي أو التي تلحقه) لعرض جميع الصفوف المُكرّرة (في جزء من الأعمدة).

WITH CTE (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName,
LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

ترجمة -وبتصرّف- للفصول من 34 إلى 40 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: الدوال النصية String Functions في SQL
• المقال السابق: حذف الجداول وقواعد البيانات في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0

تتحدّث هذه المقالة عن كيفية حذف الجداول وقواعد البيانات (DROP و DELETE)، واقتطاع الجداول (TRUNCATE TABLE)، وكيفية استخدام الحذف المتسلسل (Cascading Delete) في SQL.

العبارة DELETE

تُستخدَم عبارة DELETE لحذف السجلات من جدول معيّن.

حذف جميع الصفوف

عند استخدام DELETE بدون عبارة ‎WHERE‎، ستُحذف جميع الصفوف من الجدول.

DELETE FROM Employees

على العموم، أداء العبارة TRUNCATE (انظر الفقرة أدناه) أفضل من أداء DELETE، لأنّها تتجاهل الزنادات (triggers) والفهارس وتحذف البيانات مباشرة.

استخدام DELETE مع WHERE

ستحذف الشيفرة التالية جميع الصفوف التي تفي بشرط ‎WHERE‎، أي الصفوف التي تمثل الموظفين الذين يحملون الاسم John .

DELETE FROM Employees
WHERE FName = 'John'

الاقتطاع عبر TRUNCATE

تُستخدم عبارة الاقتطاع TRUNCATE لإعادة الجدول إلى الحالة التي كان عليها عند إنشائه. إذ تُحذف جميع الصفوف من الجدول، ويُعاد تعيين القيم تلقائية الزيادة (auto-increment) إلى قيمتها الأولى.

لا تحذف TRUNCATE كلّ صف على حدة كما هو الشأن مع DELETE:

TRUNCATE TABLE Employees

حذف بعض الصفوف بناءً على نتائج عمليات المقارنة مع جداول أخرى

من الممكن حذف البيانات (‎DELETE‎) من جدول إذا كانت مطابقة (أو غير مطابقة) لبيانات جدول آخر.

لنفترض أنّنا نريد حذف البيانات من الجدول المصدري بمجرّد تحميلها إلى الجدول الهدف.

DELETE FROM Source
WHERE EXISTS ( SELECT 1 --ليست مهمّة SELECT القيمة المحدّدة في
 FROM Target
 Where Source.ID = Target.ID )

تسمح معظم أنظمة معالجة قواعد البيانات (RDBMS) الشهيرة (مثل MySQL و Oracle و PostgresSQL و Teradata) بضمّ الجداول خلال عملية الحذف ‎DELETE‎، ممّا يتيح إجراء موازنات معقّدة في عبارات قصيرة.

دورة علوم الحاسوب

دورة تدريبية متكاملة تضعك على بوابة الاحتراف في تعلم أساسيات البرمجة وعلوم الحاسوب

اشترك الآن

لنفترض الآن أنّنا نريد تجميع (Aggregate) جدول من الجدول الهدف على أساس التاريخ date وليس المُعرّف ID. لنفترض أيضًا أنّنا نريد ألّا تُحذف البيانات من المصدر إلّا بعد أن يُملأ حقل التاريخ Date الخاص بالجدول المُجمَّع (aggregate).

في أنظمة MySQL و Oracle و Teradata، يمكن القيام بذلك باستخدام:

DELETE FROM Source
WHERE Source.ID = TargetSchema.Target.ID
     AND TargetSchema.Target.Date = AggregateSchema.Aggregate.Date

أمّا في PostgreSQL، فاستخدم الصياغة التالية:

DELETE FROM Source
USING  TargetSchema.Target, AggregateSchema.Aggregate
WHERE Source.ID = TargetSchema.Target.ID
     AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

ينتج عن هذا أساسًا عمليات ضمّ داخلي (INNER JOINs) بين الجدول المصدري والجدول الهدف والجدول المُجمّع (Aggregate.) يُنفّذ الحذف على الجدول المصدري في حال وجود نفس المعرّفات في الهدف، وكذلك في حال تساوي التاريخين date في الجدول الهدف وكذلك في الجدول المُجمَّع.

يمكن كتابة الاستعلام نفسه (في MySQL و Oracle و Teradata) على النحو التالي:

DELETE Source
FROM Source, TargetSchema.Target, AggregateSchema.Aggregate
WHERE Source.ID = TargetSchema.Target.ID
 AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

في بعض أنظمة إدارة قواعد البيانات (مثل Oracle و MySQL)، يُمكن أن استخدام عمليات الضمّ joins صراحة في عبارات ‎Delete‎، بيْد أنّها غير مدعومة في جميع المنصات (كما هو الحال في Teradata).

يمكن إجراء عمليات الموازنة للتحقق من سيناريوهات عدم التطابق بدلاً من سيناريوهات التطابق مع جميع أنماط الصياغات (لاحظ ‎NOT‎ EXISTS‎ أدناه):

DELETE FROM Source
WHERE NOT EXISTS ( SELECT 1 -- لا تهمّ SELECT القيم المحدّدة في
 FROM Target
 Where Source.ID = Target.ID )

الاقتطاع عبر TRUNCATE

تحذف عبارة TRUNCATE كافة البيانات من الجدول. فهي تكافئ إجراء عملية الحذف DELETE بدون تصفية، ولكن قد تكون لها بعض القيود أو التحسينات اعتمادا على برنامج قواعد البيانات المُستخدم.

يزيل المثال التالي جميع الصفوف من جدول الموظفين Employee:

TRUNCATE TABLE Employee;

يُفضّل عمومًا استخدام TRUNCATE على DELETE، لأنّها تتجاهل جميع الفهارس والزنادات (triggers)، وتزيل العناصر مباشرة.

حذف الجداول (DELETE) هي عملية تعمل على الصفوف، بمعنى أنّها تحذف كل صفّ على حدة. أما اقتطاع الجداول، فهي عملية تعمل على صفحة كاملة من البيانات (page operation)، إذ يُعاد تخصيص (reallocate) صفحة البيانات بأكملها. إذا كان لديك جدول يحتوي مليون صفّ، فسيكون اقتطاع الجدول أسرع بكثير من استخدام عبارة حذف DELETE الجدول.

بالمقابل، يمكننا تحديد الصفوف المراد حذفها باستخدام DELETE، ولكن لا يمكننا تحديد الصفوف المراد اقتطاعها، إذ لا يمكننا سوى اقتطاع جميع السجلات مرّة واحدة.

يؤدّي حذف جميع الصفوف (عبر DELETE) ثم إدراج سجلات جديدة إلى زيادة قيمة المفتاح الرئيسي المتزايد تلقائيًا (Auto incremented Primary key) انطلاقًا من القيمة المُدرجة سابقًا، أمّا في عبارة Truncate، فسيُعاد تعيين قيمة المفتاح الرئيسي التلقائي، وسيبدأ من 1.

لاحظ أنه عند اقتطاع جدول ما، يجب ألا تكون هناك مفاتيح خارجية (foreign keys)، وإلا فسيُطرح خطأ.

DROP

محو جدول DROP TABLE

تحذف عبارة DROP TABLE جدولًا مع بياناته من قاعدة البيانات بشكل دائم.

الأمثلة التالية تتحقّق من وجود الجدول قبل محوه:

• MySQL ≥ 3.19

DROP TABLE IF EXISTS MyTable;

• PostgreSQL ≥ 8.x

DROP TABLE IF EXISTS MyTable;

• SQL Server ≥ 2005

If Exists(Select * From Information_Schema.Tables
      Where Table_Schema = 'dbo'
         And Table_Name = 'MyTable')
    Drop Table dbo.MyTable

• SQLite ≥ 3.0

DROP TABLE IF EXISTS MyTable;

محو قاعدة بيانات

يمكن محو قاعدة البيانات باستخدام عبارة DROP DATABASE.

تنبيه: تحذف DROP DATABASE قاعدة البيانات نهائيًا، لذا عليك أن تحرص دائمًا على تخزين نسخة احتياطية من قاعدة البيانات إن خشيت ضياع البيانات.

تمحو الشيفرة التالي قاعدة بيانات الموظفين:

DROP DATABASE [dbo].[Employees]

الحذف المتسلسل Cascading Delete

لنفترض أنّ لديك تطبيقًا يدير فندقًا يضمّ عددًا من الغرف. لنفترض أنّ لديك العديد من العملاء، وقد قرّرت إنشاء قاعدة بيانات لتخزين المعلومات الخاصة بعملائك.

ستحتوي قاعدة البيانات جدولًا واحدًا للعملاء، وآخر للغرف. كل عميل يمكن أن يستأجر N غرفة. هذا يعني أنّ جدول الغرف سيحتوي مفتاحًا خارجيًا (foreign key) يشير إلى جدول العملاء.

ALTER TABLE dbo.T_Room WITH CHECK ADD CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID)
GO

عند خروج أحد العملاء، سيتعيّن عليك حذف بياناته من البرنامج. .لكن إن كتبت:

DELETE FROM T_Client WHERE CLI_ID = x

فسترتَكب "انتهاكَ مفتاحٍ خارجي" (foreign key violation)، ذلك أنّه لا يجوز لك حذف عميل لديه غرفة.

عليك حذف غرف العميل قبل أن تحذف العميل. لنفترض أنّك تتوقّع أنّه قد تُضاف العديد من المفاتيح الخارجية (foreign key dependencies) في قاعدة البيانات مستقبلا نتيجةً لنموّ التطبيق. قد يخلق هذا مشكلة كبيرة. لأنّه في كلّ مرّة تعدّل قاعدة البيانات، سيكون عليك تعديل شيفرة تطبيقك في كل المواضِع المرتبطة بها. وقد يكون عليك أيضًا تعديل شيفرات تطبيقات أخرى (مثل الواجهات البرمجية للأنظمة الأخرى).

هناك حل أفضل يكفيك كلّ هذا العناء. فيكفي أن تضيف العبارة ‎ON DELETE CASCADE‎ إلى مفتاحك الخارجي.

ALTER TABLE dbo.T_Room -- WITH CHECK -- SQL-Server can specify WITH CHECK/WITH NOCHECK
ADD CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID)
ON DELETE CASCADE

الآن يمكنك أن تكتب:

DELETE FROM T_Client WHERE CLI_ID = x

وستُحذف الغرف تلقائيًا عند حذف العميل. لقد حللنا المشكلة دون الحاجة إلى إجراء تغييرات في ِشيفرة التطبيق.

تنبيه: في Microsoft SQL-Server، لن تنجح هذه المقاربة إذا كان الجدول يشير إلى نفسه. لذا إن حاولت إجراء حذف متسلسل على بنية متشعّبة عودية (recursive tree structure)، على النحو التالي:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] WITH CHECK ADD CONSTRAINT
[FK_T_FMS_Navigation_T_FMS_Navigation] FOREIGN KEY([NA_NA_UID])
REFERENCES [dbo].[T_FMS_Navigation] ([NA_UID])
ON DELETE CASCADE
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] CHECK CONSTRAINT [FK_T_FMS_Navigation_T_FMS_Navigation]
GO

فلن ينجح الأمر، لأنّ Microsoft-SQL-server لن تسمح لك بتعيين مفتاح خارجي باستخدام ‎ON DELETE CASCADE‎ على بنية متشعّبة عودية. أحد أسباب ذلك هو أنّ الشعبة قد تكون دورية، وهذا قد يؤدي إلى عملية سرمدية غير منتهية.

نظام PostgreSQL من ناحية أخرى يمكنه القيام بذلك؛ شريطة ألّا تكون الشعبة دورية (non-cyclic). إذ أنّه في حال كانت الشعبة دورية، فسيُطرح خطأ وقت التشغيل. الحل في مثل هذه الحالة هو إنشاء دالة حذف مخصّصة.

تنبيه: لا يمكنك حذف جدول العملاء وإعادة إدراج القيم مرّة أخرى، وإن حاولت ذلك، فستُحذف جميع المدخلات في الجدول T_Room.

ترجمة -وبتصرّف- للفصول من 29 إلى 33 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: مواضيع متقدمة في SQL
• المقال السابق: معالجة الأخطاء والتعديل على قواعد البيانات في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0

تستعرض هذه المقالة كيفية معالجة الأخطاء باستخدام العبارة TRY / CATCH، وكيفية حساب الاتحاد (UNION) وبعض العمليات الأخرى التي تمكّن من تعديل قواعد البيانات.

العبارة TRY / CATCH

تُستخد عبارة TRY / CATCH لإمساك الأخطاء في الشيفرات التي يُتوقع أن تطرح أخطاء.

ستفشل عمليتا الإدراج في المثال التالي بسبب خطأ في صياغة التاريخ:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, 'not a date', 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    THROW
    ROLLBACK TRANSACTION
END CATCH

في المثال التالي، ستكتمل عَمليتا الإدراج دائمًا:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    THROW
    ROLLBACK TRANSACTION
END CATCH

الاتحاد UNION

تُستخدم الكلمة المفتاحية UNION في SQL لدمج نتائج عبارتي SELECT دون تكرار. أي أنّها تشبه عملية الاتحاد المعروفة في علم المجموعات.

من أجل استخدام UNION لدمج النتائج، يُشترط أن يكون لكلي عبارتي SELECT عدد الأعمدة ونوع البيانات نفسه، ووفق الترتيب نفسه، ولكن يجوز أن تختلف أطوال الأعمدة.

إليك الجدولين التاليين:

CREATE TABLE HR_EMPLOYEES
(
    PersonID int,
    LastName VARCHAR(30),
    FirstName VARCHAR(30),
    Position VARCHAR(30)
);
CREATE TABLE FINANCE_EMPLOYEES
(
    PersonID INT,
    LastName VARCHAR(30),
    FirstName VARCHAR(30),
    Position VARCHAR(30)
);

باستخدام ‎UNION‎، يمكننا الحصول على جميع المدراء (‎manager‎) العاملين في القسمين HR و Finance على النحو التالي:

SELECT
    FirstName, LastName  
FROM
    HR_EMPLOYEES  
WHERE
    Position = 'manager' 
UNION ALL 
SELECT
    FirstName, LastName  
FROM
    FINANCE_EMPLOYEES  
WHERE
    Position = 'manager' 

تزيل العبارة ‎UNION‎ الصفوف المكرّرة من نتائج الاستعلام. لكن لمّا كان من الممكن أن يشترك عدّة أشخاص في نفس الاسم، ويحتلّون نفس الموقع في كلا القسمين، فسنستخدم عبارة ‎UNION ALL‎ التي لا تزيل التكرارات.

يمكنك تكنِية (aliasing) الأعمدة المُعادة عبر وضع الكُنى في أول عبارات select على النحو التالي:

SELECT
    FirstName as 'First Name', LastName as 'Last Name'
FROM
    HR_EMPLOYEES  
WHERE
    Position = 'manager' 
UNION ALL 
SELECT
    FirstName, LastName  
FROM
    FINANCE_EMPLOYEES  
WHERE
    Position = 'manager' 

هناك فرق أساسي بين ‎UNION‎ و UNION ALL:

• تضمّ ‎UNION‎ مجموعتي النتائج مع إزالة التكرارات من مجموعة النتائج
• تضمّ UNION ALL مجموعتي النتائج دون إزالة التكرارات

من الأخطاء الشائعة استخدامُ ‎UNION‎ في المواضع التي لا تكون فيها حاجة إلى إزالة التكرار من النتائج، فالكلفة الإضافية على الأداء قد تكون أكبر من المكاسب الناجمة عن إزالة التكرارات.

متى تستخدم UNION

لنفترض أنك تريد تصفية الدول بحسب قيم سمتين (attributes) مختلفتين، وأنّك أنشأت فهارس منفصلة غير مجمّعة (non-clustered indexes) لكل عمود. في هذه الحالة، يمكنك استخدام ‎UNION‎، التي تتيح لك استخدام كلا الفهرسين مع تجنّب التكرارات.

SELECT C1, C2, C3 FROM Table1 WHERE C1 = @Param1
UNION
SELECT C1, C2, C3 FROM Table1 WHERE C2 = @Param2

لن تُستخدم إلا الفهارس البسيطة في تنفيذ الاستعلامات، كما يمكنك تقليل عدد الفهارس المنفصلة غير المجمّعة (separate non-clustered indexes)، وهذا سيؤدي إلى تحسين الأداء.

متى تستخدم UNION ALL

لنفترض أنك تريد تصفية جدول ما بحسب قيمتي سمتين، بيْد أنّك لا تحتاج إلى تصفية السجلات المكرّرة (إمّا لأنّ ذلك لن يضرّ، أو أنّك صمّمت قاعدة البيانات بحيث لن ينتج أيّ تكرارات عن عملية الاتحاد).

SELECT C1 FROM Table1
UNION ALL
SELECT C1 FROM Table2

يمكن أن يكون استخدام UNION ALL مفيدًا عند إنشاء معارض (Views) تضمّ (join) بيانات صُمِّمت لكي تُقسَّم وتُوزَّع عبر عدّة جداول (ربما لأسباب تتعلق بتحسين الأداء). ولمّا كانت البيانات مقُسمة سلفًا، فإنّ جعل محرّك قاعدة البيانات يزيل التكرارات لن يضيف أيّ قيمة، وسَيبطئ الاستعلام.

ALTER TABLE

تُستخدم العبارة ALTER في SQL لتعدِيل قيد (constraint) أو عمود من جدول.

إضافة عمود

يضيف المثال التالي عمودين إلى جدول الموظفين، الأول باسم ‎StartingDate‎، ولا يمكن أن يكون معدومًا (not NULLABLE)، وقيمته الافتراضية تساوي التاريخ الحالي، أمّا العمود الثاني، فيُسمّى ‎DateOfBirth‎، ويمكن أن يكون معدومًا.

ALTER TABLE Employees
ADD StartingDate date NOT NULL DEFAULT GetDate(),
    DateOfBirth date NULL

حذف عمود

تحذف الشيفرَة أدناه العمود salary من جدول الموظفين:

ALTER TABLE Employees
DROP COLUMN salary;

إضافة مفتاح رئيسي Primary Key

تضيف الشيفرة أدناه مفتاحًا رئيسيًا إلى جدول "الموظفين" في الحقل ‎ID‎:

ALTER TABLE EMPLOYEES ADD pk_EmployeeID PRIMARY KEY (ID)

يؤدّي تضمين عدّة أسماء أعمدة بين قوسين بعد العبارة PRIMARY KEY إلى إنشاء مفتاح رئيسي مُركّب (Composite Primary Key):

ALTER TABLE EMPLOYEES ADD pk_EmployeeID PRIMARY KEY (ID, FName)

تغيير عمود

يعدّل الاستعلام التالي نوع بيانات العمود ‎StartingDate‎، ويغيّره من النوع ‎date‎ إلى ‎datetime‎، كما يعيّن قيمته الافتراضية عند قيمة التاريخ الحالي.

ALTER TABLE Employees
ALTER COLUMN StartingDate DATETIME NOT NULL DEFAULT (GETDATE())

حذف القيود Drop Constraint

تحذف الشيفرة التالية قيدًا يُسمّى DefaultSalary من جدول الموظفين:

ALTER TABLE Employees
DROP CONSTRAINT DefaultSalary

تنبيه: عليك حذف قيود العمود قبل حذف العمود.

الإدراج عبر INSERT

إدراج البيانات من جدول آخر باستخدام SELECT

يدرج المثال التالي جميع الموظفين (Employees) في جدول العملاء (Customers). ونظرًا لأنّ الجدولين يحتويان على حقول مختلفة، وقد لا تريد نقل جميع الحقول، فستحتاج إلى تحديد الحقول التي ستُدرج القيم فيها، و الحقول التي يجب اختيارها.

لست مضطرّا للحفاظ على أسماء الحقول المترابطة (correlating field)، بيْد أنّه ينبغي عدم تغيير نوع البيانات.

يفترض هذا المثال أنّ هويّة حقل المعرّف (Id) مُحدّدة، وأنه تلقائيّ التزايد (auto increment).

INSERT INTO Customers (FName, LName, PhoneNumber)
SELECT FName, LName, PhoneNumber FROM Employees

إن كان للجدولين نفس أسماء الحقول، وكنت تريد نقل جميع السجلات من جدول إلى آخر، فيمكنك استخدام الشيفرة التالية:

INSERT INTO Table1
SELECT * FROM Table2

إدراج صف جديد

تدرج الشيفرة التالية صفًّا جديدًا في الجدول ‎Customers‎. لاحظ أنّنا لم نحدّد قيمة العمود ‎Id‎، ذلك أنّ قيمته ستضاف تلقائيًا. أمّا الأعمدة الأخرى فينبغي أن تُعيّن قيمها.

INSERT INTO Customers
VALUES ('Zack', 'Smith', 'zack@example.com', '7049989942', 'EMAIL');

إدراج أعمدة معيّنة

تدرج الشيفرة التالية صفًّا جديدًا في الجدول ‎Customers‎، لاحظ أنّها لن تُدرج البيانات إلّا في الأعمدة المُحدّدة:

INSERT INTO Customers (FName, LName, Email, PreferredContact)
VALUES ('Zack', 'Smith', 'zack@example.com', 'EMAIL');

لاحظ أنه لم يتم تقديم أيّ قيمة للعمود ‎PhoneNumber‎. ولاحظ أيضًا أنّه ينبغي تضمين الأعمدة الموسومة بـ ‎not null‎.

إدراج عدّة صفوف دفعة واحدة

يمكن إدراج عدّة صفوف باستخدام أمر إدراج واحد على النحو التالي:

INSERT INTO tbl_name (field1, field2, field3)
VALUES (1,2,3), (4,5,6), (7,8,9);

قد تحتاج أحيانًا إلى إدراج كمّيات كبيرة من البيانات دُفعة واحدة، عادة ما توفّر أنظمة إدارة قواعد البيانات بعض التوصيات والميزات للمساعدة على ذلك، كما في حالة:

• MySQL
• MSSQL

الدمج عبر MERGE

تسمح العبارة MERGE (تُسمّى أيضًا UPSERT) بإدراج صفوف جديدة أو تحديثها. الهدف من هذه العملية هو إجراء مجموعة كاملة من العمليات تلقائيًا (لضمان بقاء البيانات متسقة)، ومنع حمل الاتصال الزائد (communication overhead) لعبارات SQL في نظام عميل / خادم (client/server system).

إجراء عملية الدمج لجعل المصدر يطابق الهدف

يجري المثال التالي عملية دمج (MERGE) لجعل الجدول المصدري يطابق الجدول الهدف:

MERGE INTO targetTable t
    USING sourceTable s
        ON t.PKID = s.PKID
     WHEN MATCHED AND NOT EXISTS (
             SELECT s.ColumnA, s.ColumnB, s.ColumnC
             INTERSECT
             SELECT t.ColumnA, t.ColumnB, s.ColumnC
             )
     THEN UPDATE SET
            t.ColumnA = s.ColumnA
         ,t.ColumnB = s.ColumnB
         ,t.ColumnC = s.ColumnC
     WHEN NOT MATCHED BY TARGET
         THEN INSERT (PKID, ColumnA, ColumnB, ColumnC)
         VALUES (s.PKID, s.ColumnA, s.ColumnB, s.ColumnC)
     WHEN NOT MATCHED BY SOURCE
         THEN DELETE
     ;

ملاحظة: تمنع العبارة ‎AND NOT EXISTS‎ تحديث السجلات التي لم تتغير. ويتيح استخدام البنية ‎INTERSECT‎ موازنة الأعمدة المعدومة (nullable) بدون مشاكل.

MySQL: عدّ أسماء المستخدمين

لنفترض أنّنا نريد أن نحسب عدد المستخدمين الذين لهم نفس الاسم. سننشئ أولًا جدولًا ‎users‎ على النحو التالي:

create table users(
    id int primary key auto_increment,
    name varchar(8),
    count int,
    unique key name(name)
);

لنفترض الآن أنّنا اكتشفنا وجود مستخدم آخر جديدًا يُدعى Joe، ونودّ أن نأخذه في الحسبان. أولًا، سنتحقّق ممّا إذا كان هناك صفّ يحمل ذلك الاسم، فإن كان الأمر كذلك، حدّثناه وزدنا قيمته بواحد؛ خلاف ذلك، سننشئ صفًّا جديدًا.

تستخدم MySQL الصياغة التالية: insert … on duplicate key update …‎‎:

insert into users(name, count)
    values ('Joe', 1)
    on duplicate key update count=count+1;

PostgreSQL: عدّ أسماء المستخدمين

لنفترض أنّنا نريد أن نعرف عدد المستخدمين الذين لهم نفس الاسم. سننشئ جدولًا ‎users‎ على النحو التالي:

create table users(
    id serial,
    name varchar(8) unique,
    count int
);

كما في الفقرة السابقة، سنفترض أنّنا اكتشفنا للتو مستخدمًا جديدًا يُدعى Joe، ونودّ أن نأخذه في الحسبان:

تستخدم PostgreSQLالصياغة التالية: insert … on conflict … do update …‎‎:

insert into users(name, count)
    values('Joe', 1)
    on conflict (name) do update set count = users.count + 1;

التطبيق المتقاطع والتطبيق الخارجي cross apply, outer apply

تُستخدم العبارة Apply لتطبيق دالة على جدول.

سننشئ جدولًا Department لتخزين المعلومات الخاصّة بالأقسام. ثم ننشئ جدولًا Employee يحتوي معلومات حول الموظفين. ينتمي كلّ موظّف إلى قسم معيّن، وبالتالي، فإنّ لجدول الموظّفين مرجعًا متكاملًا (referential integrity) مع جدول الأقسام.

يختار الاستعلام الأول البيانات من جدول الأقسام، ثمّ يستخدم العبارة CROSS APPLY لتقييم جدول الموظفين نسبة إلى سجلّات جدول الأقسام. أمّا الاستعلام التالي، [فيضمّ](رابط الفصل 6) جدول الأقسام إلى جدول الموظفين، ثم يعيد جميع السجلات التي تحقّق شرط الضمّ.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
    ON D.DepartmentID = E.DepartmentID

لو نظرت إلى النتائج المُعادة، فستلاحظ أنّ الاستعلامين يعيدان نفس النتائج؛ قد تسأل إذن: كيف تختلف CROSS APPLY عن JOIN، وهل أداؤها أحسن.

يختار الاستعلام الأول في الشيفرة التالية البيانات من جدول الأقسام، ثمّ يستخدم OUTER APPLY لتقييم جدول الموظفين نسبة إلى كلّ سجل من سجلّات جدول الأقسام. تُعطى قيم الصفوف التي ليس لها مُطابق في جدول الموظفين القيمة المعدومة NULL، كما هو حال الصفّين 5 و 6.

يستخدم الاستعلام الثاني الضم الخارجي اليساري LEFT OUTER JOIN بين جدول الأقسام وجدول الموظفين. وكما هو متوقع، يعيد الاستعلام كافّة الصفوف من جدول الأقسام؛ بما فيها الصفوف التي ليس لها مُطابق في جدول الموظفين.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
    ON D.DepartmentID = E.DepartmentID
GO

رغم أنّ الاستعلامين أعلاه يعيدان المعلومات نفسها، فإنّ خطة التنفيذ تختلف بعض الشيء. بيْد أنّه لن يكون هناك اختلاف يُذكر في الأداء.

في بعض الحالات، يكون استخدام المعامل APPLY ضروريًا. في المثال التالي، سننشئ دالة جدولية (table-valued function)، تقبل الحقل DepartmentID كمعامل، وتعيد جميع الموظفين المنتمين إلى القسم ذي المعرّف DepartmentID. يختار الاستعلام التالي البيانات من الجدول Department ويستخدم CROSS APPLY مع الدالة التي أنشأناها. يمرَّر الحقل DepartmentID من كل صفّ من الجدول الخارجي - outer table - (أي الجدول Department)، ثمّ يطبّق الدالة على كلّ صف بشكل يماثل الاستعلامات الفرعية المرتبطة (correlated subquery).

يستخدم الاستعلام الثاني OUTER APPLY بدلاً من CROSS APPLY، وبالتالي، فعلى عكس التطبيق المتقاطع CROSS APPLY الذي لا يعيد إلّا البيانات المرتبطة (correlated data)، يعيد التطبيق الخارجي OUTER APPLY البيانات غير المرتبطة أيضًا، مع وضع القيم المعدومة (NULLs) في الأعمدة غير الموجودة.

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
RETURNS TABLE
AS
    RETURN
    (
    SELECT
       *
    FROM Employee E
    WHERE E.DepartmentID = @DeptID
    )
GO
SELECT
    *
FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT
    *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

قد يتبادر إلى ذهنك السؤال التالي: هل يمكننا استخدام عمليّة ضمّ بسيطة بدلاً من استخدام الاستعلامات أعلاه؟ الجواب هو "لا"، إذا استبدلت CROSS / OUTER APPLY في الاستعلامات أعلاه بـعمليّات الضمّ INNER JOIN أو LEFT OUTER JOIN، وحدّدت العبارة ON (مثلًا 1 = 1)، ثمّ نفّذت الاستعلام، فسيُطرح الخطأ:

The multi-part identifier "D.DepartmentID" could not be bound.

ذلك أنّه في عمليّات الضمّ، يكون سياق تنفيذ الاستعلام الخارجي مختلفًا عن سياق تنفيذ الدالة (أو الجدول المشتق - derived table)، ولا يمكنك تمرير قيمة أو متغيّر من الاستعلام الخارجي إلى الدالة كمعامل. لهذا يجب استخدام المعامل APPLY في مثل هذه الاستعلامات.

ترجمة -وبتصرّف- للفصول من 23 إلى 28 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: حذف الجداول وقواعد البيانات في SQL
• المقال سابق: تحديث الجداول في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0

تستعرض هذه المقالة كيفية تحديث قواعد البيانات، وكيفية إنشاء قواعد بيانات وجداول ودوال جديدة.

التحديث عبر UPDATE

تُستخدم الكلمة المفتاحية UPDATE لتحديث بيانات الجداول.

تحديث جدول من بيانات جدول آخر

تملأ الأمثلة الموضحة أدناه الحقل ‎PhoneNumber‎ لأي موظف يكون أيضًا عميلًا ‎Customer‎، وليس له حاليًا رقم هاتف في جدول الموظفين ‎Employees‎.

(تستخدم الأمثلة التالية جدولي الموظفين Employees والعملاء Customers)

• SQL القياسية

يحدّث المثال التالي قاعدة البيانات باستخدام استعلام فرعي مربوط (correlated subquery):

UPDATE
    Employees
SET PhoneNumber =
   (SELECT
         c.PhoneNumber
   FROM
         Customers c
   WHERE
         c.FName = Employees.FName
         AND c.LName = Employees.LName)
WHERE Employees.PhoneNumber IS NULL

• SQL:2003

تحديث باستخدام ‎MERGE‎:

MERGE INTO
    Employees e
USING
    Customers c
ON
    e.FName = c.Fname
   AND e.LName = c.LName
   AND e.PhoneNumber IS NULL
WHEN MATCHED THEN
  UPDATE
       SET PhoneNumber = c.PhoneNumber

• SQL Server

تحديث باستخدام ‎INNER JOIN‎:

UPDATE
    Employees
SET
    PhoneNumber = c.PhoneNumber
FROM
    Employees e
INNER JOIN Customers c
 ON e.FName = c.FName
 AND e.LName = c.LName
WHERE
    PhoneNumber IS NULL

تعديل القيم الحالية

يستخدم هذا المثال "الجدول Car" من الفصل 1.

تتيح لك SQL إمكانية استخدام القيم القديمة في عمليات التحديث. في المثال التالي، تُزاد قيمة ‎TotalCost‎ بمقدار 100 في الصفين ذوي المعرّفين 3 و4:

UPDATE Cars
SET TotalCost = TotalCost + 100
WHERE Id = 3 or Id = 4

• زِيدَت قيمة TotalCost الخاصّة بالسيارة 3 من 100 إلى 200.
• زيدت قيمة TotalCost الخاصّة بالسيارة 4 من 1254 إلى 1354.

يمكن اشتقاق القيمة الجديدة للعمود من قيمته السابقة، أو من قيمة أيّ عمود آخر في الجدول أو من الجدول المضموم (joined table) نفسه.

تحديث صفوف معيّنة

تعيّن الشيفرة أدناه قيمة حالة الصف ذي المُعرٍّف 4 إلى READY.

UPDATE
    Cars
SET
    Status = 'READY'
WHERE
    Id = 4

تقيّم عبارة ‎WHERE‎ شرطًا منطقيًا في كل صفّ. إذا استوفى الصف ذلك الشرط، فستُحدّث قيمته. خلاف ذلك، يظل الصف دون تغيير.

تحديث جميع الصفوف

يعيّن المثال التالي العمود "status" الخاص بجميع صفوف الجدول "Cars" إلى القيمة "READY"، التحديث يشمل جميع الصفوف بسبب غياب العبارة ‎WHERE‎ (التي تصفّي الصفوف).

UPDATE Cars
SET Status = 'READY'

التقاط السجلات المُحدّثة

قد ترغب في بعض الأحيان في التقاط السجلات التي حُدِّثت للتو. يمكنك ذلك عبر الصياغة التالية:

CREATE TABLE #TempUpdated(ID INT)
Update TableName SET Col1 = 42
    OUTPUT inserted.ID INTO #TempUpdated
    WHERE Id > 50

إنشاء قاعدة بيانات عبر CREATE

يمكن إنشاء قاعدة بيانات باستخدام أمر SQL التالي:

CREATE DATABASE myDatabase;

سينتج عن الشيفرة أعلاه قاعدة بيانات فارغة باسم myDatabase، والتي يمكن أن تضيف جداول إليها باستخدام العبارة CREATE TABLE.

إنشاء جدول عبر CREATE TABLE

تُستخدم العبارة CREATE TABLE لإنشاء جدول جديد في قاعدة البيانات. يتألّف تعريف الجدول من قائمة من الأعمدة وأنواعها، إضافة إلى أيّ قيود تكاملية (integrity constraints).

إنشاء جدول عبر Select

يمكنك إنشاء نسخة مكرّرة من جدول معيّن على النحو التالي:

CREATE TABLE ClonedEmployees AS SELECT * FROM Employees;

يمكنك استخدام أيّ من الميزات الأخرى لعبارة SELECT لتعديل البيانات قبل نقلها إلى الجدول الجديد.

تُنشأ أعمدة الجدول الجديد تلقائيًا انطلاقًا من الصفوف المُختارة.

CREATE TABLE ModifiedEmployees AS
SELECT Id, CONCAT(FName," ",LName) AS FullName FROM Employees
WHERE Id > 10;

إنشاء جدول جديد

تنشئ الشيفرة التالية جدولًا بسيطًا باسم (‎Employees‎)، يتألّف من معرّف، واسم الموظف الأول، واسم العائلة، إضافة إلى رقم الهاتف:

CREATE TABLE Employees(
    Id int identity(1,1) primary key not null,
    FName varchar(20) not null,
    LName varchar(20) not null,
    PhoneNumber varchar(10) not null
);

هذا المثال خاص بالإصدار Transact-SQL،

تنشئ العبارة CREATE TABLE جدولًا جديدًا وتضيفه إلى قاعدة البيانات، تُتبع هذه العبارة باسم الجدول (Employees). ثم تعقبه قائمة بأسماء الأعمدة وخصائصها، مثل المعرّف ID.

Id int identity(1,1) not null

شرح الشيفرة

إنشاء جدول باستخدام مفتاح خارجي FOREIGN KEY

ينشئ المثال أدناه جدولًا للموظفين ‎Employees‎ يحتوي مرجعًا إلى جدول آخر، وهو جدول المدن ‎Cities‎.

CREATE TABLE Cities(
    CityID INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Zip VARCHAR(10) NOT NULL
);
CREATE TABLE Employees(
    EmployeeID INT IDENTITY (1,1) NOT NULL,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    PhoneNumber VARCHAR(10) NOT NULL,
    CityID INT FOREIGN KEY REFERENCES Cities(CityID)
);

يستعرض الرسم التالي مخططًا توضيحيًا للعلاقة بين قاعدتي البيانات.

لاحظ أنّه في السطر الأخير من الشيفرة، يشير العمود ‎CityID‎ من الجدول ‎Employees‎ إلى العمود ‎CityID‎ في الجدول ‎Cities‎:

CityID INT FOREIGN KEY REFERENCES Cities(CityID)

شرح الشيفرة

تنبيه: لا يجوز إنشاء مرجع إلى جدول غير موجود في قاعدة البيانات. عليك أن تنشئ الجدول ‎Cities‎ أولا، ثم تنشئ الجدول ‎Employees‎ عقب ذلك. إذا فعلت ذلك بترتيب معكوس، فسيُطرَح خطأ.

تكرار جدول Duplicate a table

يمكنك تكرار جدول معيّن عبر الصياغة التالية:

CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;

إنشاء جدول مؤقت

يمكن إنشاء جدول مؤقت خاص بالجلسة (session) الحالية. لكنّ الصياغة تختلف بحسب إصدار SQL:

• PostgreSQL و SQLite

CREATE TEMP TABLE MyTable(...);

• SQL Server

CREATE TABLE #TempPhysical(...);

يمكن كذلك إنشاء جدول مؤقت مرئي للجميع، وليس حصرًا على الجلسة الحالية على النحو التالي:

CREATE TABLE ##TempPhysicalVisibleToEveryone(...);

ويمكن أيضًا إنشاء جدول في الذاكرة:

DECLARE @TempMemory TABLE(...);

إنشاء دالة عبر CREATE FUNCTION

تتيح SQL إنشاء دالة جديدة.

ينشئ المثال التالي دالة باسم FirstWord، والتي تقبل معاملًا من النوع varchar، وتُعيد قيمة من النوع نفسه.

CREATE FUNCTION FirstWord (@input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
     DECLARE @output varchar(1000)
     SET @output = SUBSTRING(@input, 0, CASE CHARINDEX(' ', @input)
         WHEN 0 THEN LEN(@input) + 1
         ELSE CHARINDEX(' ', @input)
     END)
     RETURN @output
END

شرح الشيفرة

ترجمة -وبتصرّف- للفصول من 19 إلى 22 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: معالجة الأخطاء والتعديل على قواعد البيانات في SQL
• المقال السابق: الدمج بين الجداول في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0

تدمج العبارة JOIN البيانات من جدولين، وتعيد مجموعة مختلطة من الأعمدة من كلا الجدولين، وذلك حسب نوع الضمّ المُستخدم، ومعاييره (كيفية ربط الصفوف من كلا الجدولين).

يمكن ضمّ جدولٍ إلى نفسه، أو بأيّ جدول آخر. وإذا كانت هناك حاجة للوصول إلى معلومات من أكثر من جدولين، فيمكن استخدام الضمّ عدّة مرّات في عبارة FROM.

الضمّ الذاتي Self Join

يمكن ضمّ جدول إلى نفسه، بحيث تتطابق الصفوف مع بعضها البعض وفق شروط معينة. في مثل هذه الحالة، يجب استخدام الكُنى (aliases) للتمييز بين العناصر المكرّرة من الجدول.

في المثال التالي، لكلّ موظّف في جدول الموظّفين Employees، يُعاد سجلّ يحتوي الاسم الأول للموظّف، والاسم الأول لمديره. ولمّا كان المدراء هم أيضًا موظفين، فسنضمّ الجدول إلى نفسه:

SELECT
    e.FName AS "Employee",
    m.FName AS "Manager"
FROM 
    Employees e
JOIN 
    Employees m
   ON e.ManagerId = m.Id

سيعيد هذا الاستعلام البيانات التالية:

شرح الاستعلام

يحتوي الجدول الأصلي على هذه السجلات:

الخطوة الأولى في تنفيذ الاستعلام هي إجراء جداء ديكارتي لجميع السجلات في الجداول المستخدمة في عبارة FROM. في حالتنا هذه، استخدمنا جدول الموظفين مرّتين، لذا سيبدو الجدول الوسيط كما يلي (أزلنا الحقول غير المستخدمة في المثال):

الخطوة التالية هي ترشيح السجلات، والإبقاء على السجلات التي تفي بشرط الضمّ وحسب، أي سجلات الجدول ‎e‎ التي يساوي الحقل ‎ManagerId‎ خاصتها الحقلَ ‎Id‎ في الجدول ‎m‎:

بعد ذلك، تُقيّم كل التعبيرات المستخدمة في عبارة SELECT لإعادة الجدول التالي:

أخيرًا، يُستبدل اسما العمودين ‎e.FName‎ و ‎m.FName‎ بكُنيتيهما:

الاختلاف بين الضم الداخلي والخارجي

هناك عدّة أنواع من الضمّ في SQL، وتختلف تلك الأنواع عن بعضها من حيث ما إذا كانت الصفوف التي (لا) تحقّق الشرط ستُضمّ أم لا.

هذه بعض أهمّ أنواع الضمّ: ‎INNER JOIN‎ و ‎LEFT‎ OUTER‎‎JOINوRIGHT OUTER JOIN‎و‎FULL OUTER ‎JOIN‎(الكلمتان المفتَاحيتان‎INNER‎و‎OUTER‎` اختياريتان).

يوضّح الشكل أدناه الاختلافات بين مختلف أنواع الضمّ: تمثل المنطقة الزرقاء النتائج المُعادة من عملية الضمّ، فيما تمثّل المنطقة البيضاء النتائج التي لن تعيدها عملية الضمّ.

وهذه صورة لتمثيل الضمّ المتقاطع (Join SQL)

مصدر الصورة

على سبيل المثال، إليك الجدولين التاليين:

A    B
-    -
1    3
2    4
3    5
4    6

لاحظ أنّ القيمتين (1،2) حصريتان للجدول A، أمّا القيمتان (3،4) فمُشتركتان، و القيمتان (5،6) حصريتان لـ B.

الضمّ الداخلي

يعيد الضم الداخلي تقاطع الجدولين، أي الصفوف المشترك بينهما:

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4

الضم الخارجي اليساري Left outer join

يعيد الضم الخارجي اليساري جميع صفوف A، بالإضافة إلى الصفوف المشتركة مع B:

select * from a LEFT OUTER JOIN b on a.a = b.b;
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

الضم الخارجي اليميني Right outer join

وبالمثل، يعيد الضمّ الخارجي اليميني كل صفوف B، بالإضافة إلى الصفوف المشتركة في A:

select * from a RIGHT OUTER JOIN b on a.a = b.b;
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

الضمّ الخارجي التام Full outer join

يعيد الضمّ الخارجي التام اتحاد A و B، أي جميع الصفوف الموجودة في A وجميع الصفوف الموجودة في B. إذا كانت هناك بيانات في A بدون بيانات مقابلة في B، فسيكون الجزء الخاص بـ B معدوما (null). والعكس صحيح.

select * from a FULL OUTER JOIN b on a.a = b.b;
a   |  b
-----+-----
1 | null
2 | null
3 |    3
4 |    4
null |    6
null |    5

اصطلاحات الضمّ JOIN Terminology

لنفترض أنّ لدينا جدولين A و B، وأنّ بعض صفوفِهما متطابقة (وفق شرط JOIN):

هناك عدّة أنواع مختلفة من الضمّ يمكن استخدامها لأجل تضمين أو استبعاد الصفوف التي (لا) تحقق شرط الضمّ في كلا الجانبين.

تستخدم الأمثلة أدناه البيانات التالية:

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);
CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);
INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');
INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

الضمّ الداخلي Inner Join

يجمع الضمّ الداخلي بين الصفوف اليسرى واليمنى المتطابقة.

SELECT * FROM A JOIN B ON X = Y;
X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

الضم الخارجي اليساري Left outer join

يُسمّى اختصارًا الضمّ اليساري. ويجمع بين الصفوف اليسرى واليمنى التي تحقّق الشرط، مع تضمين الصفوف اليسرى التي لا تحقّق الشرط.

SELECT * FROM A LEFT JOIN B ON X = Y;
X         Y
-----     -----
Amy       NULL
John      NULL
Lisa      Lisa
Marco     Marco
Phil      Phil

الضم الخارجي اليميني Right outer join

يُسمّى اختصارًا الضمّ الأيمن. ويجمع بين الصفوف اليسرى واليمنى التي تحقّق الشرط، مع تضمين الصفوف اليمنى التي لا تحقّق الشرط.

SELECT * FROM A RIGHT JOIN B ON X = Y;
X          Y
-----      -------
Lisa       Lisa
Marco      Marco
Phil       Phil
NULL       Tim
NULL       Vincent

الضمّ الخارجي التام Full outer join

يُسمّى اختصارًا الضمّ التام. وهو اتحاد لعمليتي الضم اليساري واليميني.

SELECT * FROM A FULL JOIN B ON X = Y;
X          Y
-----      -------
Amy        NULL
John       NULL
Lisa       Lisa
Marco      Marco
Phil       Phil
NULL       Tim
NULL       Vincent

الضمّ شبه اليساري

يضمّ هذا النوع الصفوفَ اليُسرى التي تتطابق مع الصفوف اليمنى.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);
X
-----
Lisa
Marco
Phil

الضمّ شبه اليميني Right Semi Join

يضمّ هذا النوع الصفوف اليمنى التي تطابق الصفوف اليسرى.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);
Y
-----
Lisa
Marco
Phil

لا توجد صياغة للعبارة IN مُخصّصة للضمّ شبه اليساري أو شبه اليميني - كلّ ما عليك فعله هو تبديل مواضع الجدول في SQL.

الضمّ شبه اليساري المعكوس Left Anti Semi Join

يُضمِّن هذا النوع الصفوفَ اليُسرى التي لا تتطابق مع الصفوف اليمنى.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);
X
----
Amy
John

تنبيه: استخدام NOT IN في الأعمدة التي تقبل القيم المعدومة NULL قد يسبّب بعض المشاكل (المزيد من التفاصيل هنا).

الضمّ شبه اليميني المعكوس Right Anti Semi Join

يُضمِّن هذا النوع الصفوف اليمنى التي لا تطابق الصفوف اليسرى.

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);
Y
-------
Tim
Vincent

لا توجد صياغة للعبارة IN مخصّصة للضمّ شبه اليساري أو شبه اليميني المعكوس - كلّ ما عليك فعله هو تبديل مواضع الجدول في SQL.

الضم المتقاطع Cross Join

يُجري هذا النوع من الضمّ جداءً ديكارتيًا (Cartesian product) بين الصوف اليسرى والصفوف اليمنى.

SELECT * FROM A CROSS JOIN B;
X         Y
-----     -------
Amy       Lisa
John      Lisa
Lisa      Lisa
Marco     Lisa
Phil      Lisa
Amy       Marco
John      Marco
Lisa      Marco
Marco     Marco
Phil      Marco
Amy       Phil
John      Phil
Lisa      Phil
Marco     Phil
Phil      Phil
Amy       Tim
John      Tim
Lisa      Tim
Marco     Tim
Phil      Tim
Amy       Vincent
John      Vincent
Lisa      Vincent
Marco     Vincent
Phil      Vincent

يكافئ الضمّ المتقاطع ضمًّا داخليًا ذا شرط يتحقّق دائمًا، لذا سيعيد الاستعلام التالي النتيجة نفسها:

SELECT * FROM A JOIN B ON 1 = 1;

الضمّ الذاتي Self-Join

يشير هذا النوع من الضم إلى ضمّ الجدول إلى نفسه. يمكن أن تكون عملية الضمّ الذاتي من أيّ نوع من أنواع الضمّ التي ناقشناها أعلاه. على سبيل المثال، هذا ضمّ ذاتي داخلي ( inner self-join):

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);
X         X
----      -----
Amy       John
Amy       Lisa
Amy       Marco
John      Marco
Lisa      Marco
Phil      Marco
Amy       Phil

الضمّ الخارجي اليساري Left Outer Join

يضمن الضمّ الخارجي اليساري (المعروف أيضًا باسم الضمّ اليساري أو الضمّ الخارجي) تمثيل جميع صفوف الجدول الأيسر؛ وفي حال عدم وجود صفّ مطابق في الجدول الأيمن، فسيُعطى الحقل المقابل القيمةَ ‎NULL‎.

سيختار المثال التالي جميع الأقسام (departments) والأسماء الأولى للموظّفين الذين يعملون في تلك الأقسام. وستُعاد الأقسام التي لا تحتوي على أيّ موظفين، مع إعطاء اسم الموظف المقابل لها القيمة NULL:

SELECT                    Departments.Name, Employees.FName
FROM                       Departments
LEFT OUTER JOIN  Employees
ON                            Departments.Id = Employees.DepartmentId

سنحصل على الخرج التالي:

شرح الاستعلام

يوجد جدولان في عبارة FROM، وهما:

وهذا هو الجدول الثاني:

في المرحلة الأولى، يُنشأ جداء ديكارتي للجدولين، وينتج عنه جدول وسيط.

يُغلَّظُ خطّ السجلات التي تفي بشرط الضمّ (والذي هو في هذه الحالة: Departments.Id = Employees.DepartmentId)؛ وتُمرَّر إلى المرحلة التالية من الاستعلام.

لمّا كان هذا الضّمّ ضمًّا خارجيًا يساريًا (LEFT OUTER JOIN)، فستُعاد جميع السجلّات الموجودة في الجانب الأيسر من الضمّ (أي الأقسام Departments)، في حين تُعطى السجلات الموجودة على الجانب الأيمن القيمة المعدومة (NULL) في حال لم تُطابق شرط الضمّ.

بعد ذلك، تُقيّم كل التعبيرات المستخدَمة في عبارة SELECT لإعادة الجدول التالي:

الضمّ الضمني Implicit Join

يمكن أيضًا إجراء عملية الضمّ على عدّة جداول، حيث توضع في عبارة ‎from‎ مفصولة بالفاصلة ‎,‎، مع تحديد العلاقة بينها في العبارة ‎where‎. تسمى هذه التقنية "الضمّ الضمني" - Implicit Join - (لأنها لا تحتوي فعليًا العبارةَ ‎join‎).

تدعم جميع أنظمة معالجة قواعد البيانات (RDBMSs) هذه التقنية، ولكن ينصح بتجنّب استخدامها للأسباب التالية:

• قد تتداخل صياغة الضمّ الضمني مع صياغة الضمّ المتقاطع (cross join)، وهو ما قد يؤدي إلى إعادة نتائج غير صحيحة، خاصةً إذا كان الاستعلام يحتوي الكثير من عمليات الضمّ.
• إذا كنت تنوي استخدام الضم المتقاطع، فلن يكون ذلك واضحًا من الصياغة (اكتب CROSS JOIN بدلاً من ذلك)، ومن المحتمل أن يعدّلها شخص ما أثناء صيانة الشيفرة دون أن ينتبه.

سيختار المثال التالي أسماء الموظفين الأولى وكذلك أسماء الأقسام التي يعملون فيها:

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

سنحصل على الخرج التالي:

الضم المتقاطع CROSS JOIN

يُجري الضمّ المتقاطع جداءً ديكارتيًا (Cartesian product) على جدولين (الجداء الديكارتي هو عملية تُجمِّع كلّ صفّ من الجدول الأول مع كل صفّ من الجدول الثاني).

على سبيل المثال، إذا كان كلّ من الجدولين ‎TABLEA‎ و ‎TABLEB‎ يحتويان 20 صفًا، فستتألّف النتيجة المُعادة من ‎20*20 = 400‎ صفًّا.

إليك المثال التالي:

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

سنحصل على الخرج التالي:

يوصى بكتابة CROSS JOIN بشكل صريح إن أردت إجراء ضمّ ديكارتي دفعًا للُّبس.

التطبيق المتقاطع و الضم الحرفي CROSS APPLY & LATERAL JOIN

هناك نوع خاص من الضمّ يُسمّى الضمّ الحرفي LATERAL JOIN (أضيف حديثًا إلى الإصدار 9.3 وما بعده من PostgreSQL)، والذي يُعرف أيضًا باسم التطبيق المتقاطع CROSS APPLY أو التطبيق الخارجي OUTER APPLY في كلّ من SQL Server و Oracle.

الفكرة الأساسية التي ينبني عليها هذا النوع من الضمّ هي أنه سيتم تطبيق دالة (أو استعلام فرعي مضمّن - inline subquery) على كل الصفوف المضمومة.

يتيح هذا التحكم في عملية الضمّ، مثلًا يمكنك الاكتفاء بضمّ أوّل مُدخل يحقّق شرط الضمّ (matching entry) في الجدول الآخر.

يكمن الاختلاف بين الضمّ العادي والضمّ الحرفي في حقيقة أنّه يمكنك استخدام عمود سبق أن ضممته في الاستعلام الفرعي (subquery) الذي طبّقته تقاطعيًا (CROSS APPLY).

هذه صياغة الضم الحرفي.

• PostgreSQL 9.3 والإصدارات الأحدث:

left | right | inner JOIN LATERAL

• SQL Server

CROSS | OUTER APPLY

‎INNER‎ JOIN‎ LATERAL و ‎CROSS‎ APPLY‎ متكافئتان، وكذلك ‎LEFT JOIN LATERAL‎ و ‎OUTER APPLY‎

إليك المثال التالي (الإصدار 9.3 وما بعده من PostgreSQL):

SELECT * FROM T_Contacts
--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND
MAP_CTCOU_SoftDeleteStatus = 1
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989
LEFT JOIN LATERAL
(
 SELECT
              --MAP_CTCOU_UID    
              MAP_CTCOU_CT_UID  
 ,MAP_CTCOU_COU_UID  
 ,MAP_CTCOU_DateFrom
 ,MAP_CTCOU_DateTo  
 FROM T_MAP_Contacts_Ref_OrganisationalUnit
 WHERE MAP_CTCOU_SoftDeleteStatus = 1
 AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID
 /*  
    AND
    (
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
        AND
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
    )
    */
 ORDER BY MAP_CTCOU_DateFrom
 LIMIT 1
) AS FirstOE

وهذا مثال يخصّ SQL-Server:

SELECT * FROM T_Contacts
--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND
MAP_CTCOU_SoftDeleteStatus = 1
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989
-- CROSS APPLY -- = INNER JOIN
OUTER APPLY -- = LEFT JOIN
(
 SELECT TOP 1
 --MAP_CTCOU_UID    
                            MAP_CTCOU_CT_UID  
 ,MAP_CTCOU_COU_UID  
 ,MAP_CTCOU_DateFrom
 ,MAP_CTCOU_DateTo  
 FROM T_MAP_Contacts_Ref_OrganisationalUnit
 WHERE MAP_CTCOU_SoftDeleteStatus = 1
 AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID
 /*  
    AND
    (
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
        AND
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
    )
    */
 ORDER BY MAP_CTCOU_DateFrom
) AS FirstOE

الضم التام FULL JOIN

هناك نوع آخر من الضمّ أقل شهرة من غيره، وهو الضمّ التام FULL JOIN

(ملاحظة: لا تدعم MySQL الضمّ التام)

يعيد الضمّ التام الخارجي FULL OUTER JOIN جميع صفوف الجدول الأيسر، وكذلك جميع صفوف الجدول الأيمن.

ستُدرج صفوف الجدول الأيسر التي ليس لها مُطابِقَات مقابلة في الجدول الأيمن، وكذلك في الحالة المعكوسة.

إليك المثال التالي:

SELECT * FROM Table1
FULL JOIN Table2
    ON 1 = 2

وهذا مثال آخر:

SELECT
      COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear
      ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value
FROM T_Budget
FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear
      ON tYear.Year = T_Budget.Year

إن كنت تستخدم عمليات الحذف اللينة soft-deletes (والتي لا تحذف البيانات بشكل نهائي)، فسيتعيّن عليك التحقق من حالة الحذف الليّن مرة أخرى في عبارة WHERE (لأنّ سلوك الضمّ التام - FULL JOIN - يتصرف بشكل يشبه الاتحاد UNION)؛

عند إجراء الضمّ التام، سيتعيّن عليك عادةً السماح بـاستخدام القيمة المعدومة NULL في عبارة WHERE؛ وفي حال نسيت ذلك، فسيتصرّف الضمّ كما لو كان ضمًّا داخليًا (INNER join)، وهو ما لا تريده عند إجراء الضمّ التام.

إليك المثال التالي:

SELECT
  T_AccountPlan.AP_UID
 ,T_AccountPlan.AP_Code
 ,T_AccountPlan.AP_Lang_EN
 ,T_BudgetPositions.BUP_Budget
 ,T_BudgetPositions.BUP_UID
 ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    
FULL JOIN T_AccountPlan
 ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID
 AND T_AccountPlan.AP_SoftDeleteStatus = 1
WHERE (1=1)
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL)
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL)

الضم العودي Recursive JOIN

يُستخدم الضمّ العودي عادة للحصول على بيانات من نوع أب-ابن (parent-child data). في SQL، تُقدّم عمليات الضمّ العودية باستخدام تعبيرات الجدول العادية كما يوضّح المثال التالي:

WITH RECURSIVE MyDescendants AS (
 SELECT Name
 FROM People
 WHERE Name = 'John Doe'
 UNION ALL
 SELECT People.Name
 FROM People
 JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

الضم الداخلي الصريح

يستعلم الضمّ الأولي - basic join (يُسمّى أيضًا الضمّ الداخلي - inner join) عن البيانات من جدولين، حيث تُحدَّد العلاقة بينهما في عبارة ‎join‎.

يستعلم المثال التالي عن أسماء الموظفين (FName) من جدول الموظفين Employees، وأسماء الأقسام التي يعملون فيها (Name) من جدول الأقسام Departments:

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

سنحصل على الخرج التالي:

الضم في استعلام فرعي Joining on a Subquery

غالبًا ما يُستخدم الضمّ في الاستعلامات الفرعية (subquery) للحصول على بيانات مُجمّعة (aggregate data) من جدول يحتوي التفاصيل (الجدول الإبن) وعرضها جنبًا إلى جنب مع السجلات من الجدول الأصلي (الجدول الأب).

على سبيل المثال، قد ترغب في الحصول على عدد السجلات الفرعية (child records)، أو متوسط قيم عمود معيّن في السجلات الفرعية، أو الصف ذو القيمة الأكبر أو الأصغر.

يستخدم هذا المثال الكُنى (لتسهيل قراءة الاستعلامات التي تشمل عدّة جداول)، يعطي المثال فكرة عامّة عن كيفية صياغة عمليات ضمّ الاستعلامات الفرعية. إذ يعيد جميع صفوف الجدول الأصلي "Purchase Orders"، مع إعادة الصف الأول وحسب لكل سجلّ أصلي (parent record) من الجدول الفرعي PurchaseOrderLineItems.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
      item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN
 (
 SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id
 FROM PurchaseOrderLineItems l
 GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
 ) AS item ON item.PurchaseOrderId = po.Id

ترجمة -وبتصرّف- للفصل Chapter 18: JOIN من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: تحديث الجداول في SQL
• المقال السابق: البحث والتنقيب والترشيح في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0

تستعرض هذه المقالة بعض معاملات SQL المتخصصة في البحث والتنقيب وترشيح النتائج.

المعامل LIKE

مطابقة الأنماط المفتوحة Match open-ended pattern

يطابق حرف البدل ‎%‎ الموضوع في بداية أو نهاية السلسلة النصية (أو كليهما) 0 حرف أو أكثر قبل بداية أو بعد نهاية النمط المراد مطابقته.

يسمح استخدام "%' في الوسط بوجود 0 حرف أو أكثر بين جزأي النمط المُراد مُطابقته.

سنستخدم جدول الموظفين Employees التالي:

تطابق العبارة التالية جميع السجلات التي يحتوي حقل FName خاصتها على السلسلة النصية 'on':

SELECT * FROM Employees WHERE FName LIKE '%on%';

سنحصل على الخرج التالي:

يطابق التعبير التالي جميع السجلات التي يبدأ الحقل PhoneNumber خاصتها بالسلسلة النصية "246" في جدول الموظفين.

SELECT * FROM Employees WHERE PhoneNumber LIKE '246%';

سنحصل على الخرج التالي:

تطابق العبارة التالية جميع السجلات التي ينتهي الحقل PhoneNumber خاصتها بالسلسلة النصية "11" في جدول الموظفين.

SELECT * FROM Employees WHERE PhoneNumber LIKE '%11'

يطابق التعبير التالي جميع السجلات التي يساوي الحرف الثالث من حقل Fname خاصتها 'n' في جدول الموظفين.

SELECT * FROM Employees WHERE FName LIKE '__n%';

(استخدمنا شرطتين سفليتين قبل 'n' لتخطي أول حرفين)

مطابقة محرف واحد

يمكن استخدام أحرف البدل وعلامة النسبة المئوية (%) والشرطة السفلية (_) لتوسيع أنماط الاختيار في SQL.

يمكن استخدام المحرف ‎_‎ (الشرطة السفلية) كحرف بدل يمثل حرفًا منفردًا.

يبحث النمط التالي عن جميع الموظفين الذين يتألف الحقل Fname خاصتهم من 3 حروف، ويبدأ بالحرف "j" وينتهي بـ "n".

SELECT * FROM Employees WHERE FName LIKE 'j_n'

يمكن استخدام المحرف ‎_‎ أكثر من مرة بحيث يتصرف كبطاقة بدل (wild card) لمطابقة أنماط معينة.

على سبيل المثال، يُطابق النمط أعلاه السلاسل النصية التالية: jon و jan و jen. بيْد أنّه لا يُطابق الأسماء التالية: jn و john و jordan و justin و jason و julian و jillian و joann، لأنّ الشرطة السفلية التي استخدمناها في الاستعلام تتخطى حرفًا واحدًا فقط، لذلك لن تُقبل إلا الحقول المؤلفة من 3 أحرف.

يُطابق النمط التالي السلاسل النصية التالية: LaSt و LoSt و HaLt:

SELECT * FROM Employees WHERE FName LIKE '_A_T'

العبارة ESCAPE في الاستعلام LIKE

يمكن إجراء بحث نصي في العبارة ‎LIKE‎ على النحو التالي:

SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%')

ستحدث مشكلة إذا أدخل شخص ما نصُّا من قبيل "50%" أو "a_b" (بصرف النظر عن حقيقة أنه يُفضل البحث عن النص الكامل بدل استخدام ‎LIKE‎). يمكن حل هذه المشكلة باستخدام عبارة ‎LIKE‎:

SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%') ESCAPE '\'

هذا يعني أنّ ‎\‎ ستُعامل كحرف التهريب ESCAPE. أي أنّه يمكنك الآن إضافة ‎\‎ إلى كل حرف في السلسلة النصية التي تبحث عنها، وستكون النتائج صحيحة، حتى لو أدخل المستخدم محارف خاصة مثل ‎%‎ أو ‎_‎.

إليك المثال التالي:

string stringToSearch = "abc_def 50%";
string newString = "";
foreach(char c in stringToSearch)
     newString += @"\" + c;

sqlCmd.Parameters.Add("@in_SearchText", newString);
// sqlCmd.Parameters.Add("@in_SearchText", stringToSearch); بدلا من

ملاحظة: وضعنا الخوارزمية أعلاه للتوضيح وحسب، ولن تعمل في حال احتوت السلسلة النصية على رسمة (grapheme) مؤلفة من عدّة أحرف (مثل رموز utf-8). مثلًا، في السلسلة النصية ‎string stringToSearch = "Les Mise\u0301rables";‎، ستحتاج إلى فعل ذلك لكل رسمة، وليس لكل حرف. عليك ألا تستخدم الخوارزمية أعلاه إذا كنت تتعامل مع لغات آسيوية أو شرق آسيوية أو جنوب آسيوية.

البحث عن مجموعة من المحارف

تطابق العبارة التالية جميع السجلات التي يبدأ حقل FName خاصتها بحرف محصور (أبجديا) بين A و F في جدول الموظفين.

SELECT * FROM Employees WHERE FName LIKE '[A-F]%'

مطابقة نطاق أو مجموعة

يمكن مطابقة حرف واحد داخل نطاق محدد (على سبيل المثال: ‎[a-f]‎) أو مجموعة (على سبيل المثال: ‎[abcdef]‎).

يطابق نمط النطاق التالي السلسلة النصية gary، ولكن ليس mary:

SELECT * FROM Employees WHERE FName LIKE '[a-g]ary'

يُطابق نمط المجموعة التالي mary ولكن ليس gary:

SELECT * FROM Employees WHERE Fname LIKE '[lmnop]ary'

يمكن أيضًا عكس أو نفي النطاق أو المجموعة بوضع العلامة ‎^‎ قبل النطاق أو المجموعة، فلن يتطابق نمط النطاق التالي مع gary، ولكنه سيتطابق مع mary:

SELECT * FROM Employees WHERE FName LIKE '[^a-g]ary'

لن يتطابق نمط المجموعة التالي مع mary ولكن سيتطابق مع gary:

SELECT * FROM Employees WHERE Fname LIKE '[^lmnop]ary'

أحرف البدل Wildcard characters

يمكن استخدام أحرف البدل مع المعامل LIKE. تُستخدم أحرف البدل في SQL للبحث عن البيانات داخل جدول معيّن. وهناك أربعة منها، وهي كالتالي:

• % - بديل عن صفر حرف أو أكثر

--   "Lo" اختيار جميع العملاء الذين يقطنون مدينة تبدأ بـ 
 SELECT * FROM Customers
 WHERE City LIKE 'Lo%';

-- "es" اختيار جميع العملاء الذين يقطنون مدينة تحتوي 
 SELECT * FROM Customers
 WHERE City LIKE '%es%';

• _ - بديل عن حرف واحد

-- erlin  اختيار جميع العملاء الذين يقطنون مدينة تبدأ بحرف معين، متبوعا بـ 
SELECT * FROM Customers
WHERE City LIKE '_erlin';

• [charlist] - مجموعات ونطاقات مؤلفة من الحروف المُراد مُطابقتها

-- "a" أو "d" أو "l" اختيار جميع العملاء الذين يقطنون مدينة تبدأ بـ 
SELECT * FROM Customers
WHERE City LIKE '[adl]%';
-- "a" أو "d" أو "l" اختيار جميع العملاء الذين يقطنون مدينة تبدأ بـ 
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

• [‎^ charlist] - تطابق الحروف غير الموجودة داخل القوسين المربعين

-- "a" أو "d" أو "l" اختيار جميع العملاء الذين يقطنون مدينة لا تبدأ بـ 
SELECT * FROM Customers
WHERE City LIKE '[^apl]%';
or
SELECT * FROM Customers
WHERE City NOT LIKE '[apl]%' and city like '_%';

التحقق من الانتماء عبر IN

يمكن استخدام العبارة IN للتحقق من إنتماء قيمة إلى مجموعة معينة.

تعيد الشيفرة التالية السجلات التي ينتمي مُعرّفها ‎id‎ إلى مجموعة معينة من القيم ((1,8,3)):

select *
from products
where id in (1,8,3)

يكافئ الاستعلام أعلاه:

select *
from products
where id = 1
 or id = 8
 or id = 3

يمكن استخدام IN مع استعلام فرعي على النحو التالي:

SELECT *
FROM customers
WHERE id IN (
 SELECT DISTINCT customer_id
 FROM orders
);

ستعيد الشيفرة أعلاه جميع العملاء الذين لديهم طلبات في النظام.

ترشيح النتائج باستخدام WHERE و HAVING

استخدم BETWEEN لترشيح النتائج

تستخدم الأمثلة التالية قاعدتي البيانات Sales و Customers.

تذكر أنّ المعامل BETWEEN تضميني (inclusive):

استخدام المعامل BETWEEN مع الأعداد

يعيد الاستعلام التالي جميع سجلات ‎ItemSales‎ التي ينحصر حقل الكمية quantity خاصتها بين 10 و 17.

SELECT * From ItemSales
WHERE Quantity BETWEEN 10 AND 17

سنحصل على النتائج التالية:

استخدام المعامل BETWEEN مع قيم التاريخ

يعيد الاستعلام التالي كافة سجلات ‎ItemSales‎ التي ينحصر حقل ‎SaleDate‎ خاصتها بين التاريخين 11 يوليو 2013 و 24 مايو 2013.

SELECT * From ItemSales
WHERE SaleDate BETWEEN '2013-07-11' AND '2013-05-24'

هذا هو الخرج المتوقع:

عند موازنة قيم الوقت (datetime) بدلًا من قيم التاريخ (dates)، قد تحتاج إلى تحويل قيم الوقت إلى قيم التاريخ، أو إضافة أو طرح 24 ساعة للحصول على النتيجة المتوقعة.

استخدام المعامل BETWEEN مع القيم النصية

يعيد الاستعلام التالي كافة العملاء الذين تنحصر أسماؤهم (أبجديًا) بين الحرفين 'D' و 'L'. في هذه الحالة، سيُعاد العميلان ذوي الرقمين 1 و 3. أما العميل رقم 2 ، الذي يبدأ اسمه بـالحرف "M"، فلن يُعاد:

SELECT Id, FName, LName FROM Customers
WHERE LName BETWEEN 'D' AND 'L';

هذا مثال حي

الخرج:

استخدم HAVING مع الدوال التجميعية

على خلاف العبارة ‎WHERE‎ ، يمكن استخدام ‎HAVING‎ مع الدوال التجميعية.

الدوال التجميعية (aggregate functions) هي دوال تأخذ القيم الموجودة في في عدة صفوف كمُدخلات (بناء على شروط محددة) وتعيد قيمة معينة. هذه بعض الدوال التجميعية: ‎COUNT()‎ و ‎SUM()‎ و ‎MIN()‎ و ‎MAX()‎.

يستخدم هذا المثال الجدول Car من الفصل الأول.

SELECT CustomerId, COUNT(Id) AS [Number of Cars]
FROM Cars
GROUP BY CustomerId
HAVING COUNT(Id) > 1

يعيد الاستعلام أعلاه ‎CustomerId‎ وعدد السيارات ‎Number of Cars‎ لأيّ عميل لديه أكثر من سيارة واحدة. في هذا المثال، العميل الوحيد الذي لديه أكثر من سيارة واحدة هو العميل رقم 1.

هذا هو الخرج:

استخدام WHERE مع القيم NULL / NOT NULL

يعيد المثال التالي جميع سجلات الموظفين (Employee) التي تتساوى قيمة العمود ‎ManagerId‎ خاصتهم مع القيمة المعدومة ‎NULL‎.

SELECT *
FROM Employees
WHERE ManagerId IS NULL

النتيجة:

Id    FName    LName    PhoneNumber    ManagerId    DepartmentId
1     James     Smith      1234567890        NULL           1

يعيد المثال التالي جميع سجلات الموظفين التي لا تساوي قيمة العمود ‎ManagerId‎ خاصتهم القيمة ‎NULL‎.

SELECT *
FROM Employees
WHERE ManagerId IS NOT NULL

ستكون النتيجة كما يلي:

Id    FName       LName     PhoneNumber    ManagerId    DepartmentId
2     John           Johnson    2468101214      1                    1
3     Michael       Williams   1357911131       1                    2
4     Johnathon   Smith       1212121212       2                   1

ملاحظة: لن يعيد الاستعلام أعلاه أيّ نتائج في حال غيّرت صياغة العبارة WHERE إلى ‎WHERE ManagerId = NULL‎ أو ‎WHERE‎ ‎ManagerId‎ <> NULL.

معامل التساوي

تعيد الشيفرة التالية كل صفوف الجدول ‎Employees‎.

SELECT * FROM Employees

الخرج:

Id   FName     LName    PhoneNumber   ManagerId   DepartmentId    Salary  Hire_date CreatedDate   ModifiedDate
1    James     Smith        1234567890      NULL          1                        1000     01-01-2002  01-01-2002  01-01-2002
2    John       Johnson     2468101214      1                1                        400       23-03-2005   23-03-2005  01-01-2002
3    Michael   Williams    1357911131      1                  2                        600      12-05-2009   12-05-2009  NULL
4    Johnathon Smith      1212121212      2                 1                        500       24-07-2016   24-07-2016  01-01-2002

يتيح لك استخدام ‎WHERE‎ في نهاية العبارة ‎SELECT‎ ترشيح الصفوف المُعادة وفق شرط معين. إن أردت مثلًا اشتراط التطابق التام مع قيمة معينة، فاستخدم علامة التساوي ‎=‎:

SELECT * FROM Employees WHERE DepartmentId = 1

لن يعيد الاستعلام أعلاه إلا الصفوف التي يساوي الحقل ‎DepartmentId‎ خاصتها القيمة ‎1‎:

Id   FName     LName    PhoneNumber   ManagerId   DepartmentId    Salary  Hire_date       CreatedDate   ModifiedDate
1    James       Smith      1234567890       NULL          1                       1000      01-01-2002    01-01-2002    01-01-2002
2    John          Johnson  2468101214       1                 1                       400        23-03-2005    23-03-2005    01-01-2002
4    Johnathon Smith       1212121212       2                 1                       500        24-07-2016    24-07-2016     01-01-2002

لنفترض أنّ متجرًا للألعاب لديه فئة من الألعاب يقل سعرها عن 10 دولارات. يعيد الاستعلام التالي هذه الفئة من الألعاب:

SELECT *
FROM Items
WHERE Price < 10

المعاملان المنطقيان AND و OR

يمكنك الجمع بين عدة معاملات معًا لإنشاء شروط ‎WHERE‎ أكثر تعقيدًا.

تستخدم الأمثلة التالية الجدول ‎Employees‎ التالي:

Id   FName     LName    PhoneNumber   ManagerId   DepartmentId    Salary    Hire_date       CreatedDate   ModifiedDate
1    James       Smith      1234567890      NULL           1                       1000        01-01-2002    01-01-2002    01-01-2002
2    John         Johnson   2468101214      1                  1                       400         23-03-2005    23-03-2005     01-01-2002
3    Michael     Williams  1357911131       1                   2                       600         12-05-2009    12-05-2009    NULL
4    Johnathon  Smith     1212121212       2                   1                       500         24-07-2016    24-07-2016    01-01-2002

إليك الاستعلام التالي:

SELECT * FROM Employees WHERE DepartmentId = 1 AND ManagerId = 1

سينتج الخرج التالي:

Id   FName     LName    PhoneNumber   ManagerId   DepartmentId    Salary  Hire_date        CreatedDate   ModifiedDate
2    John        Johnson   2468101214      1                   1                       400         23-03-2005    23-03-2005    01-01-2002

وهذا استعلام آخر يستخدم المعامل المنطقي OR:

SELECT * FROM Employees WHERE DepartmentId = 2 OR ManagerId = 2

سينتج الخرج التالي:

Id   FName     LName    PhoneNumber   ManagerId   DepartmentId    Salary  Hire_date       CreatedDate   ModifiedDate
3    Michael     Williams   1357911131      1                  2                       600         12-05-2009    12-05-2009   NULL
4    Johnathon Smith      1212121212      2                  1                       500         24-07-2016     24-07-2016   01-01-2002

استخدم IN لإعادة الصفوف التي تنتمي قيمها إلى قائمة معينة

يستخدم هذا المثال الجدول "Car".

SELECT *
FROM Cars
WHERE TotalCost IN (100, 200, 300)

سيعيد هذا الاستعلام السيارة رقم 2، والتي تبلغ تكلفتها 200، والسيّارة رقم 3، والتي تساوي تكلفتها 100. لاحظ أنّ الاستعلام أعلاه يكافئ استخدام ‎OR‎ عدة مرّات كما هو موضّح في المثال التالي:

SELECT *
FROM Cars
WHERE TotalCost = 100 OR TotalCost = 200 OR TotalCost = 300

استخدم LIKE للبحث عن السلاسل النصية

يستخدم المثال التالي الجدول Car:

SELECT *
FROM Employees
WHERE FName LIKE 'John'

لن يعيد هذا الاستعلام إلا الموظف رقم 1، والذي يتطابق اسمه الأول مع السلسلة النصية "John".

SELECT *
FROM Employees
WHERE FName like 'John%'

يمكنك البحث عن سلسلة نصية فرعية عبر إضافة الرمز ‎%‎:

• John%‎‎ - تعيد أيّ موظف يبدأ اسمه بـ "John" ، متبوعًا بأي عدد من الأحرف
• ‎‎%John‎‎ - تعيد أيّ موظف ينتهي اسمه بـ "John" ، يعقبه أي عدد من الأحرف
• ‎%‎John‎%‎ - تعيد أيّ موظف يتضمّن اسمه السلسلة النصية "John"

في المثال أعلاه، سيعيد الاستعلام الموظفَ رقم 2، والذي يحمل الاسم "John"، وكذلك الموظف رقم 4، والذي يحمل الاسم "Johnathon".

Where EXISTS

في المثال التالي، تختار العبارة WHERE EXISTS سجلّات ‎TableName‎ التي تطابق سجلاتٍ في الجدول ‎TableName1‎.

SELECT * FROM TableName t WHERE EXISTS (
 SELECT 1 FROM TableName1 t1 where t.Id = t1.Id)

استخدام HAVING للتحقق من عدة شروط

إليك جدول الطلبات التالي:

للحصول على العملاء الذين طلبوا المنتَجَين ذوي المُعرّف 2 و 3، يمكن استخدام العبارة HAVING:

 select customerId
 from orders
 where productID in (2,3)
 group by customerId
 having count(distinct productID) = 2

الخرج الناتج:

لن يختار الاستعلام إلا السجلات ذات معرّفات المنتجات المحددة، والتي تحقق شرط HAVING، أي وجود معرّفين اثنين للمنتجات (productIds)، وليس معرّفًا واحدًا فقط.

هذه صياغة أخرى:

 select customerId
 from orders
 group by customerId
 having sum(case when productID = 2 then 1 else 0 end) > 0
     and sum(case when productID = 3 then 1 else 0 end) > 0

لن يختار هذا الاستعلام إلا المجموعات التي لها سجل واحد على الأقل يساوي معرّف منتجه (productID) القيمة 2، وسجل واحد على الأقل يساوي معرّف منتجه 3.

ترقيم الصفحات Pagination

يمكن وضع حدّ لعدد النتائج المُعادة في استعلام معين، لكنّ الصياغة تختلف بحسب النظام المُستخدم:

• في إصدار SQL القياسي ISO / ANSI:

SELECT * FROM TableName FETCH FIRST 20 ROWS ONLY;

• MySQL و PostgreSQL و SQLite:

SELECT * FROM TableName LIMIT 20;

• Oracle :

SELECT Id,
   Col1
FROM (SELECT Id,
           Col1,
 row_number() over (order by Id) RowNumber
 FROM TableName)
WHERE RowNumber <= 20

• SQL Server:

SELECT TOP 20 *
FROM dbo.[Sale]

قد ترغب أحيانًا في تخطّي عدد من نتائج الاستعلام وتأخذ النتائج الموالية لها، يمكنك ذلك عبر الصياغة التالية:

• ISO / ANSI SQL:

SELECT Id, Col1
FROM TableName
ORDER BY Id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

• MySQL:

SELECT * FROM TableName LIMIT 20, 20; -- offset, limit

• Oracle و SQL Server:

SELECT Id,
   Col1
 FROM (SELECT Id,
           Col1,
 row_number() over (order by Id) RowNumber
 FROM TableName)
WHERE RowNumber BETWEEN 21 AND 40