CODEWITHSUNDEEP
mysqlsql
Chella
Chella

Reputation: 1562

What is the difference between these two queries can anybody explain

i thought I achieve same result with the below two queries but I got a different result when I tried, can anybody please explain what is the difference, other than join and subquery. here t1id is primary column and t2id is referenced column from tbl_1

select * from tbl_1 where t1id in (select t2id from tbl_2);

select t1.* from tbl_1 t1, tbl_2 t2 where t1.t1id = t2.t2id;

EDIT: When I tried I got 93 records for first query and 74 for second query, I changed slightly the first query like :

select * from tbl_1 where t1id in (select distinct t2id from tbl_2);

then I got 40 rows. can anybody explain whats happening.

Thanks in advance

Upvotes: 1

Views: 107

Answers (3)

Sunny
Sunny

Reputation: 99

The reason for mismatch between 'in' and 'join' query is:-

tbl1 id name 1 abc

tbl2 id name 1 abc 1 abc

now, select * from tbl1 where id in (select id from tbl2) although there exists 2 values of id from table 2 i.e. 1, but only one row is fetched from tbl1. you can try executing the above query as select * from tbl1 where id in (1,1) --> It will give 1 row only This is because "In returns true if a specified value matches any value in a subquery or a list"

2nd, the innerjoin query will result in 2 rows as id from tbl1 gets matched with 2 rows from tbl2 table.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

They are both trying to do the same thing: get all rows from tbl_1 whose id value can be found in the t2id column if tbl_2, although the second one will return the same row from tbl_1 multiple times if the id is found multiple times in tbl_2 (not particularly useful).

But both queries are inferior to this one, which also does the sane thing, just mucho re efficiently:

select distinct t1.*
from tbl_1 t1
join tbl_2 t2 on t1.t1id = t2.t2id;

Upvotes: 1

Andomar
Andomar

Reputation: 238086

The first query selects all rows from the first table that have an id in the second table.

The second query selects all rows from the first table, and for each of those rows, all rows in the second table. It then filters out rows where the id does not match.

If id is not unique in the second table, the second query can return more rows than the first.

Upvotes: 4

Related Questions