SQL Logical Operators are used to combine multiple conditions in a WHERE clause. They help refine query results by allowing you to set more specific criteria.
Examples of SQL Logical Operators:
1. AND
- The
ANDoperator is used when you want all the conditions to be true.
Syntax:
SELECT column_names
FROM table_name
WHERE condition1 AND condition2;Example:
SELECT * FROM Employees
WHERE salary > 50000 AND department = 'Sales';This query retrieves employees who have a salary greater than 50,000 and work in the Sales department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 1 | John | 60000 | Sales |
| 3 | Alice | 70000 | Sales |
2. OR
- The
ORoperator is used when you want at least one of the conditions to be true.
Syntax:
SELECT column_names
FROM table_name
WHERE condition1 OR condition2;Example:
SELECT * FROM Employees
WHERE department = 'Sales' OR department = 'HR';This query retrieves employees who work in either the Sales or HR department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 1 | John | 60000 | Sales |
| 2 | Sarah | 45000 | HR |
| 3 | Alice | 70000 | Sales |
3. NOT
- The
NOToperator is used to negate a condition.
Syntax:
SELECT column_names
FROM table_name
WHERE NOT condition;Example:
SELECT * FROM Employees
WHERE NOT department = 'Sales';This query retrieves all employees except those in the Sales department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 2 | Sarah | 45000 | HR |
| 4 | Mike | 55000 | IT |
4. BETWEEN
The BETWEEN operator is used to filter results within a certain range.
This range can be numeric, date, or text values. The BETWEEN operator is inclusive, meaning it includes the boundary values.
Syntax:
SELECT column_names
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Example:
SELECT * FROM Employees
WHERE salary BETWEEN 40000 AND 60000;
-- OR
WHERE salary >= 40000 AND salary <= 60000
-- We can use it with NOTThis query retrieves employees whose salaries are between 40,000 and 60,000 (inclusive).
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 2 | Sarah | 45000 | HR |
| 4 | Mike | 55000 | IT |
Use Cases:
- Numeric Range:
BETWEEN 10 AND 100 - Date Range:
BETWEEN '2023-01-01' AND '2023-12-31' - Text Range:
BETWEEN 'A' AND 'M'(alphabetically)
5. IN
The IN operator allows you to specify multiple values in a WHERE clause. It’s a shorthand for multiple OR conditions.
Syntax:
SELECT column_names
FROM table_name
WHERE column_name IN (value1, value2, ...);Example:
SELECT * FROM Employees
WHERE department IN ('Sales', 'HR');
-- OR
WHERE department = 'Sales' OR department = 'HR';This query retrieves employees who work in either the Sales or HR department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 1 | John | 60000 | Sales |
| 2 | Sarah | 45000 | HR |
Use Cases:
- Checking for multiple possible values:
IN ('Value1', 'Value2', ...) - For date comparisons:
IN ('2023-01-01', '2023-01-02')
6. NOT IN
The NOT IN operator is used to exclude multiple values. It’s the opposite of the IN operator and returns records that do not match the specified list of values.
Syntax:
SELECT column_names
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);Example:
SELECT * FROM Employees
WHERE department NOT IN ('Sales', 'HR');This query retrieves employees who do not work in either the Sales or HR department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 3 | Alice | 70000 | IT |
| 4 | Mike | 55000 | IT |
Use Cases:
- Exclude specific values:
NOT IN (10, 20, 30) - Exclude multiple departments, dates, or other values.
7. LIKE
The LIKE operator is used to search for a specified pattern in a column.
It’s often used with wildcards to match strings that follow a particular pattern.
%: Represents zero or more characters._: Represents a single character.
Syntax:
SELECT column_names
FROM table_name
WHERE column_name LIKE pattern;Example:
SELECT * FROM Employees
WHERE employee_name LIKE 'A%';
-- Another
SELECT DISTINCT CITY
FROM STATION
WHERE CITY Like 'a%'
OR CITY LIKE 'e%'
OR CITY LIKE 'i%'
OR CITY LIKE 'o%'
OR CITY LIKE 'u%';This query retrieves all employees whose names start with the letter “A”.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 3 | Alice | 70000 | IT |
Example with _:
SELECT * FROM Employees
WHERE employee_name LIKE '_o%';This query retrieves all employees whose names have “o” as the second letter (e.g., “John”, “Bob”).
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 1 | John | 60000 | Sales |
| 5 | Bob | 45000 | HR |
Use Cases:
- Matching starts with specific characters:
LIKE 'A%' - Matching ends with specific characters:
LIKE '%ing' - Matching any character in a position:
LIKE '_a%'(second character is ‘a’)
Wildcards
1. % (نسبة مئوية)
- الوصف: يطابق أي عدد من الأحرف (بما في ذلك صفر).
- الاستخدام: يُستخدم للبحث عن تطابقات تحتوي على جزء معين من النص في أي مكان.
أمثلة:
-
البحث عن أسماء تبدأ بـ “J”:
SELECT employee_name FROM Employees WHERE employee_name LIKE 'J%'; -
البحث عن أسماء تحتوي على “ohn” في أي مكان:
SELECT employee_name FROM Employees WHERE employee_name LIKE '%ohn%';
2. _ (الشرطة السفلية)
- الوصف: يطابق حرف واحد فقط.
- الاستخدام: يُستخدم عند الحاجة للبحث عن حرف واحد فقط في مكان معين داخل النص.
أمثلة:
-
البحث عن أسماء تحتوي على حرفين قبل “an”:
SELECT employee_name FROM Employees WHERE employee_name LIKE '__an';هذا سيطابق مثل “Dan” أو “Jan” ولكن ليس “John” لأن هناك أكثر من حرف في “John”.
3. [] (القوسين المعقفين)
- الوصف: يُستخدم لتحديد مجموعة من الأحرف التي يمكن أن تتطابق مع مكان معين.
- الاستخدام: يُستخدم للبحث عن حرف واحد من مجموعة محددة من الأحرف.
أمثلة:
-
البحث عن أسماء تحتوي على حرف من “A” أو “B” في بداية الاسم:
SELECT employee_name FROM Employees WHERE employee_name LIKE '[AB]%'; -
البحث عن أسماء تحتوي على حرف من “1” إلى “5”:
SELECT employee_name FROM Employees WHERE employee_name LIKE '[1-5]%';
4. [^] (القوسين المعقفين مع علامة العطف)
- الوصف: يُستخدم لاستبعاد مجموعة من الأحرف.
- الاستخدام: يُستخدم للبحث عن حرف واحد لا ينتمي إلى مجموعة معينة من الأحرف.
أمثلة:
-
البحث عن أسماء لا تبدأ بحرف “A” أو “B”:
SELECT employee_name FROM Employees WHERE employee_name LIKE '[^AB]%';
5. - (الشرطة) في القوسين المعقفين
- الوصف: يُستخدم لتحديد نطاق من الأحرف داخل القوسين.
- الاستخدام: يُستخدم للبحث عن حرف يقع ضمن نطاق معين داخل مجموعة من الأحرف.
أمثلة:
-
البحث عن أسماء تحتوي على حرف من “a” إلى “f”:
SELECT employee_name FROM Employees WHERE employee_name LIKE '[a-f]%';
ملاحظات إضافية:
- الـ**
LIKE** عادة ما تكون غير حساسة لحالة الأحرف في بعض أنظمة قواعد البيانات مثل MySQL و PostgreSQL (حسب الإعدادات الافتراضية)، بينما في SQL Server قد تكون حساسة لحالة الأحرف اعتمادًا على collation (التنسيق المحلي). %و_هما الأكثر استخدامًا في أغلب الاستعلامات، بينما[]و[^]تُستخدم لتحديد تطابقات أكثر تخصيصًا.
خلاصة:
%: يطابق أي عدد من الأحرف (بما في ذلك صفر)._: يطابق حرفًا واحدًا فقط.[]: يطابق أي حرف من مجموعة معينة.[^]: يطابق أي حرف ليس في مجموعة معينة.-: يُستخدم لتحديد نطاق من الأحرف داخل القوسين المعقفين.
Combining Logical Operators
You can combine logical operators to create more complex conditions.
Example:
SELECT * FROM Employees
WHERE salary > 50000 AND (department = 'Sales' OR department = 'HR');This query retrieves employees who have a salary greater than 50,000 and work either in the Sales or HR department.
Output:
| employee_id | employee_name | salary | department |
|---|---|---|---|
| 1 | John | 60000 | Sales |
| 3 | Alice | 70000 | Sales |
Conclusion
ANDensures all conditions are true.ORensures at least one condition is true.NOTnegates a condition.BETWEEN: Useful for checking if a value lies within a specific range.IN: Checks if a value matches any value in a list.NOT IN: Excludes values that match any value in a list.LIKE: Used for pattern matching with wildcards for text data.