0

I want to know if is possible if this kind of make queries has a name and which is the better option?

OPTION 1

select * from users, tasks
where users.id = tasks.user_id
and user.name='Lucas'

OPTION 2

SELECT * from task
where user_id in (select id in users where user.name='Lucas')

OPTION 3

SELECT * 
FROM tasks
JOIN users
ON tasks.user_id = users.Id
where user.name='Lucas'

Thanks

Tlaloc-ES
  • 4,825
  • 7
  • 38
  • 84
  • 3
    The second version doesn't make much sense, and isn't doing what you think. The first and third queries are logically equivalent, but you should generally use the _explicit_ join approach in the third query. – Tim Biegeleisen May 16 '22 at 09:09
  • Typos in the code suggest you haven't actually run these queries to observe that at least the first 2 would produce different result sets assuming non-trivial table contents, so first work out what result you're after and if any of the queries produce that result set. – Damien_The_Unbeliever May 16 '22 at 09:09
  • I dont't have that code I only have a notes about how can I make a querie in tables related, the second option, which a Where is not the same that the first option witch a where? – Tlaloc-ES May 16 '22 at 09:12
  • Updated the second option with a condition – Tlaloc-ES May 16 '22 at 09:14
  • 1
    Better? I would reject the first because of the comma join, and the second because of the IN and keep the third because It's clearer what the intent is - just my opinion I doubt if there's much difference in performance. – P.Salmon May 16 '22 at 09:18
  • The first and third queries will produce identical result but the first one is written with ancient syntax . I will choose the third one but change `SELCECT *` to `SELECT col1,col2,coln` . The second query will produce different results with the first and third one – Ergest Basha May 16 '22 at 09:21
  • I would use none of them since using * in such queries will likely be no good idea and you furthermore should use aliases. Generally, there is no reason to avoid the usage of JOIN when JOIN is possible. JOIN is easy to read and to use. – Jonas Metzler May 16 '22 at 09:27
  • This is an example of where having a style guide, and sticking to it, makes _everyone's_ life better. – Neville Kuyt May 16 '22 at 10:15

1 Answers1

1

When joining tables, I usually go with the join. (OPTION 3 here)

Performance-wise, I don't think there is much difference between options 1 and 3. As for the 2nd option, I'd avoid it in almost every scenario.

I think this post answers your question in more details.

Stratis Dermanoutsos
  • 647
  • 1
  • 8
  • 20