How to check if a given data exists in multiple tables (all of which has the same column)?

Question

I have 3 tables, each consisting of a column called username. On the registration part, I need to check that the requested username is new and unique.

I need that single SQL that will tell me if that user exists in any of these tables, before I proceed. I tried:

SELECT tbl1.username, tbl2.username, tbl3.username
FROM tbl1,tbl2,tbl3
WHERE tbl1.username = {$username}
   OR tbl2.username = {$username}
   OR tbl3.username ={$username}

Is that the way to go?

Answer 1

select 1 
from (
    select username as username from tbl1
    union all
    select username from tbl2
    union all
    select username from tbl3
) a
where username = 'someuser'

Answer 2

In the event you honestly just want to know if a user exists:

The quickest approach is an existence query:

select 
NOT EXISTS (select username from a where username = {$username}) AND 
NOT EXISTS (select username from b where username = {$username}) AND 
NOT EXISTS (select username from c where username = {$username});

If your username column is marked as Unique in each table, this should be the most efficient query you will be able to make to perform this operation, and this will outperform a normalized username table in terms of memory usage and, well, virtually any other query that cares about username and another column, as there are no excessive joins. If you've ever been called on to speed up an organization's database, I can assure you that over-normalization is a nightmare. In regards to the advice you've received on normalization in this thread, be wary. It's great for limiting space, or limiting the number of places you have to update data, but you have to weigh that against the maintenance and speed overhead. Take the advice given to you on this page with a grain of salt.

Get used to running a query analyzer on your queries, if for no other reason than to get in the habit of learning the ramifications of choices when writing queries -- at least until you get your sea legs.

In the event you want to insert a user later:

If you are doing this for the purpose of eventually adding the user to the database, here is a better approach, and it's worth it to learn it. Attempt to insert the value immediately. Check afterwards to see if it was successful. This way there is no room for some other database call to insert a record in between the time you've checked and the time you inserted into the database. For instance, in MySQL you might do this:

INSERT INTO {$table} (`username`, ... )
  SELECT {$username} as `username`, ... FROM DUAL 
   WHERE 
     NOT EXISTS (select username from a where username = {$username}) AND 
     NOT EXISTS (select username from b where username = {$username}) AND 
     NOT EXISTS (select username from c where username = {$username});

All database API's I've seen, as well as all SQL implementations will provide you a way to discover how many rows were inserted. If it's 1, then the username didn't exist and the insertion was successful. In this case, I don't know your dialect, and so I've chosen MySQL, which provides a DUAL table specifically for returning results that aren't bound to a table, but honestly, there are many ways to skin this cat, whether you put it in a transaction or a stored procedure, or strictly limit the process and procedure that can access these tables.

Update -- How to handle users who don't complete the sign up process

As @RedFilter points out, if registration is done in multiple steps -- reserving a username, filling out details, perhaps answering an email confirmation, then you will want to at least add a column to flag this user (with a timestamp, not a boolean) so that you can periodically remove users after some time period, though I recommend creating a ToBePurged table and add new users to that, along with a timestamp. When the confirmation comes through, you remove the user from this table. Periodically you will check this table for all entries prior to some delta off your current time and simply delete them from whichever table they were originally added. My philosophy behind this is to define more clearly the responsibility of the table and to keep the number of records you are working with very lean. We certainly don't want to over-engineer our solutions, but if you get into the habit of good architectural practices, these designs will flow out as naturally as their less efficient counterparts.

Answer 3

No. Two processes could run your test at the same time and both would report no user and then both could insert the same user.

It sounds like you need a single table to hold ALL the users with a unique index to prevent duplicates. This master table could link to 'sub-tables' using a user ID, not user name.

Answer 4

Given the collation stuff, you could do this instead, if you don't want to deal with the collation mismatch:

select sum(usercount) as usercount
from (
    select count(*) as usercount from tbl1 where username = 'someuser'
    union all
    select count(*) as usercount from tbl2 where username = 'someuser'
    union all
    select count(*) as usercount from tbl3 where username = 'someuser'
) as usercounts

If you get 0, there isn't a user with that username, if you get something higher, there is.

Note: Depending on how you do the insert, you could in theory get more than one user with the same username due to race conditions (see other comments about normalisation and unique keys).