Why use JOIN rather than inner queries

Question

I find myself unwilling to push to using JOIN when I can easily solve the same problem by using an inner query:

e.g.

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

My question is, is this a bad programming practice? I find it easier to read and maintain as opposed to a join.

UPDATE

I want to add that there's some great feedback in here which in essence is pushing be back to using JOIN. I am finding myself less and less involved with using TSQL directly these days as of a result of ORM solutions (LINQ to SQL, NHibernate, etc.), but when I do it's things like correlated subqueries which I find are easier to type out linearly.

Answer 1

Personally, I find this incredibly difficult to read. It isn't the structure a SQL developer expects. By using JOIN, you are keeping all of your table sources in a single spot instead of spreading it throughout your query.

What happens if you need to have three or four joins? Putting all of those into the SELECT clause is going to get hairy.

Answer 2

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

And I find a join much much easier to read and maintain.

Answer 3

If you needed more than one column from the second table, then you would require two subqueries. This typically would not perform as well as a join.

Answer 4

This is not a bad programming practice at all IMO, it is a little bit ugly though. It can actually be a performance boost in situations where the sub-select is from a very large table while you are expecting a very small result set (you have to consider indexes and platform, 2000 having a different optimizer and all from 2005). Here is how I format it to be easier to read.

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

Edit: This of course assumes that your subselect will only return one value, if not it could be returning you bad results. See gbn's response.

Answer 5

it makes it a lot easier to use other types of joins (left outer, cross, etc) because the syntax for those in subquery terms is less than ideal for readability

Answer 6

This is not equivalent to JOIN.

If you have multiple rows in TABLE2 for each row in TABLE1, you won't get them. For each row in TABLE1 you get one row output so you can't get multiple from TABLE2.

This is why I'd use "JOIN": to make sure I get the data I wanted...

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

Answer 7

The query you use was often used as a replacement for a LEFT JOIN for the engines that lacked it (most notably, PostgreSQL before 7.2)

This approach has some serious drawbacks:

1. It may fail if TABLE2.ID is not UNIQUE

2. Some engines will not be able to use anything else than NESTED LOOPS for this query

3. If you need to select more than one column, you will need to write the subquery several times

If your engine supports LEFT JOIN, use the LEFT JOIN.

In MySQL, however, there are some cases when an aggregate function in a select-level subquery can be more efficient than that in a LEFT JOIN with a GROUP BY.

See this article in my blog for the examples:

• Aggregates: subqueries vs. GROUP BY

Answer 8

At the end of the day, the goal when writing code, beyond functional requirements, is to make the intent of your code clear to a reader. If you use a JOIN, the intent is obvious. If you use a subquery in the manner you describe, it begs the question of why you did it that way. What were you trying to achieve that a JOIN would not have accomplished? In short, you waste the reader's time in trying to determine if the author was solving some problem in an ingenious fashion or if they were writing the code after a hard night of drinking.