MCQ King

MS SQL Server – Union and Case

Start MCQ

In Microsoft SQL Server, both the UNION operator and the CASE expression are powerful tools for working with data in SQL queries. Here’s a brief overview of each:

UNION Operator:

  1. Purpose:
    • The UNION operator combines the result sets of two or more SELECT statements into a single result set.
    • It is used to retrieve data from multiple tables or queries and present it as a single result set.
  2. Syntax:SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
    • The column names and data types in the SELECT statements must match.
  3. Distinct Values:
    • By default, UNION removes duplicate rows from the result set. If you want to include duplicate rows, you can use UNION ALL instead.
  4. Example:SELECT FirstName, LastName FROM Employees UNION SELECT FirstName, LastName FROM Customers;
    • This query combines the “Employees” and “Customers” tables to retrieve a list of unique names.
  5. Use Cases:
    • UNION is useful when you need to combine data from different sources or tables and present it as a single result set. It’s commonly used for data integration and reporting.

CASE Expression:

  1. Purpose:
    • The CASE expression allows you to perform conditional logic in SQL queries, making it possible to return different values based on specified conditions.
  2. Syntax:SELECT column1, column2, ... CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END AS Alias FROM table_name;
    • You can have multiple WHEN conditions, and the ELSE part is optional.
  3. Example:SELECT ProductName, CASE WHEN Price > 100 THEN 'Expensive' WHEN Price <= 100 AND Price > 50 THEN 'Moderate' ELSE 'Inexpensive' END AS PriceCategory FROM Products;
    • This query categorizes products based on their prices.
  4. Use Cases:
    • CASE expressions are used for conditional calculations, custom value mapping, and data transformation in SQL queries. They are handy for generating calculated columns in result sets.

Choosing Between UNION and CASE:

  • Use UNION when you need to combine distinct rows from multiple tables or queries into a single result set.
  • Use CASE when you need to perform conditional logic to generate values within a single query, such as categorizing data or creating custom labels.

Both UNION and CASE are valuable tools for querying and manipulating data in SQL Server, and their usage depends on the specific requirements of your queries and reporting needs.