SQL Conditional Clauses! 📊

By /

SQL offers several conditional clauses that help filter data based on specific criteria. These clauses include IS NULL, LIKE, IN, BETWEEN, NOT BETWEEN, and combinations such as BETWEEN with IN.

Key Points and Examples:

1. IS NULL

The IS NULL clause is used to check for null values in a column.

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

💡 Example: Retrieve all employees with no assigned department:

SELECT *
FROM Employees
WHERE DepartmentID IS NULL;

2. LIKE

The LIKE clause is used for pattern matching in a string.

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

💡 Example: Retrieve all employees whose names start with ‘J’:

SELECT *
FROM Employees
WHERE FirstName LIKE 'J%';

🔍 In-Depth Guide to SQL LIKE Clause 🔍

The LIKE clause in SQL is used for pattern matching in text data. It’s a powerful tool for querying strings based on specific patterns, making it essential for text-based searches within your database.

Key Points:

  • Wildcard Characters: The LIKE clause uses two primary wildcard characters:
    • % (percent): Matches zero or more characters.
    • _ (underscore): Matches exactly one character.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Examples:

Matching Patterns with %

  • Start with a specific string: Retrieve all employees whose names start with ‘J’:
SELECT * FROM Employees WHERE FirstName LIKE 'J%';

This matches names like ‘John’, ‘Jane’, ‘Jack’, etc.

  • End with a specific string: Retrieve all employees whose names end with ‘n’
SELECT * FROM Employees WHERE FirstName LIKE '%n';

This matches names like ‘John’, ‘Megan’, ‘Brian’, etc.

  • Contain a specific substring: Retrieve all employees whose names contain ‘an’: 
SELECT * FROM Employees WHERE FirstName LIKE '%an%';

This matches names like ‘Megan’, ‘Ryan’, ‘Nancy’, etc.

Matching Patterns with _

  • Single character match: Retrieve all employees whose names have ‘a’ as the second letter: 
SELECT * FROM Employees WHERE FirstName LIKE '_a%';

This matches names like ‘James’, ‘Karen’, ‘Adam’, etc.

Combining % and _

  • Complex patterns: Retrieve all employees whose names start with ‘J’ and are at least 3 characters long: 
SELECT * FROM Employees WHERE FirstName LIKE 'J__%';

This matches names like ‘Jack’, ‘John’, ‘Jane’, etc.

Escaping Wildcards

If you need to search for a literal % or _, use the escape character (often '\'):

  • Literal % or _ search: Retrieve all employees whose names contain a literal underscore: 
SELECT * FROM Employees WHERE FirstName LIKE '%\_%' ESCAPE '\';

This matches names like ‘A_B’, ‘C_D’, etc.

Practical Scenarios for Using LIKE:

  • Search in a Table: When you need to find records with a specific pattern in a text column, such as searching for email domains:
SELECT * FROM Users WHERE Email LIKE '%@gmail.com';
  • Partial Matches: Useful for finding records with partial matches, such as product names containing a specific word:
SELECT * FROM Products WHERE ProductName LIKE '%Laptop%';
  • Dynamic Searches: Building dynamic search queries in applications where users can search for records with partial information:
SELECT * FROM Customers WHERE LastName LIKE 'Sm%';

Summary:

  • Purpose: The LIKE clause is used for pattern matching in text data.
  • Wildcards:
    • %: Matches zero or more characters.
    • _: Matches exactly one character
Syntax:SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
  • Examples:
    • LIKE 'J%': Matches any string starting with ‘J’.
    • LIKE '%an%': Matches any string containing ‘an’.
    • LIKE '_a%': Matches any string with ‘a’ as the second character.
    • LIKE '%\_%' ESCAPE '\': Matches any string containing a literal underscore.

By mastering the LIKE clause, you can perform complex text-based searches and pattern matching efficiently in your SQL queries!

3. IN

The IN clause is used to filter results based on a set of values.

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

💡 Example: Retrieve all employees who work in departments 101, 102, or 103:

SELECT *
FROM Employees
WHERE DepartmentID IN (101, 102, 103);

4. BETWEEN

The BETWEEN clause filters results within a specific range, inclusive of the boundary values.

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

💡 Example: Retrieve all employees with salaries between 50000 and 100000:

SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 100000;

5. NOT BETWEEN

The NOT BETWEEN clause filters results outside a specific range.

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

💡 Example: Retrieve all employees with salaries not between 50000 and 100000:

SELECT *
FROM Employees
WHERE Salary NOT BETWEEN 50000 AND 100000;

6. BETWEEN with IN

Combining BETWEEN with IN helps filter results within a specific range and set of values.

💡 Example: Retrieve all employees with salaries between 50000 and 100000 who work in departments 101 or 102:

SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 100000
AND DepartmentID IN (101, 102);

Summary:

  • IS NULL: Checks for null values.WHERE column_name IS NULL;
  • LIKE: Used for pattern matching.WHERE column_name LIKE 'pattern';
  • IN: Filters results based on a set of values.WHERE column_name IN (value1, value2, ...);
  • BETWEEN: Filters results within a specific range.WHERE column_name BETWEEN value1 AND value2;
  • NOT BETWEEN: Filters results outside a specific range.WHERE column_name NOT BETWEEN value1 AND value2;
  • BETWEEN with IN: Combines range filtering with a set of specific values.WHERE column_name BETWEEN value1 AND value2 AND another_column IN (value1, value2, ...);

By mastering these SQL conditional clauses, you can effectively filter and manipulate your data to retrieve precise and meaningful results!

Related Posts

Scroll to Top