SQL query In Vs Exists
User asked whether to use EXISTS or IN subquery when joining a huge table (t1, millions of rows) with a smaller table (t2, 1M rows). Consensus: EXISTS is generally faster than IN because it uses join semantics rather than nested loops. A direct join syntax (SELECT t1.* FROM t1, t2 WHERE t1.col1=t2.col1) is preferred, though results may differ if t2.col1 has duplicates.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All,
I have two tables, t1 (huge table with 20 columns and millions of rows) & t2
(one column and 1 million rows)
the common column between these two table is col1 integer (indexed column in
t1 and t2)
Need to extract all data from t1 where t1.col1 = t2.col1
which one is the best in terms of performance and why :)
select * from t1 a where
exists
(select 1 from t2 b where a.col1 = b.col1);
AND
select * from t1 where col1 IN (select col1 from t2);
EXISTS will tend to use a join while IN will tend to use a subselect =
(nested loop). I would expect the EXISTS to run an order of magnitude =
faster than the IN.
j.
On Aug 26, 2013, at 7:37 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com> =
wrote:
> Hello All,=20
>=20
> I have two tables, t1 (huge table with 20 columns and millions of =
rows) & t2=20
> (one column and 1 million rows)=20
>=20
> the common column between these two table is col1 integer (indexed =
column in=20
> t1 and t2)=20
>=20
> Need to extract all data from t1 where t1.col1 =3D t2.col1=20
>=20
> which one is the best in terms of performance and why :)=20
>=20
> select * from t1 a where=20
> exists=20
> (select 1 from t2 b where a.col1 =3D b.col1);=20>=20
> AND=20
>=20
> select * from t1 where col1 IN (select col1 from t2);=20>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Select t1.* from t1, t2 where t1.col1 = t2.col1 ???
Subqueries are more expensive always.
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com>
An: ids@iiug.org
Gesendet: Montag, 26. August 2013 13:37:29
Betreff: SQL query In Vs Exists [31260]
Hello All,
I have two tables, t1 (huge table with 20 columns and millions of rows) & t2
(one column and 1 million rows)
the common column between these two table is col1 integer (indexed column in
t1 and t2)
Need to extract all data from t1 where t1.col1 = t2.col1
which one is the best in terms of performance and why :)
select * from t1 a where
exists
(select 1 from t2 b where a.col1 = b.col1);
AND
select * from t1 where col1 IN (select col1 from t2);
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Probably
Select t1.* from t1, t2 where t1.col1 =t2 col1;
But the optimizer should convert your first one to this but may not.
Art
On Aug 26, 2013 7:38 AM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> wrote:
> Hello All,
>
> I have two tables, t1 (huge table with 20 columns and millions of rows) &
> t2
> (one column and 1 million rows)
>
> the common column between these two table is col1 integer (indexed column
> in
> t1 and t2)
>
> Need to extract all data from t1 where t1.col1 = t2.col1
>
> which one is the best in terms of performance and why :)
>
> select * from t1 a where
> exists
> (select 1 from t2 b where a.col1 = b.col1);>
> AND
>
> select * from t1 where col1 IN (select col1 from t2);>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3675eb3a70d04e4d85e39
This query may not return the same results as the other two (assuming
t2.col1 has duplicate values).
I'd vote for EXISTS... but it's hard to explain why... It will do a
first-key only... IN will require a temp structure probably.... but it
depends on the query plan... If it can do an hash join it can be faster...
Regards
On Mon, Aug 26, 2013 at 1:51 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Probably
> Select t1.* from t1, t2 where t1.col1 =t2 col1;
>
> But the optimizer should convert your first one to this but may not.
>
> Art
> On Aug 26, 2013 7:38 AM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com>
> wrote:
>
> > Hello All,
> >
> > I have two tables, t1 (huge table with 20 columns and millions of rows) &
> > t2
> > (one column and 1 million rows)
> >
> > the common column between these two table is col1 integer (indexed column
> > in
> > t1 and t2)
> >
> > Need to extract all data from t1 where t1.col1 = t2.col1
> >
> > which one is the best in terms of performance and why :)
> >
> > select * from t1 a where
> > exists
> > (select 1 from t2 b where a.col1 = b.col1);> >
> > AND
> >
> > select * from t1 where col1 IN (select col1 from t2);> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c3675eb3a70d04e4d85e39
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf307f37c298d70c04e4d8e462
Thanks Jack, Marcus, Art and Fernando!
> This query may not return the same results as the other two (assuming
> t2.col1 has duplicate values).
> I'd vote for EXISTS... but it's hard to explain why... It will do a
> first-key only... IN will require a temp structure probably.... but it
> depends on the query plan... If it can do an hash join it can be faster...
> Regards
>
> On Mon, Aug 26, 2013 at 1:51 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> Probably
> Select t1.* from t1, t2 where t1.col1 =t2 col1;
>
> But the optimizer should convert your first one to this but may not.
>
> Art
> On Aug 26, 2013 7:38 AM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com>
> wrote:
>
> > Hello All,
> >
> > I have two tables, t1 (huge table with 20 columns and millions of rows) &
> > t2
> > (one column and 1 million rows)
> >
> > the common column between these two table is col1 integer (indexed column
> > in
> > t1 and t2)
> >
> > Need to extract all data from t1 where t1.col1 = t2.col1
> >
> > which one is the best in terms of performance and why :)
> >
> > select * from t1 a where
> > exists
> > (select 1 from t2 b where a.col1 = b.col1);> >
> > AND
> >
> > select * from t1 where col1 IN (select col1 from t2);