3

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.

  • Interesting question indeed. Proving equivalence between queries is, in general, a difficult task. Though it does not answer your question (AFAIK IN predicate is not supported) you might find some interest in cosette: cosette.cs.washington.edu – Lennart Apr 6 '18 at 6:12
  • 1
    @Lennart Thank you for your feedback. Someone asked the general question about two sql statements being equal some month ago: dba.stackexchange.com/questions/96865/… – guettli Apr 6 '18 at 6:29
1

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.

1

Yes, both queries would return the same data.

You may want to use EXISTS to help your Query Optimizer, since you are only SELECTing data from 1 of the 3 tables.

It also helps readability if you keep your code short and well-formatted:

SELECT username
FROM auth_user u
WHERE EXISTS (
        SELECT 1
        FROM auth_user_groups ug
        JOIN auth_group g ON g.id = ug.group_id
        WHERE g.name = 'group1'
            AND ug.user_id = u.id
        );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.