-1

What is the difference between the following 2 Query?

select * from DB_TABLE1 Inner join DB_TABLE2 ON DB_TABLE1.ID = DB_TABLE2.ID

select * from DB_TABLE1 Where ID in (select ID from DB_TABLE2)

Maybe the question is stupid, I'm a newbie to queries... Thanks everyone in advance

Giovanni
  • 1
  • 1
  • 1
    Your question had already been asked (and answered) on SO before: Here: https://stackoverflow.com/questions/11533891/difference-between-inner-join-and-where-in-select-join-sql-statement#:~:text=4%20Answers&text=One%20difference%20is%20that%20the,exact%20intent%20of%20the%20query. and Here: https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – GoodJuJu Mar 23 '21 at 10:10
  • @GoodJuJu Eh? They are totally different questions you have linked to. – Stu Mar 23 '21 at 10:13
  • The previous posts I linked to are: `Difference between inner join and where in select join SQL statement` and `INNER JOIN ON vs WHERE clause`. I would say they are very similar to your question, `Differences between inner join and where in (select…)` – GoodJuJu Mar 23 '21 at 10:18
  • 1
    @GoodJuJu You need to read the question, not just the (sometimes) innapropriate title ;-) – Stu Mar 23 '21 at 10:33

2 Answers2

2

These are two very different queries.

In your first, you are joining the tables using a key column in either, and then selecting all columns from both tables.

For each row in your left table, you are matching all rows in the right table, so if Table1 has 1 row with an ID of 1 and Table2 has 2 rows with an ID of 1, you will get 2 rows.

Conversly, your second query is selecting only the columns from Table1 and filtering out rows where the ID is not in Table2. you only get columns from Table1 and rows are never duplicated, only filtered out.

Stu
  • 30,392
  • 6
  • 14
  • 33
  • Thank you so much, originally my problem arose precisely for this reason and, I wanted to understand if it was actually due to what you indicated. – Giovanni Mar 25 '21 at 10:10
  • Hey, if this helped you out, feel free to accept the answer :-()) – Stu Apr 03 '21 at 21:20
1

These are called JOIN and subquery respectively. You can check this out by using this simple explanation.

Nijat Mammadov
  • 84
  • 4