SQL with Manoj

–> Question:

Please tell me which method is good and fast

1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id )

2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id )

3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id )
 

–> My Answer:

It does not matter what you use with SELECT inside EXISTS () statement

Because, as soon as EXIST() statement get one row it breaks/exits, and it just ignores the column’s list in SELECT clause.

You can check by this by simple example:

select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)

The “SELECT 1/0” ideally should give divide-by-zero error, but here inside EXISTS() it does not.

So, I use “SELECT *” happily within EXISTS() clause 🙂 like:

select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)

–> Answer By CELKO:

The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.

Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.

SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style.
 

Ref link.