 | |
#1
 
| |||
| |||
|
faster query style -
03-07-2008
, 09:38 AM
#2
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#3
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#4
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#5
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#6
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#7
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#8
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#9
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#10
| |||
| |||
|
|
Is there any noticeable speed difference in these two queries? select * from table1, table2 where table1.field=table2.field blahblahblah select * from table1 where table1.field in (select field from table2) Both fields are indexed, of course. |
Consider:
table1:
data plus1 (c(10))
1 one
2 two
3 three
table2:
data plus2 (c(10))
1 1-1
3 3-1
3 3-2
The first query will return
data_a plus1 data_b plus2
1 one 1 1-1
3 three 3 3-1
3 three 3 3-2
and the second will return
data plus1
1 one
3 three
The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal. All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.
The second query means to return those rows in table1 where data
is in table2 as well. The columns of table2 will not be included, and
there is no possibility of duplicates.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.