mysql根据表中数据输出结果,MySQL根据另一个表中的数据从一个表返回结果

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.

解决方案

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'