Equivalente to INNER

Question

I want to structure a query with no using INNER JOIN. I figured out that an INNER like this:

Select A.Name 
from A INNER JOIN B on (A.id1 = B.id1 AND A.id2 = B.id2)
Where B.id = @Id

produce the same as:

select A.Name 
from A 
where  
A.id1 in (select B.id1 from B where B.id = @Id)
and
A.id2 in (select B.id2 from B where B.id = @Id)

Isn't it?

Note that my question is not about if it is better or not, only if it is an equivalent or if there is not an equivalente for that INNER.

Possible duplicate of [Subquery v/s inner join in sql server](https://stackoverflow.com/questions/14052596/subquery-v-s-inner-join-in-sql-server) — Ankush Madankar, Feb 05 '19 at 08:55
My question is about equivalence of functionality, not about if it is better or not. — Ibai, Feb 05 '19 at 09:00
If inner joins are better than subqueries depends on dbms and other things. — jarlh, Feb 05 '19 at 09:00
You can not replace an INNER JOIN with an IN or EXISTS condition. They mean different things. Sometimes one can be rewritten to the other, but not always. — , Feb 05 '19 at 09:01
Please a_horse_with_no_name may be you are right ... please can you make your comment as an answer with a little bit of argumentation. THX — Ibai, Feb 05 '19 at 09:05

jarlh · Accepted Answer · 2019-02-05T09:05:41.557

3

Your second query may match id1 and id2 from different B rows, so that query may return rows not expected. You have to keep id1 and id2 together:

Use EXISTS:

select A.Name 
from A 
where exists (select * from B
              where A.id1 = B.id1 AND A.id2 = B.id2
                and B.id = @Id)

or "Row and table constructors"

select A.Name 
from A 
where (A.id1, A.id2) in (select B.id1,  B.id2 from B where B.id = @Id)

edited Feb 05 '19 at 09:05

answered Feb 05 '19 at 08:58

jarlh

I´m not sure if it is the same as an INNER JOIN, are you? – Ibai Feb 05 '19 at 09:03
2

@Ibai, probably better, because if it's a one to many relation, those queries will not return duplicate rows. – jarlh Feb 05 '19 at 09:04
Thx @jarlh I agree with you, the "Row and table constructor" seems to be the correct (and may be better) way. – Ibai Feb 05 '19 at 09:14
1

I, too, agree. This is the way to write the query. You want the name `FROM` A `WHERE` `EXISTS` a related record in B. (In other words: You want the name `FROM` A `WHERE` the tuple id1/id2 is `IN` the set of tuples in B.) This is the idea of SQL, that you describe what you are looking for. – Thorsten Kettner Feb 05 '19 at 09:16

1 Answers1