-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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • How about `WHERE Username IN ('Jack', 'Jill',....)` – forpas Apr 12 '19 at 17:25
  • Possible duplicate of [SQL search multiple values in same field](https://stackoverflow.com/questions/16240041/sql-search-multiple-values-in-same-field) – Lucas Hendren Apr 12 '19 at 17:25
  • *I have a list of Usernames* where? – forpas Apr 12 '19 at 17:40
  • @forpas - The list is contained in an Excel file. –  Apr 12 '19 at 17:42
  • Read this: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017 on how to import data from Excel to your database and then compare the data from your table to the imported data. – forpas Apr 12 '19 at 17:48

2 Answers2

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

Luke
  • 186
  • 1
  • 7
  • Yes. But the list contains more than 1000 Usernames. And inserting each Username in IN will be time consuming. –  Apr 12 '19 at 17:33
  • I'll update my answer, but you may still get O(N) time if you have an index on `column_name`. – Luke Apr 12 '19 at 21:07
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)
RToyo
  • 2,877
  • 1
  • 15
  • 22
  • Yes. But the list contains more than 1000 Usernames. And inserting each Username in IN will be time consuming. –  Apr 12 '19 at 17:32
  • 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. – RToyo Apr 12 '19 at 17:34
  • 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. –  Apr 12 '19 at 17:36
  • 2
    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). – RToyo Apr 12 '19 at 17:40
  • 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. – RToyo Apr 12 '19 at 17:49