Use values as a derived table in SQL

• Language:: T-SQL
• Type:: backend
• Context:: Values as a derived table
• Description – Use a list of values as a table when trying to see which values match in other data tables. I used this to see which users in an AD list of users where not in the Onboarded Users table in our db.
• Snippet

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;

-- 
SELECT * FROM (VALUES 
    ('Shan.Aliawan@test-uri.com'),
    ('Mason.Kasumovic@test-uri.com'),
    ('Otto.Kainulainen@test-uri.com'),
    ('Niamh.Reading@test-uri.com'),
) AS ActiveUsers(AzureName)
LEFT join OnboardedUsers o ON ActiveUsers.AzureName = o.AzureUserName
WHERE o.AzureUserName IS NULL
ORDER BY ActiveUsers.AzureName Asc

• Dependencies::

---

📇Additional Metadata

• 📁Type:: snippet
• 🏷️Tags::
• 📡Status:: #status/🌲