Usage of JOINs vs IN/NOT IN for readability

Question

Let me preface this by saying this is not a question about efficiency.

Let's say, for the sake of argument, I have a database with two tables: Users and Roles.

Users looks like this:

Username    Password    FirstName    LastName
--------    --------    ----------   ---------
braab       pass1234    Braab        Himself
joe         pass1257    Joe          Smith
robert      pass1235    Robert       Irvine
kelly       pass1236    Kelly        Clarkson

Roles looks like this:

Username    Role
--------    ----
braab       admin
robert      customer
kelly       developer
joe         admin

I want to find the first and last names of all users who have the 'admin' role.

I have two ways to achieve this: using a join or using the in clause.

IN Method:

select
    firstname, lastname
from
    users
where
    username in
        (select
             username
         from
             roles
         where
             role = 'admin' 
        )

JOIN Method:

select
    user.firstname, user.lastname
from
    users as user
inner join
    roles as role
on
    user.username = role.username

Which way is more clear that I'm trying to find the first and last names of admin users? Is there an intended usage? To me, the in clause is the most clear in way of achieving the above goal.

Depending on your experience / training with SQL I can see this going either way. My worry is that I want other developers to be able to understand what goal I was trying to achieve.

Be aware that `NOT IN` can be tricky when subquery can return `NULL`. I would use `JOIN` over `IN/NOT IN` especially when subquery is correlated. — Lukasz Szozda, Dec 02 '15 at 15:30
Can one user have multiple roles? Otherwise should be better add a field on `Users` table — Juan Carlos Oropeza, Dec 02 '15 at 15:32
Those two statement do not necessarily return the same thing, e.g. when a user can have more than one role — , Dec 02 '15 at 15:34
Your JOIN example misses `where role.Role = 'admin'`. As to the question: always JOIN. If you have more than two tables, subqueries become less readable. Also, efficiency *should* be important to you. — Andre, Dec 02 '15 at 15:37
Anyone who is not able to understand them both is not a developer. — Dan Bracuk, Dec 02 '15 at 15:38
Efficiency IS important, but I wanted to make it clear that the intent of the question wasn't one about efficiency. StackOverflow already has tons of questions regarding efficiency. — braab, Dec 02 '15 at 16:24
@Andre451 Efficiency IS important. But the question isn't regarding efficiency. I wanted to know which of the two queries above were the most clear in achieving the goal in the example. — braab, Dec 02 '15 at 16:41

score 0 · Answer 1 · answered Dec 02 '15 at 15:56

0

The problem isnt if developer will understand the code. You should focus on what will offer the best perfomance.

As you can see in this SQL Fiddle Demo execution plans are different. The order on what tables are process are different. Because tables are small index not become a factor, but that would be also affect what option you use.

answered Dec 02 '15 at 15:56

Juan Carlos Oropeza

47,252
12
78
118

It IS a problem though. If another developer has to maintain my code, it's important that they understand what I was doing. Performance is important, but so is readability. StackOverflow is already inundated with efficiency questions. I wanted to know which of the two queries above were the most clear in achieving the goal in the example. – braab Dec 02 '15 at 16:36
@braab both are equally clear, so is more like what standard you follow. I like use more `JOIN` than `IN`. because less code lines. – Juan Carlos Oropeza Dec 02 '15 at 16:39

score 0 · Answer 2 · answered Dec 02 '15 at 17:02

FWIW, this would be my statement of choice. Concise, readable code, and a comment for whoever doesn't want to read the code.

-- get first and last name of all users who have the 'admin' role
SELECT users.firstname
     , users.lastname
FROM users
INNER JOIN roles
    ON users.username = roles.username
WHERE roles.Role = 'admin'

As I wrote in the comment, once you go beyond 2 or maybe 3 tables, JOINs are much better than IN subqueries for readability.

Usage of JOINs vs IN/NOT IN for readability

2 Answers2