MySQL returning results from one table based on data in another table

Question

Before delving into the issue, first I will explain the situation. I have two tables such as the following:

USERS TABLE
user_id
username
firstName
lastName

GROUPS TABLE
user_id
group_id

I want to retrieve all users who's first name is LIKE '%foo%' and who is a part of a group with group_id = 'givengid'

So, the query would like something like this:

SELECT user_id FROM users WHERE firstName LIKE '%foo'"

I can make a user defined sql function such as ismember(user_id, group_id) that will return 1 if the user is a part of the group and 0 if they are not and this to the WHERE clause in the aforementioned select statement. However, this means that for every user who's first name matches the criteria, another query has to be run through thousands of other records to find a potential match for a group entry.

The users and groups table will each have several hundred thousand records. Is it more conventional to use the user defined function approach or run a query using the UNION statement? If the UNION approach is best, what would the query with the union statement look like?

Of course, I will run benchmarks but I just want to get some perspective on the possible range of solutions for this situation and what is generally most effective/efficient.

Answer 1

You don't need to use either a UNION or a user-defined function here; instead, you can use a JOIN (which lets you join one table to another one based on a set of equivalent columns):

SELECT u.user_id
FROM users AS u
JOIN groups AS g
  ON g.user_id = u.user_id
WHERE g.group_id = 'givengid'
  AND u.firstName LIKE '%foo'

What this query does is join rows in the groups table to rows in the users table when the user_id is the same (so if you were to use SELECT *, you would end up with a long row containing the user data and the group data for that user). If multiple groups rows exist for the user, multiple rows will be retrieved before being filtered by the WHERE clause.

Answer 2

You should use a JOIN to get users matching your two criteria.

SELECT 
  user_id 
FROM 
  users 
INNER JOIN
  groups
  ON groups.user_id = users.users_id
    AND groups.group_id = given_id
WHERE 
  firstName LIKE '%foo'

Answer 3

Use a join:

SELECT DISTINCT user_id 
FROM users
INNER JOIN groups ON groups.user_id = users.user_id
WHERE users.firstName LIKE '%foo'
AND groups.group_id = '23'

The DISTINCT makes sure you don't have duplicate user IDs in the result.