Subselect vs join: Are both SQL queries equal? I have this db:

DROP TABLE auth_user_groups;
DROP TABLE auth_user;
DROP TABLE auth_group;

CREATE TABLE auth_user (
    id integer PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);


SQL statement using join:

select username from auth_user, auth_user_groups, auth_group where 
  auth_group.name='group1' and 
  auth_user_groups.group_id=auth_group.id and 
  auth_user.id=auth_user_groups.user_id;


SQL statement using subselect:

select username from auth_user where id in (
  select user_id from auth_user_groups where group_id in (
    select id from auth_group where name ='group1'));


Is the result of both SQL statements equal for all possible data sets?

I use PostgreSQL, but AFAIK this should not matter for this question.
Both query will return same possible data sets as if you use in as sub query, it behaves like inner join in SQL query and where clause in both SQL statements should be same as in your where condition.