Re: good style? - Mailing list pgsql-sql

this kind of joining has been termed "explicit JOIN syntax"
by pgsql 7.3 docs.

I personally  feel it makes ur SQL look uglier and complicated. i feel the 
WHERE caluse shud contain the genuine filters of result set not the ones 
which could be a part of JOIN syntax itself. (its personal view though)


you may refer to the DOCs below on postgresqls' website.


http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-joins.html

..... excerpt from the docs.........
When a query only involves two or three tables, there aren't many join orders 
to worry about. But the number of possible join orders grows exponentially as 
the number of tables expands. Beyond ten or so input tables it's no longer 
practical to do an exhaustive search of all the possibilities, and even for 
six or seven tables planning may take an annoyingly long time. When there are 
too many input tables, the PostgreSQL planner will switch from exhaustive 
search to a genetic probabilistic search through a limited number of 
possibilities. (The switch-over threshold is set by the GEQO_THRESHOLD 
run-time parameter described in the PostgreSQL 7.3 Administrator's Guide.) 
The genetic search takes less time, but it won't necessarily find the best 
possible plan. 
............

regds
mallah.



On Friday 21 February 2003 07:00 pm, Rafal Kedziorski wrote:
> hi,
>
> I have 8 tables and this query:
>
> select u.users_id, m.name as mandant_name, u.login_name, u.password,
> u.first_name, u.last_name, u.creation_date, g.name as groups_name,
> ae.acl_entry_id, a.name as acl_name, p.name as permission_name
>   from mandant m, users_2_groups u2g, groups g, users u, permission p,
> acl a, acl_entry ae, groups_2_acl_entry g2ae
>   where m.mandant_id = u.mandant_id and
>              u2g.groups_id = g.groups_id and
>              u2g.users_id = u.users_id and
>              g2ae.groups_id = g.groups_id and
>              g2ae.acl_entry_id = ae.acl_entry_id and
>              ae.acl_id = a.acl_id and
>              ae.permission_id = p.permission_id
>
> I'm not using JOIN for get this information. would be JOIN a better sql
> programming style? faster?
>
>
> Best Regards,
> Rafal
>
>
> sql script:
>
> DROP TABLE groups_2_acl_entry;
> DROP TABLE users_2_groups;
> DROP TABLE groups;
> DROP TABLE users;
> DROP TABLE acl_entry;
> DROP TABLE permission;
> DROP TABLE acl;
> DROP TABLE language;
> DROP TABLE mandant;
> DROP TABLE license;
> DROP TABLE firm;
>
>
>
> CREATE TABLE firm (
>   firm_id numeric(20, 0) NOT NULL,
>   name varchar(40) NOT NULL,
>   CONSTRAINT firm_pkey PRIMARY KEY (firm_id)
> ) WITH OIDS;
>
> INSERT INTO firm VALUES (1, 'polonium');
>
>
>
> CREATE TABLE license (
>   license_id numeric(20, 0) NOT NULL,
>   key varchar(100) NOT NULL,
>   creation_date timestamp NOT NULL,
>   valid_from timestamp NOT NULL,
>   expired timestamp,
>   CONSTRAINT license_pkey PRIMARY KEY (license_id)
> ) WITH OIDS;
>
> INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED',
> now(), now(), NULL);
>
>
>
> CREATE TABLE mandant (
>   mandant_id numeric(20, 0) NOT NULL,
>   firm_id numeric(20, 0) NOT NULL,
>   license_id numeric(20, 0) NOT NULL,
>   parent_id numeric(20, 0),
>   name varchar(20) NOT NULL,
>   creation_date timestamp NOT NULL,
>   CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id),
>   CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id),
>   CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license
> (license_id),
>   CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant
> (mandant_id)
> ) WITH OIDS;
>
> INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now());
>
>
>
> CREATE TABLE language (
>   language_id int2 NOT NULL,
>   lang_short char(2) NOT NULL,
>   lang_long varchar(20) NOT NULL,
>   CONSTRAINT language_pkey PRIMARY KEY (language_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short);
> CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long);
>
> INSERT INTO language VALUES (1, 'de', 'deutsch');
> INSERT INTO language VALUES (2, 'en', 'english');
>
>
>
> CREATE TABLE acl (
>   acl_id int2 NOT NULL,
>   name varchar(20) NOT NULL,
>   description varchar(200),
>   CONSTRAINT acl_pkey PRIMARY KEY (acl_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX acl_name_idx ON acl (name);
>
> INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module');
> INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module');
> INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module');
> INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module');
> INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module');
>
>
>
> CREATE TABLE permission (
>   permission_id int2 NOT NULL,
>   name varchar(20) NOT NULL,
>   description varchar(200),
>   CONSTRAINT permission_pkey PRIMARY KEY (permission_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX permission_name_idx ON permission (name);
>
> INSERT INTO permission VALUES (1, 'access', 'access permission');
> INSERT INTO permission VALUES (2, 'read', 'read permission');
> INSERT INTO permission VALUES (3, 'write', 'write permission');
> INSERT INTO permission VALUES (4, 'execute', 'execute permission');
> INSERT INTO permission VALUES (5, 'modify', 'modify permission');
> INSERT INTO permission VALUES (6, 'list', 'list permission');
>
>
>
> CREATE TABLE acl_entry (
>   acl_entry_id int2 NOT NULL,
>   acl_id int2 NOT NULL,
>   permission_id int2 NOT NULL,
>   CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id),
>   CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id),
>   CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES
> permission (permission_id)
> ) WITH OIDS;
>
> -- acl_entry for mmcms.access acl with access permission
>
> -- acl 'mmcms.access' has 'access' permission
> INSERT INTO acl_entry VALUES (1, 1, 1);
> -- acl 'mmcms.system' has 'read' permission
> INSERT INTO acl_entry VALUES (2, 2, 2);
> -- acl 'mmcms.system' has 'write' permission
> INSERT INTO acl_entry VALUES (3, 2, 3);
> -- acl 'mmcms.admin' has 'read' permission
> INSERT INTO acl_entry VALUES (4, 3, 2);
> -- acl 'mmcms.admin' has 'write' permission
> INSERT INTO acl_entry VALUES (5, 3, 3);
>
>
>
> CREATE TABLE users (
>   users_id numeric(20, 0) NOT NULL,
>   mandant_id numeric(20, 0) NOT NULL,
>   language_id int2 NOT NULL,
>   login_name varchar(50) NOT NULL,
>   password varchar(15) NOT NULL,
>   first_name varchar(20) NOT NULL,
>   last_name varchar(20) NOT NULL,
>   creation_date timestamp NOT NULL,
>   last_login_date timestamp,
>   status int2 NOT NULL,
>   CONSTRAINT users_pkey PRIMARY KEY (users_id),
>   CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
> (mandant_id),
>   CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language
> (language_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX users_login_name_idx ON users (login_name);
>
> INSERT INTO users VALUES (1, 1, 1, 'rafcio@polonium.de', 'test',
> 'Rafal', 'Kedziorski', now(), NULL, 0);
>
>
>
> CREATE TABLE groups (
>   groups_id numeric(20, 0) NOT NULL,
>   mandant_id numeric(20, 0) NOT NULL,
>   name varchar(20) NOT NULL,
>   description varchar(200) NOT NULL,
>   creation_date timestamp NOT NULL,
>   CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
>   CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
> (mandant_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name);
>
> -- every mandant should have own access group
> INSERT INTO groups VALUES (1, 1, 'access', 'access group', now());
> INSERT INTO groups VALUES (2, 1, 'system', 'system group', now());
> INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now());
>
>
>
> CREATE TABLE users_2_groups (
>   users_2_groups_id numeric(20, 0) NOT NULL,
>   users_id numeric(20, 0) NOT NULL,
>   groups_id numeric(20, 0) NOT NULL,
>   valid_from timestamp NOT NULL,
>   expired timestamp,
>   CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id),
>   CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
> (groups_id),
>   CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups
> (users_id, groups_id);
>
> INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL);
> INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL);
> INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL);
>
>
>
> CREATE TABLE groups_2_acl_entry (
>   groups_2_acl_entry_id numeric(20, 0) NOT NULL,
>   groups_id numeric(20, 0) NOT NULL,
>   acl_entry_id int2 NOT NULL,
>   CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id),
>   CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
> (groups_id),
>   CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES
> acl_entry (acl_entry_id)
> ) WITH OIDS;
>
> INSERT INTO groups_2_acl_entry VALUES (1, 1, 1);
> INSERT INTO groups_2_acl_entry VALUES (2, 2, 2);
> INSERT INTO groups_2_acl_entry VALUES (3, 2, 3);
> INSERT INTO groups_2_acl_entry VALUES (4, 3, 4);
> INSERT INTO groups_2_acl_entry VALUES (5, 3, 5);
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.