-1

I have a list of Usernames. How do I check if those usernames already exists in a SQL table column?

For eg: 

Select * from tblPerson where Username in ('Jack', 'Jill', 'Mary');

I want to check list of Usernames. About more than 1000 Usernames.

Inserting 1000 Usernames is too time consuming.

Improve this question
5
  • How about WHERE Username IN ('Jack', 'Jill',....) Commented Apr 12, 2019 at 17:25
  • Possible duplicate of SQL search multiple values in same field Commented Apr 12, 2019 at 17:25
  • I have a list of Usernames where? Commented Apr 12, 2019 at 17:40
  • @forpas - The list is contained in an Excel file. Commented Apr 12, 2019 at 17:42
  • Read this: learn.microsoft.com/en-us/sql/relational-databases/… on how to import data from Excel to your database and then compare the data from your table to the imported data. Commented Apr 12, 2019 at 17:48

2 Answers 2

Reset to default
1

I think you're looking for the IN condition. This can check against a list of usernames at once.

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

Update
If you have a lot of columns, you will want to add an index to column_name. This allows your database to lookup each value of the IN with O(1) time. This will depend on your database, of course. Not all SQL databases are created equal.

You will also want to use bind variables. This allows the database to optimize performance. More info here: https://stackoverflow.com/a/1013959/11352813

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yes. But the list contains more than 1000 Usernames. And inserting each Username in IN will be time consuming.
I'll update my answer, but you may still get O(N) time if you have an index on column_name.
0

If you have a list of usernames, you can use IN instead of =. For example:

select * form tblPerson where Username in ('Jack', 'Jill', 'Alice', 'Bob')

If you have the list of usernames already existing in another table, you can also use the IN operator, but replace the hard coded list of usernames with a subquery.

So for example, if you have another table called tblOtherPerson, with the usernames stored in a column called OtherUsername, you could do:

select * from tblPerson where Username in (select OtherUsername from tblOtherPerson)

The other way (often preferred) is to JOIN the two tables together:

select
    tblPerson.*
from
    tblPerson
    inner join tblOtherPerson
        on (tblPerson.Username = tblOtherPerson.OtherUsername)
Improve this answer

5 Comments

Yes. But the list contains more than 1000 Usernames. And inserting each Username in IN will be time consuming.
Is that list contained in a table in your database? If so, I can modify this answer to demonstrate joining to that table to find duplicates.
No. The list is not contained in my database. It is in Excel file. I want to check if the List of Usernames are already existed in the database before inserting in the table.
If that's the case, I would suggest using an Excel formula to build you a list to place in the IN list. If your usernames are stored in column A, then make a formula that says ="'"&A1&"'," for row 1, and then feed that formula through the rest of the rows. This will create a list of usernames that you can paste into your IN (just remove the comma after the final username).
To clarify: you can use Excel formulas to generate that list of usernames that you paste into your IN list. You can think of it like you want to have a query that says select * from tbl where user in ('A1', 'A2', 'A3', 'A4'...) (assuming that your usernames are in column A in your Excel file). All you need to do is have Excel add quotes around each username and add a comma afterwards to make it into a list. Then you copy that column in Excel and paste it into your IN list.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.