Why use JOIN rather than internal queries

Question

I do not want to try to use JOIN when I can easily solve the same problem using an internal query:

eg.

SELECT COLUMN1, ( SELECT COLUMN1 FROM TABLE2 WHERE TABLE2.ID = TABLE1.TABLE2ID ) AS COLUMN2 FROM TABLE1;

My question is: is this a bad programming practice? It’s easier for me to read and support, rather than join.

UPDATE

I want to add that there is excellent feedback here, which essentially pushes back to using JOIN. I find myself less and less connected with using TSQL directly these days based on the results of ORM solutions (LINQ to SQL, NHibernate, etc.), but when I do this, such as correlated subqueries that seem to me, it’s easier to enter linearly .

Answer 1

Personally, I find this incredibly difficult to read. This is not the structure that SQL developer expects. Using JOIN, you save all your table sources in one place instead of distributing it throughout the query.

What happens if you need to have three or four connections? Putting all of these elements in a SELECT clause will be unintentional.

Answer 2

A join is usually faster than a correlated subquery because it acts on a set of rows rather than one row at a time. I would never allow this code to go to my production server.

And I find the connection a lot easier to read and maintain.

Answer 3

If you need more than one column from the second table, you will need two subqueries. Usually this will not be performed in the same way as a join.

Answer 4

This is not equivalent to JOIN.

If you have several tables in table 2 for each row in table 1, you will not get them. For each line in TABLE1 you get one line output, so you cannot get multiple from TABLE2.

That's why I would use "JOIN": to make sure I get the right data ...

After your update: I rarely use correlation, except with EXISTS ...

Answer 5

The query you used was often used as a replacement for LEFT JOIN for engines that lacked it (especially PostgreSQL before 7.2 )

This approach has some serious drawbacks:

• It may fail if TABLE2.ID not UNIQUE

• Some engines will not be able to use anything but NESTED LOOPS for this request.

• If you need to select more than one column, you will need to write a subquery several times

If your engine supports LEFT JOIN , use LEFT JOIN .

In MySQL , however, there are some cases where an aggregate function in a subselect of a level selection may be more efficient than in a LEFT JOIN with GROUP BY .

See this blog post for examples:

• Aggregates: Subqueries vs GROUP BY

Answer 6

This is not a bad programming practice in all IMOs, but it is a bit ugly. In fact, this can be a performance improvement in situations where sub-selection is performed from a very large table, while you are expecting a very small result set (you need to consider indexes and the platform, 2000 with a different optimizer, and that's all from 2005). This is how I format it so that it is easier to read.

 select column1 [column2] = (subselect...) from table1 

Edit: This, of course, assumes that your subtitle will return only one value if it does not produce bad results. See gbn's answer.

Answer 7

this makes it much easier to use other types of joins (left outer, cross, etc.), because the syntax for those in the subqueries is less ideal for readability

Answer 8

After all, the goal when writing code, in addition to functional requirements, is to make your code understandable to the reader. If you use JOIN, this is obvious. If you use the subquery in the form that you describe, it asks the question of why you did it. What did you try to achieve so that the CONNECTION is not achieved? In short, you spend time reading, trying to determine if the author solved a problem in an ingenious way or wrote code after a hard night of drinking.