Now I answered the question about SQL, I saw the neighboring answer and wondered if these 2 queries will always be identical in result:

SELECT table1.* FROM table1 INNER JOIN table2 ON table1.id = table2.table2_id WHERE table2.name = 'something.zip'

and

 SELECT t1.* FROM table1 t1 INNER JOIN (SELECT table2_id FROM table2 WHERE name = 'something.zip') t2 ON t1.id = t2.table2_id

Could there be cases when these 2 queries on one table will give a different answer? For example, the 1st query will return fewer lines? Or is it completely 2 identical queries and for any data in the tables table1 and table2 will produce the same result?

  • 2
    If you first add table1.* Instead of a common * sample, it will be. But in terms of execution time they will not be identical. - Alex Krass
  • after being edited by @AlexKrass, what are your doubts? - 4per
  • @AlexKrass corrected the question to make it clearer, thanks. Another had in mind - Denis
  • @ 3per corrected the question. - Denis

2 answers 2

These are two different requests, but the result will be the same.

In the first case, the union is performed and after that the set of selected elements is cut off by the parameters: A ⋂ B ⋂ C In the second case, the elements are cut off first and only then the union: A ⋂ (B ⋂ C) . Where in this case is C ⊆ B

According to set theory, this is an associative operation (A â‹‚ B) â‹‚ C = A â‹‚ (B â‹‚ C)

In other words, the only question is that it will be faster executed from the point of view of the computational process. And the result will always be the same.

    And you can also do so

     SELECT t1.* FROM table1 t1, table2 t2 WHERE t2.name = 'something.zip' AND t1.id = t2.table2_id