On Wednesday, May 30, 2012 9:45:16 f.h. David Bicking wrote:
> If Uniform has a given EmployeeName twice, you will get the Employee.Name
> twice in this query. Thus it would be a different result than if you did
> not join with Uniform.
On Wednesday, May 30, 2012 9:57:00 f.h. Petite Abeille wrote:
> The outer join could affect the cardinality of the result (i.e. there could
> be multiple rows matching the driving table). Therefore, no, it cannot be
> discarded without additional information regarding the nature of the
> reference (i.e. to-one).
Uniform has a unique constraint on EmployeeName, is this not the necessary
"additional information"? Is it possible for me to give the query optimizer
this necessary information so it could do the optimization?
sqlite> create table Employee (name);
sqlite> create table Uniform (employeename, inseam, constraint ue unique
(employeename));
sqlite> insert into employee values ("Joe");
sqlite> insert into employee values ("Steve");
sqlite> insert into employee values ("Eric");
sqlite> insert into employee values ("Dave");
sqlite> insert into Uniform values ("Joe", 77);
sqlite> insert into Uniform values ("Dave", 81);
sqlite> select Name from Employee join Uniform on
Employee.name=Uniform.employeename;
Joe
Dave
sqlite> explain query plan select Name from Employee join Uniform on
Employee.name=Uniform.employeename;
0|0|0|SCAN TABLE Employee (~1000000 rows)
0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
(employeename=?) (~1 rows)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
