Example Codes for Selecting from List of Values in SQL Server

Understanding the Concept:

• List of values: A collection of specific values that you want to filter data from a table.
• SELECT query: A SQL statement used to retrieve data from a table based on specified criteria.

Key Methods:

1. IN Clause:

   • Syntax:

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

   • Explanation:
     • The IN clause compares a column value to a list of values.
     • If the column value matches any of the values in the list, the row is included in the result set.
   • Example:

     SELECT *
     FROM Customers
     WHERE Country IN ('USA', 'Canada', 'Mexico');
     

     This query selects all customers from the "Customers" table who reside in the United States, Canada, or Mexico.

2. EXISTS Subquery:

   • Syntax:

     SELECT column1, column2, ...
     FROM table1
     WHERE EXISTS (
       SELECT 1
       FROM table2
       WHERE table1.column_name = table2.column_name
       AND table2.column_name IN (value1, value2, ...)
     );
     

   • Explanation:
     • The EXISTS subquery checks if a correlated subquery returns any rows.
     • If the subquery returns at least one row, the outer query includes the corresponding row.
   • Example:

     SELECT OrderID, CustomerID
     FROM Orders
     WHERE EXISTS (
       SELECT 1
       FROM OrderDetails
       WHERE Orders.OrderID = OrderDetails.OrderID
       AND ProductID IN (100, 101, 102)
     );
     

     This query selects all orders from the "Orders" table that have at least one order detail with a product ID of 100, 101, or 102.

3. JOIN with a Values Table:

   • Syntax:

     SELECT column1, column2, ...
     FROM table_name
     JOIN (VALUES (value1), (value2), ...) AS values_table(column_name)
     ON table_name.column_name = values_table.column_name;
     

   • Explanation:
     • A temporary table (values_table) is created with the list of values.
     • The JOIN clause is used to match values between the main table and the temporary table.
   • Example:

     SELECT OrderID, CustomerID
     FROM Orders
     JOIN (VALUES ('USA'), ('Canada'), ('Mexico')) AS countries(Country)
     ON Orders.Country = countries.Country;
     

     This query selects all orders from the "Orders" table where the country is one of the specified countries.

Choosing the Right Method:

• IN clause: Simple and efficient for filtering based on a fixed list of values.
• EXISTS subquery: Useful when you need to check for existence in a related table.
• JOIN with a values table: Provides flexibility for dynamic lists of values.

Example Codes for Selecting from List of Values in SQL Server

IN Clause

• Scenario: Retrieve customers from specific countries.
• Code:

SELECT CustomerID, CustomerName, Country
FROM Customers
WHERE Country IN ('USA', 'Canada', 'Mexico');

• Explanation:
  • The IN clause checks if the Country column matches any of the specified values.
  • Returns rows where the country is 'USA', 'Canada', or 'Mexico'.

• Scenario: Find orders that include a specific product.

SELECT OrderID, OrderDate
FROM Orders
WHERE EXISTS (
  SELECT 1
  FROM OrderDetails
  WHERE Orders.OrderID = OrderDetails.OrderID
  AND ProductID = 100
);

• Explanation:
  • The EXISTS subquery checks if there's at least one order detail for the current order with a product ID of 100.
  • Returns orders with at least one order detail for product 100.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN (VALUES (1), (2), (3)) AS Categories(CategoryID)
ON Products.CategoryID = Categories.CategoryID;

• Explanation:
  • A temporary table Categories is created with the specified category IDs.
  • The JOIN clause matches products with categories based on their IDs.
  • Returns products from categories 1, 2, or 3.

Using a Variable

• Scenario: Dynamically specify the list of values.

DECLARE @ProductIDs NVARCHAR(MAX) = '100,101,102';

SELECT ProductID, ProductName
FROM Products
WHERE ProductID IN (SELECT value FROM STRING_SPLIT(@ProductIDs, ','));

• Explanation:
  • A variable @ProductIDs holds a comma-separated list of product IDs.
  • The STRING_SPLIT function splits the list into individual values.

CASE Expression

• Scenario: Conditional selection based on multiple values.

SELECT CustomerID, CustomerName,
       CASE WHEN Country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
            WHEN Country IN ('France', 'Germany', 'Spain') THEN 'Europe'
            ELSE 'Other'
       END AS Region
FROM Customers;

• Explanation:
  • The CASE expression evaluates conditions and returns a corresponding value.
  • Groups customers by region based on their country.

Common Table Expressions (CTEs)

• Scenario: Complex filtering or calculations involving multiple queries.

WITH ProductCategories AS (
  SELECT ProductID, CategoryID
  FROM Products
  WHERE CategoryID IN (1, 2, 3)
)
SELECT Products.ProductID, Products.ProductName, ProductCategories.CategoryID
FROM Products
JOIN ProductCategories ON Products.ProductID = ProductCategories.ProductID;

• Explanation:
  • A CTE defines a temporary result set that can be referenced within the main query.
  • Filters products by category and then joins with the main products table.

Table-Valued Functions (TVFs)

• Scenario: Reusable logic for filtering or transforming data.

CREATE FUNCTION GetProductsByCategory (@CategoryID INT)
RETURNS TABLE
AS
RETURN (
  SELECT ProductID, ProductName
  FROM Products
  WHERE CategoryID = @CategoryID
);

SELECT *
FROM GetProductsByCategory(1);

• Explanation:
  • A TVF returns a table result set based on input parameters.
  • Can be used to encapsulate complex filtering logic and reuse it in multiple queries.

Full-Text Search

• Scenario: Searching for text patterns within large datasets.

CREATE FULLTEXT INDEX ON Products (ProductName, Description);

SELECT ProductID, ProductName
FROM Products
WHERE CONTAINS(ProductName, 'laptop');

• Explanation:
  • Creates a full-text index on specified columns.
  • Uses the CONTAINS function to search for text patterns.