I wonder if there any case that a join can not be converted to a sub-query.
BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
2
-
For any one-to-many relationship a join between two tables will return something different than a sub-query: sqlfiddle.com/#!15/e50d9/1 So I would say the answer is a clear: "No, that statement is not true"– user1822Commented Aug 26, 2014 at 8:27
-
However you can change that around so that you aren't selecting from the parent but rather the child. sqlfiddle.com/#!15/e50d9/4/2– MickyTCommented Aug 26, 2014 at 19:09
Add a comment
|
2 Answers
As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform:
SELECT T1.A, T2.B
FROM T1
JOIN T2
ON condition(T1.C, T2.D)
into:
SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2))
A similar conclusion can be reached with LEFT [OUTER] JOINS and RIGHT [OUTER] JOINS (the other types supported on MySQL), by including the NULL value:
SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2) UNION SELECT NULL)
-
I would like to comment that asking what is theoretically possible is not very useful. In reality, while there are some queries that can be written as a JOIN or as a SUBQUERY with identical performance (more and more as the query optimiser becomes better), in many cases only one of them is practical and/or performant, and there are subqueries that cannot be expressed as joins.– jynusCommented Aug 26, 2014 at 0:20
-
agree. But this question is all about theoretical possibility.– zx_wingCommented Aug 27, 2014 at 0:24
-
@zx_wing yes, that is why I answered theoretically and tried to demonstrate it, but I wanted to write an addendum for other viewers.– jynusCommented Aug 27, 2014 at 0:27
I would say the statement is false, though it's difficult to prove a negative.
I don't believe a CROSS JOIN could be done with sub queries
SELECT *
FROM T1 CROSS JOIN T2
or for that matter any join that delivers a many to many result
-
Well, I can do a cross join like this:
SELECT c , (SELECT i FROM t2 LIMIT 1) as i FROM t1 UNION SELECT c , (SELECT i FROM t2 LIMIT 1, 1) as i FROM t1 UNION SELECT c, (SELECT i FROM t2 LIMIT 2, 1) FROM t1 ...;– jynusCommented Aug 25, 2014 at 23:08 -
1I concede, that is true. I wouldn't want to be the one to generate it though :)– MickyTCommented Aug 26, 2014 at 1:56