100 Usernames to search for in SQL-CodePudding

I need to perform a query on a large database with thousands of users. I have the query except I need to search where username = 600 different usernames. How do I do such a thing without having to paste all of the usernames and manually format each name?

CodePudding user response：

As per @Abra's comment. There are a few options:

Using the SQL IN Operator with a list of values:

SELECT *
FROM someTable t
WHERE t.userName IN ('userName1', 'username2', ...)

This option means formatting all possible values to be within quotes and separated by comma's, which can be a chore when dealing with many values. There are some tricks to make this easier (at least in SQL Server). For example you can hold shift alt and press the arrow keys to select and format multiple rows at the same time.

Using the IN operator with a subquery:

This is a more convenient option when dealing with lots of possible values:

SELECT *
FROM someTable t
WHERE t.userName IN 
(
    SELECT u.username 
    FROM users u 
    WHERE u.someColumn = 'someValue'
)

This will only work though when you can filter the users adequately using the where clause.

When a link can be established between the tables, a JOIN might be a good option:

SELECT t.*
FROM someTable t
JOIN users u ON u.userId = t.userId
WHERE u.someColumn = 'someValue')

CodePudding user response：

I did indeed have to use the "IN" operator. This site saved me hours in formatting. SQL Auto Formatter