The following Microsoft SQL Server T-SQL sample code demonstrates the use of UNION ALL. Index usage is usually better with UNION ALL:

--  T-SQL UNION ALL operator - duplicates are NOT eliminated

USE Northwind

GO

SELECT CompanyName,  -- 3 rows

       ContactName,

       ContactTitle,

       Phone

FROM   Customers

WHERE  Country = 'Canada'

UNION ALL

SELECT CompanyName,  -- 3 rows

       ContactName,

       ContactTitle,

       Phone

FROM   Customers

WHERE  Country = 'Canada'

UNION ALL

SELECT CompanyName,   -- 0 rows

       ContactName,

       ContactTitle,

       Phone

FROM   Customers

WHERE  Region = 'Quebec'

UNION ALL

SELECT CompanyName,  -- 0 rows

       ContactName,

       ContactTitle,

       Phone

FROM   Customers

WHERE  City = 'Montreal'

/*

CompanyName             ContactName       ContactTitle            Phone

Bottom-Dollar Markets   Elizabeth Lincoln Accounting Manager      (604) 555-4729

Laughing Bacchus Wine   Yoshi Tannamuri   Marketing Assistant     (604) 555-3392

Mère Paillarde          Jean Fresnière    Marketing Assistant     (514) 555-8054

Bottom-Dollar Markets   Elizabeth Lincoln Accounting Manager      (604) 555-4729

Laughing Bacchus Wine   Yoshi Tannamuri   Marketing Assistant     (604) 555-3392